vendredi 9 mars 2012

MySQL: from utf8 to utf8mb4

After my last upgrade from MySQL 5.1 to MySQL 5.5 to use utf8mb4 instead of utf8, I noticed that some accented characters were being stored as question mark.

To fix this problem, I had to update the character set of the database, tables and columns and make sure that the my.cnf contains the good charset.

Server Character Set and Collation

In /etc/mysql/my.cnf file make sure you have the following:
# ...
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci
# ...
Display the current character set

To see the current character set, issue the following command:

For Schemas:
SELECT default_character_set_name FROM information_schema.SCHEMATA S
WHERE schema_name = "schemaname";
For Tables:
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "schemaname"
  AND T.table_name = "tablename";
For Columns:
SELECT character_set_name FROM information_schema.`COLUMNS` C
WHERE table_schema = "schemaname"
  AND table_name = "tablename"
  AND column_name = "columnname";
Alter database character set:

Use the following command:
alter database schemaname CHARACTER SET = utf8mb4;
Alter table and column character set

ALTER TABLE tablename DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY columname LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

ALTER TABLE tablename CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;
ALTER TABLE tablename MODIFY columnname longtext CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;
Finally, the output of SHOW VARIABLES LIKE 'character_set%'; should at least look like to the following:
mysql> SHOW VARIABLES LIKE 'character_set%';
| Variable_name            | Value                                                              |
| character_set_client     | utf8                                                               |
| character_set_connection | utf8                                                               |
| character_set_database   | utf8mb4                                                            |
| character_set_filesystem | binary                                                             |
| character_set_results    | utf8                                                               |
| character_set_server     | utf8mb4                                                            |
| character_set_system     | utf8                                                               |
| character_sets_dir       | /usr/local/appservers/mysql-5.5.21-linux2.6-x86_64/share/charsets/ |
8 rows in set (0.00 sec)

If you are using MySQL with a Java application, you must upgrade your mysql-connector-java to the latest version, at the moment of writing, it is 5.1.18 or at least to the 5.1.14

Here is the maven dependency:



Aucun commentaire:

Enregistrer un commentaire