1、MySQL會出現(xiàn)中文亂碼的原因
當(dāng)我們在使用MySQL數(shù)據(jù)庫的時候，經(jīng)常會碰到亂碼的問題，看下面的代碼。

    mysql> create table test(id int,name varchar(10)); Query OK, 0 rows affected (0.01 sec)      mysql> insert into test values(1,'宋蔚然');     ERROR 1366 (HY000): Incorrect string value: 'xE5xAEx8BxE8x94x9A...' for column 'name' at row 1     mysql>

相關(guān)學(xué)習(xí)推薦：mysql視頻教程

很顯然，當(dāng)插入中文的時候報錯，原因在哪呢？

    mysql> show variables like '%CHARACTER%';     +--------------------------+----------------------------+     | Variable_name            | Value                      |     +--------------------------+----------------------------+     | character_set_client     | utf8                       |     | character_set_connection | utf8                       |     | character_set_database   | latin1                     |     | character_set_filesystem | binary                     |     | character_set_results    | utf8                       |     | character_set_server     | latin1                     |     | character_set_system     | utf8                       |     | character_sets_dir       | /usr/share/mysql/charsets/ |     +--------------------------+----------------------------+

character_set_client 客戶端使用的字符編碼
character_set_connection 數(shù)據(jù)庫鏈接使用的編碼
character_set_database 數(shù)據(jù)庫使用的字符編碼
原來是字符編碼與服務(wù)端、數(shù)據(jù)庫的編碼不統(tǒng)一而造成的。

2、MySQL會出現(xiàn)中文亂碼的解決方法
方法一：設(shè)置names

    mysql> set names latin1;          mysql> set names latin1;     Query OK, 0 rows affected (0.00 sec)          mysql> select * from test;     Empty set (0.00 sec)          mysql> insert into test values(1,'宋蔚然');     Query OK, 1 row affected (0.01 sec)          mysql> select * from test;     +------+-----------+     | id   | name      |     +------+-----------+     |    1 | 宋蔚然    |     +------+-----------+     1 row in set (0.00 sec)

再來看一下字符集的設(shè)置

     mysql> show variables like '%CHARACTER%';     +--------------------------+----------------------------+     | 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     | latin1                     |     | character_set_system     | utf8                       |     | character_sets_dir       | /usr/share/mysql/charsets/ |     +--------------------------+----------------------------+

字符編碼統(tǒng)一就不會出現(xiàn)亂碼。
解決亂碼問題其實就是將客戶端的字符編碼與服務(wù)端、數(shù)據(jù)庫的編碼統(tǒng)一。這里的服務(wù)端與數(shù)據(jù)庫編碼都是latin1，所有set names latin1即可臨時解決亂碼問題。

方法二：修改數(shù)據(jù)庫配置文件字符集為UTF8
UTF8支持很多語言體系，因此在生產(chǎn)中強烈建議將字符編碼設(shè)置UTF8。打開數(shù)據(jù)庫的配置文件，分別在[client]，[mysql]，[mysqld]下添加如下內(nèi)容。

    #vi /mysql/data/3306/my.cnf          [client]     default-character-set=utf8          [mysql]     default-character-set=utf8          [mysqld]     default-storage-engine=INNODB     character-set-server=utf8     collation-server=utf8_general_ci

重啟數(shù)據(jù)庫

   [root@test ~]# systemctl restart mysqld

重寫創(chuàng)建庫和表

    mysql> create database test;     Query OK, 1 row affected (0.00 sec)          mysql> use test;     Database changed     mysql> create table test(id int,name varchar(10));     Query OK, 0 rows affected (0.02 sec)          mysql> insert into test values(1,'宋蔚然');     Query OK, 1 row affected (0.00 sec)          mysql> select * from test;     +------+-----------+     | id   | name      |     +------+-----------+     |    1 | 宋蔚然    |     +------+-----------+     1 row in set (0.00 sec)

再來看一下字符集的設(shè)置

    mysql> show variables like '%CHARACTER%';     +--------------------------+----------------------------+     | Variable_name            | Value                      |     +--------------------------+----------------------------+     | character_set_client     | utf8                       |     | character_set_connection | utf8                       |     | character_set_database   | utf8                       |     | character_set_filesystem | binary                     |     | character_set_results    | utf8                       |     | character_set_server     | utf8                       |     | character_set_system     | utf8                       |     | character_sets_dir       | /usr/share/mysql/charsets/ |     +--------------------------+----------------------------+

1、MySQL會出現(xiàn)中文亂碼的原因
當(dāng)我們在使用MySQL數(shù)據(jù)庫的時候，經(jīng)常會碰到亂碼的問題，看下面的代碼。

    mysql> create table test(id int,name varchar(10)); Query OK, 0 rows affected (0.01 sec)      mysql> insert into test values(1,'宋蔚然');     ERROR 1366 (HY000): Incorrect string value: 'xE5xAEx8BxE8x94x9A...' for column 'name' at row 1     mysql>

很顯然，當(dāng)插入中文的時候報錯，原因在哪呢？

    mysql> show variables like '%CHARACTER%';     +--------------------------+----------------------------+     | Variable_name            | Value                      |     +--------------------------+----------------------------+     | character_set_client     | utf8                       |     | character_set_connection | utf8                       |     | character_set_database   | latin1                     |     | character_set_filesystem | binary                     |     | character_set_results    | utf8                       |     | character_set_server     | latin1                     |     | character_set_system     | utf8                       |     | character_sets_dir       | /usr/share/mysql/charsets/ |     +--------------------------+----------------------------+

character_set_client 客戶端使用的字符編碼
character_set_connection 數(shù)據(jù)庫鏈接使用的編碼
character_set_database 數(shù)據(jù)庫使用的字符編碼
原來是字符編碼與服務(wù)端、數(shù)據(jù)庫的編碼不統(tǒng)一而造成的。

2、MySQL會出現(xiàn)中文亂碼的解決方法
方法一：設(shè)置names

    mysql> set names latin1;          mysql> set names latin1;     Query OK, 0 rows affected (0.00 sec)          mysql> select * from test;     Empty set (0.00 sec)          mysql> insert into test values(1,'宋蔚然');     Query OK, 1 row affected (0.01 sec)          mysql> select * from test;     +------+-----------+     | id   | name      |     +------+-----------+     |    1 | 宋蔚然    |     +------+-----------+     1 row in set (0.00 sec)

再來看一下字符集的設(shè)置

     mysql> show variables like '%CHARACTER%';     +--------------------------+----------------------------+     | 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     | latin1                     |     | character_set_system     | utf8                       |     | character_sets_dir       | /usr/share/mysql/charsets/ |     +--------------------------+----------------------------+

字符編碼統(tǒng)一就不會出現(xiàn)亂碼。
解決亂碼問題其實就是將客戶端的字符編碼與服務(wù)端、數(shù)據(jù)庫的編碼統(tǒng)一。這里的服務(wù)端與數(shù)據(jù)庫編碼都是latin1，所有set names latin1即可臨時解決亂碼問題。

方法二：修改數(shù)據(jù)庫配置文件字符集為UTF8
UTF8支持很多語言體系，因此在生產(chǎn)中強烈建議將字符編碼設(shè)置UTF8。打開數(shù)據(jù)庫的配置文件，分別在[client]，[mysql]，[mysqld]下添加如下內(nèi)容。

    #vi /mysql/data/3306/my.cnf          [client]     default-character-set=utf8          [mysql]     default-character-set=utf8          [mysqld]     default-storage-engine=INNODB     character-set-server=utf8     collation-server=utf8_general_ci

重啟數(shù)據(jù)庫

   [root@test ~]# systemctl restart mysqld

重寫創(chuàng)建庫和表

    mysql> create database test;     Query OK, 1 row affected (0.00 sec)          mysql> use test;     Database changed     mysql> create table test(id int,name varchar(10));     Query OK, 0 rows affected (0.02 sec)          mysql> insert into test values(1,'宋蔚然');     Query OK, 1 row affected (0.00 sec)          mysql> select * from test;     +------+-----------+     | id   | name      |     +------+-----------+     |    1 | 宋蔚然    |     +------+-----------+     1 row in set (0.00 sec)

再來看一下字符集的設(shè)置

    mysql> show variables like '%CHARACTER%';     +--------------------------+----------------------------+     | Variable_name            | Value                      |     +--------------------------+----------------------------+     | character_set_client     | utf8                       |     | character_set_connection | utf8                       |     | character_set_database   | utf8                       |     | character_set_filesystem | binary                     |     | character_set_results    | utf8                       |     | character_set_server     | utf8                       |     | character_set_system     | utf8                       |     | character_sets_dir       | /usr/share/mysql/charsets/ |     +--------------------------+----------------------------+