Doctrine2: How to set all tables to collate with UTF8

Question

I am using Doctrine with Symfony2. My config.yml file looks something like this:-

Doctrine Configuration

doctrine:
    dbal:
        driver: %database_driver%
        host: %database_host%
        port: %database_port%
        dbname: %database_name%
        user: %database_user%
        password: %database_password%
        charset: UTF8

Unfortunately my tables are not collating to the UTF8_general_ci or UTF8_unicode_ci I tried

collate: utf8_unicode_ci

But Doctrine2 didn't recognize the option.

How can I achieve the same?

Answer 1

The behavior of collate has changed in doctrine: http://www.doctrine-project.org/jira/browse/DDC-2139

The collation is now set to utf8_unicode_ci if you don't specify anything at the table level. The way to go right now is to add it to options in your table declaration:

/**
 * @ORM\Table(options={"collate"="utf8_swedish_ci"})
 * @ORM\Entity
 */

This will generate the correct collation for the table:

$ php app/console doctrine:schema:update --dump-sql --env=test | grep swedish
...  DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci ENGINE = InnoDB;

I've filed an issue for the documentation to be updated to reflect this behaviour.

Answer 2

The charset: UTF8 option is just useful to ask Doctrine to execute SET NAMES UTF-8 on each page. I don't have any specific configuration for Doctrine, and my tables are by default in utf8_general_ci InnoDB. Read this part of the documentation: https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/faq.html#how-do-i-set-the-charset-and-collation-for-mysql-tables, it answers your question:

You can’t set these values inside the annotations, yml or xml mapping files. To make a database work with the default charset and collation you should configure MySQL to use it as default charset, or create the database with charset and collation details. This way they get inherited to all newly created database tables and columns.

Answer 3

UPDATE:

See Symfony3.1 book for reference (click here):

Also notice the use of utf8mb4 instead of plain utf8. ("Symfony recommends utf8mb4 against MySQL's utf8 character set, since it does not support 4-byte unicode characters, and strings containing them will be truncated. This is fixed by the newer utf8mb4 character set.")

Setting UTF8 defaults for MySQL is as simple as adding a few lines to your configuration file (typically my.cnf):

[mysqld]
# Version 5.5.3 introduced "utf8mb4", which is recommended
collation-server     = utf8mb4_general_ci # Replaces utf8_general_ci
character-set-server = utf8mb4            # Replaces utf8

You can also change the defaults for Doctrine so that the generated SQL uses the correct character set.

# app/config/config.yml
doctrine:
    dbal:
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci

Answer 4

I suggest you try add this setting to your Doctrine configuration:

    options:
        1002: "SET NAMES 'UTF8' COLLATE 'utf8_unicode_ci'"

So it looks like this:

    doctrine:
        dbal:
            driver: %database_driver%
            host: %database_host%
            port: %database_port%
            dbname: %database_name%
            user: %database_user%
            password: %database_password%
            charset: UTF8
            options:
                1002: "SET NAMES 'UTF8' COLLATE 'utf8_unicode_ci'"              

As a reference Doctrine Configuration: http://symfony.com/doc/2.0/reference/configuration/doctrine.html#reference-dbal-configuration

And in case you are using MySql: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

BTW. I had problems with displaying polish characters and adding only set names without collation (as below) helped.

    options:
        1002: "SET NAMES 'UTF8'

Answer 5

Use code below to set collation, engine and charset (annotation example):

/**
* @ORM\Table(
*     name="temporary", 
*     options={"collate":"utf8_general_ci", "charset":"utf8", "engine":"MyISAM"}
* )  
* @ORM\Entity
*/    

Source: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/annotations-reference.html#annref-column