MySQL modifies the character set for existing data

MySQL modifies character set problems for existing data

Ran in the production environment for a long time, found that the Mysqlclient connection character set is the default latin1, we have always thought is utf8, resulting in this misunderstanding, because in the intranet environment, we are source compiled MySQL, and specify the compilation option character set bit UTF8, At this point, MySQL is the default character connection is UTF8.

While in the external network, we are binary package installation, the default is Latin1, although in my.conf specified [client] [MySQL] [mysqld] in the character set is UTF8, This does not guarantee that the character set is UTF8 when the mysqlclient is connected. So it is best to establish a connection is the specified character set, this is guaranteed. such as:

// c connections need to be set before connection is established " UTF8 ");

Since data is connected to MySQL storage via laint1, fetching data also requires a LAINT1 connection. However, this method does not match the expected, and the other client connections are connected through the UTF8, which causes garbled and must be repaired to the data stored through the UTF8 connection.

Repair principle

set up a connection by latin1 the data out, and then set up the connection by UTF8 to save the data back .

Repair case

The table structure is defined as follows:

CREATE TABLE IF  not EXISTS' tbl_friend ' (' Uin ' )bigint( -) unsigned not NULL, ' Name 'varchar(255)CHARACTER SETUTF8 COLLATE Utf8_binDEFAULT NULL,  PRIMARY KEY(' Uin '),UNIQUE KEY' name ' (' name ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

 select  CONCAT ( " update tbl_friend set ' Name ' =\   " , Name,    " \"   where uin=  , Uin,"  ;  ) from  tbl_friend into  outfile  '  

After executing the above statement in MySQL and then importing the file in MySQL /tmp/UpdateFriendName.sql , the data is fixed. It should be noted that the character set in the My.cnf [MySQL] segment is your modified character set or through MySQL- Default-character-set=utf8 </tmp/updatefriendname.sql Specifies the fixed character set.

After the repair can be passed in MySQL set names utf8/latin1 , and then look at the select Name from tbl_friend limit 10; changes,

Obstacles that may be encountered

If you encounter the MySQL server is running and the--SECURE-FILE-PRIV option so it cannot execute this statement need to be in my Add the [mysqld] in the. conf secure_file_priv = and restart MySQL.

MySQL modifies the character set for existing data