0

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)?

Jeroen
  • 2,011
  • 2
  • 26
  • 50

1 Answers1

1

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;
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks for this answer. This is what I needed. Then there's another thing. The database runs on 2 separate servers, and is synchronised using `master-master replication`. I've seen that the column-settings are changed on both, but not the table-settings. Is it okay to do this on both databases, or will this break anything? Thanks! – Jeroen Dec 21 '17 at 15:51
  • @JeroenJK - It seems strange that the servers would not be identical. Exactly what commands did you perform? A problem _may_ occur if you later add a column without specifying a charset/collation -- if it picks up the _different_ `DEFAULT` one each machine. – Rick James Dec 22 '17 at 00:51