CODEWITHSUNDEEP
mysqldatabaseutf-8character-encoding
Jeroen
Jeroen

Reputation: 2093

MySQL change database + tables charset & collation from UTF8 to UTF8mb4

I currently have a MySQL database with the following settings:

character_set_client: utf8
character_set_connection: utf8
character_set_database: utf8
character_set_filesystem: binary
character_set_results: utf8
character_set_server: latin1
character_set_system: utf8
collation_connection: utf8_general_ci 
collation_database: utf8_general_ci  
collation_server: latin1_swedish_ci

I want to support emoji's and other languages (like Chinese) in the database. Currently this is not working, those characters are automatically converted to a ?.

I created a test database with charset & collation utf8mb4(_general_ci) and a table with the same settings. Emojis work here. However, when I change the database settings to utf8(_general_ci) and leave the table as utf8mb4(_general_ci), emojis are still working, while this is not the case with my main database.

If I change my main database settings to charset + collation utf8mb4(_general_ci), and the tables as well, would that work?

And for database-access, will anything else have to be changed, such as character_set_connection or collation_connection? I know on my JavaScript server, the connection is configured as utf8, I assume this has to be utf8mb4.

All current utf8(_general_ci) data, will that be kept intact when changing to utf8mb4(_general_ci)?

Upvotes: 0

Views: 1424

Answers (1)

Rick James
Rick James

Reputation: 142298

Correctly stored utf8 characters will convert correctly to utf8mb4.

You should also specify that the connections are utf8mb4.

See this for discussion of 'question mark'.

To convert all the char/text columns to utf8mb4:

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

To convert one column:

ALTER TABLE tbl MODIFY COLUMN col ... CHARACTER SET utf8mb4;

Upvotes: 1

Related Questions