How to get MySQL to handle UTF-8 correctly

Question

One of the answers to the question I asked yesterday suggested that I should make sure that my database correctly processes UTF-8 characters. How can I do this with MySQL?

Answer 1

Update:

The short answer is that you should almost always be using utf8mb4 code and utf8mb4_unicode_ci reconciliation.

See:

• Aaron comments on this answer How to get MySQL to handle UTF-8 correctly

• What is the difference between utf8_general_ci and utf8_unicode_ci

• Conversion Guide: https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-conversion.html

Original answer:

MySQL 4.1 and above has the default UTF-8 character set. You can check this in the my.cnf file, do not forget to install both the client and the server ( default-character-set character-set-server default-character-set and default-character-set character-set-server ).

If you have the data you want to convert to UTF-8, dump your database and import it as UTF-8, making sure:

• use SET NAMES utf8 before querying / inserting into database
• use DEFAULT CHARSET=utf8 when creating new tables
• at the moment, your client and MySQL server must be in UTF-8 (see my.cnf ). remember that any languages ​​you use (e.g. PHP) must also be UTF-8. Some versions of PHP will use their own MySQL client library, which may not support UTF-8.

If you want to migrate existing data, remember to back up first! Many strange data changes can occur when things go wrong as planned!

Some resources:

• full migration of UTF-8 (cdbaby.com)
• article on UTF-8 ready for php functions (note that this part is deprecated)

Answer 2

To make this "persistent", in my.cnf :

 [client] default-character-set=utf8 [mysqld] character-set-server = utf8 

To check, go to the client and show some variables:

 SHOW VARIABLES LIKE 'character_set%'; 

Make sure they are all utf8 except ..._filesystem , which must be binary and ..._dir , which points somewhere in the MySQL installation.

Answer 3

MySQL 4.1 and above has a default character set, which it calls utf8 , but which in fact is only a subset of UTF-8 (allows only three-byte characters and less).

Use utf8mb4 as your encoding if you want "full" UTF-8.

Answer 4

Short answer: use utf8mb4 in 4 places:

• The byte in your client is utf8, not latin1 / cp1251 / etc.
• SET NAMES utf8mb4 or something similar when establishing a client connection to MySQL
• CHARACTER SET utf8mb4 for all tables / columns - except for columns that strictly correspond to ascii / hex / country_code / zip_code / etc.
• <meta charset charset=UTF-8> if you output HTML. (Yes, the spelling is different here.)

Additional information
Utf8 fully

The links above contain a "detailed canonical answer needed to solve all problems." - There is a gap on this forum.

Edit

In addition to the CHARACTER SET utf8mb4 containing "all" world characters, COLLATION utf8mb4_unicode_520_ci is an argument in favor of using the "best universal" mapping. (There are also Turkish, Spanish, etc., Sorts for those who want nuances in these languages.)

Answer 5

Encoding is a property of the database (default) and the table. You can see (MySQL commands):

 show create database foo; > CREATE DATABASE `foo`.`foo` /*!40100 DEFAULT CHARACTER SET latin1 */ show create table foo.bar; > lots of stuff ending with > ) ENGINE=InnoDB AUTO_INCREMENT=252 DEFAULT CHARSET=latin1 

In other words; It's easy enough to check the encoding of the database or change it:

 ALTER TABLE `foo`.`bar` CHARACTER SET utf8; 

Answer 6

To change the character set encoding in UTF-8 for the database itself, enter the following command at the mysql> prompt. USE ALTER DATABASE . Replace DBNAME with the database name:

 ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci; 

This is a duplicate of this question. How to convert the entire MySQL database character set and mapping to UTF-8?

Answer 7

These MySQL and UTF-8 tips may be helpful. Unfortunately, they are not a complete solution, just common mistakes.

Answer 8

I followed Javier's decision, but I added several different lines to my.cnf:

 [myslqd] skip-character-set-client-handshake collation_server=utf8_unicode_ci character_set_server=utf8 

I found this idea here: http://dev.mysql.com/doc/refman/5.0/en/charset-server.html in the user comment of the first or only user at the bottom of the page. He mentions that shaking hands with a missing client character has some meaning.

Answer 9

Install database collation to UTF-8 then apply table collation to the default database.

Answer 10

Your answer: you can customize using MySql settings. There may be something out of context in My Answer, but it is also useful to you. How to customize Character Set and Collation .

For applications that store data using the default MySQL character set and collation ( latin1, latin1_swedish_ci ), no special configuration should be needed. If applications require data storage using a different character set or sorting, you can configure the character set of information in several ways:

• Specify character settings for each database. For example, hotels to use one database may require utf8 , while applications that use another database may require sjis.
• Specify character settings when starting the server. This causes the server to use the specified settings for all applications that do not take other measures.
• Specify character settings during setup if you are creating MySQL from a source. This forces the server to use these settings for all applications, without specifying them when the server starts.

The examples shown here for your question, to set the utf8 character set, here also set the mapping for the more useful ( utf8_general_ci collation`).

Specify character settings for each database

  CREATE DATABASE new_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci; 

Specify character parameters at server startup

 [mysqld] character-set-server=utf8 collation-server=utf8_general_ci 

Set character preferences at the time of MySQL configuration

 shell> cmake . -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci 

To see the character set values ​​and system matching variables that apply to your connection, use the following instructions:

 SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%'; 

This may be a long answer, but there is a way you can use. Hope my answer will be helpful to you. for more information http://dev.mysql.com/doc/refman/5.7/en/charset-applications.html

Answer 11

SET NAMES UTF8

It's a trick

Answer 12

Establish a database connection in UTF8:

  if($handle = @mysql_connect(DB_HOST, DB_USER, DB_PASS)){ //set to utf8 encoding mysql_set_charset('utf8',$handle); } 

Answer 13

CONNECTING THE DATABASE TO UTF-8

 $connect = mysql_connect('$localhost','$username','$password') or die(mysql_error()); mysql_set_charset('utf8',$connect); mysql_select_db('$database_name','$connect') or die(mysql_error()); 

Answer 14

It was possible to find a solution. Follow the steps outlined at http://technoguider.com/2015/05/utf8-set-up-in-mysql/

 SET NAMES UTF8; set collation_server = utf8_general_ci; set default-character-set = utf8; set init_connect = 'SET NAMES utf8′; set character_set_server = utf8; set character_set_client = utf8;