MySQL garbled related

1. The Environment for libraries and tables is as follows, and the character set is latin1:

Mysql> Show CREATE Database yuanqk;+----------+---------------------------------------------------------------- ---+| Database | Create Database |+----------+------------------------------------------- ------------------------+| Yuanqk | CREATE DATABASE ' yuanqk '/*!40100 DEFAULT CHARACTER SET latin1 */|+----------+---------------------------------------- ---------------------------+1 Row in Set (0.00 sec) mysql> mysql> use yuanqkdatabase changedmysql> Show Create tab Le student;+---------+----------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----------------------------+| Table |                                                                                                                                                                                                Create Table                        |+---------+-------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------+| Student | CREATE TABLE ' student ' (' ID ' int (one) not null auto_increment, ' name ' char (a) ' NOT NULL, PRIMARY key (' ID '), key ' Ind_ Name ' (' name '), KEY ' Ind_name_age ' (' name ')) Engine=innodb auto_increment=7 DEFAULT charset=latin1 |+---------+-------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------+  1 row in Set (0.00 sec) mysql>

2, insert the Chinese content, the query, found to be garbled:

mysql> INSERT into student values (1, ' Shingkun '); Query OK, 1 row affected, 1 Warning (0.00 sec) mysql> SELECT * FROM student;+----+------+| ID | Name |+----+------+|  1 |???  | +----+------+1 row in Set (0.00 sec)

3, the cause of garbled, is generally the database server character set and the client character set inconsistency, view the MySQL character set

Mysql> Show variables like ' character_set% '    , +--------------------------+----------------------------+ | Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | UTF8                       |  <=== client Character Set is utf8| character_set_connection | UTF8                       |  | character_set_database   | latin1                     |  <=== database Character set is latin1| Character_set_filesystem | Binary                     | | character_set_results    | UTF8                       | | character_set_server     | latin1                     | | character_set_ System     | UTF8                       | | character_sets_dir       

4, the character set of the database this should be set when the CREATE database, even if modified to UTF8, the inside of the inserted data must also be re-imported can also solve the garbled problem, but using the set names Latin1 to specify the client character set is no problem:

mysql> set names latin1; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into student values (1, ' Shingkun '); ERROR 1062 (23000): Duplicate entry ' 1 ' for key ' PRIMARY ' mysql> insert into student values (2, ' Shingkun '); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from student;+----+-----------+| ID | Name      |+----+-----------+|  1 |??? |       |  2 | Shingkun    |    

5, in fact, can also be specified by the login character set is also possible, such as: Mysql-uroot-pyuanqk yuanqk --default-character-set=latin1 -s/data/3306/ The Mysql.sock effect is the same.

6, that set names Latin1 is what the database did to make it normal to display, and then look at the current character set, the client and server side of the character set is consistent.

mysql> show variables like ' character_set% '; +--------------------------+ ----------------------------+| variable_name | Value |+--------------------------+----------------------------+| character_set_client |  Latin1 | The <=== client character set becomes Latin1 | character_set_connection |  Latin1 | The <=== connection character set also becomes latin1| Character_set_database | Latin1 | | Character_set_filesystem | binary | | Character_set_results |  Latin1 | <=== This should be the result return character set, also become Latin1 | Character_set_server | Latin1 | | Character_set_system | UTF8 | | Character_sets_dir | /u01/mysql/share/charsets/|+--------------------------+----------------------------+8 rows in Set (0.00 sec) MySQL > 

7. Why is the client's character set UTF8? In fact, if these character sets are set in My.cnf, then there is no such situation, I think this is usually not set in the parameter file, then the character set of these clients is related to the operating system character set, do a test to see:

[Email protected] 3306]# cat/etc/sysconfig/i18nlang= "en_US. UTF-8 "              

--Give him a gb2312 to see.

[Email protected] 3306]# cat/etc/sysconfig/i18n
#LANG = "en_US. UTF-8 "
lang= "zh_cn.gb2312"           <=== change to gb2312
Sysfont= "Latarcyrheb-sun16"
[Email protected] 3306]#

[[email protected] 3306]#. /etc/sysconfig/i18n <=== brought him into force [[email protected] 3306]# mysql-uroot-py Uanqk-s/data/3306/mysql.sock <=== Reconnect Welcome to the MySQL Monitor. Commands End With;  or \g.your MySQL connection ID is 4Server version:5.5.60-log mysql Community Server (GPL) Copyright (c) +, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help.                                        Type ' \c ' to clear the current input statement.mysql> show variables like ' character_set% '; <=== View the database character Set +--------------------------+----------------------------+| variable_name | Value |+--------------------------+----------------------------+| character_set_client |                                                   gb2312 | The <=== client-related character set has all become gb2312 |character_set_connection | gb2312 | | Character_set_database | Latin1 | | Character_set_filesystem | binary | | Character_set_results | gb2312 | | Character_set_server | UTF8 | | Character_set_system | UTF8 | | Character_sets_dir | /u01/mysql/share/charsets/|+--------------------------+----------------------------+8 rows in Set (0.00 sec) MySQL                                 >mysql> select * from student; <=== again query, display is full of garbled +----+--------------+| ID | Name |+----+--------------+| 1 |??? || 2 |???????????? || 5 |??????? || 6 |?????? |+----+--------------+4 rows in Set (0.05 sec) mysql>

9, and then set names Llatin1 can also display Chinese, so garbled is generally the client and the server side of the character set caused by inconsistent, for the client's character set and the operating system character set has a relationship. The best way is to write them all in the configuration file.

mysql> set names latin1; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from student;+----+-----------+| ID |  Name |+----+-----------+|       1 |???  || 2 |  Shingkun | | 5 |  Zhang San | | 6 | John Doe |+----+-----------+4 rows in Set (0.00 sec) mysql> Show variables like ' character_set% '; +------------------------- -+----------------------------+| variable_name | Value |+--------------------------+----------------------------+| character_set_client | Latin1 | | character_set_connection | Latin1 | | Character_set_database | Latin1 | | Character_set_filesystem | binary | | Character_set_results | Latin1 | | Character_set_server | UTF8 | | Character_set_system | UTF8 | | Character_sets_dir | /u01/mysql/share/charsets/|+--------------------------+----------------------------+8 rows inSet (0.00 sec) mysql>  

　　

MySQL garbled related