1、MySQL會出現中文亂碼的原因
當我們在使用MySQL資料庫的時候，常常會碰到亂碼的問題，看下面的程式碼。
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: '\xE5\xAE\x8B\xE8\x94\x9A...' for column 'name' at row 1
mysql>相關學習推薦：mysql影片教學
#很顯然，當插入中文的時候報錯，原因在哪呢？
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 資料庫連結使用的編碼
character_set_database 資料庫使用的字元編碼
原來是字元編碼與服務端、資料庫的編碼不統一而造成的。
2、MySQL會出現中文亂碼的解決方法
#方法一：設定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)再來看一下字元集的設定
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/ |
+--------------------------+----------------------------+字元編碼統一就不會出現亂碼。
解決亂碼問題其實就是將客戶端的字元編碼與服務端、資料庫的編碼統一。這裡的服務端與資料庫編碼都是latin1，所有set names latin1即可暫時解決亂碼問題。
方法二：修改資料庫設定檔字元集為UTF8
#UTF8支援許多語言體系，因此在生產中強烈建議將字元編碼設定UTF8。開啟資料庫的設定文件，分別在[client]，[mysql]，[mysqld]下新增如下內容。
#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重啟資料庫
[root@test ~]# systemctl restart mysqld
重寫建立函式庫與表格
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)再看一下字元集的設定
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/ |
+--------------------------+----------------------------+
當我們在使用MySQL資料庫的時候，常常會碰到亂碼的問題，看下面的程式碼。 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: '\xE5\xAE\x8B\xE8\x94\x9A...' for column 'name' at row 1
mysql>
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_connection 資料庫連結使用的編碼
character_set_database 資料庫使用的字元編碼原來是字元編碼與服務端、資料庫的編碼不統一而造成的。
2、MySQL會出現中文亂碼的解決方法
#方法一：設定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)
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/ |
+--------------------------+----------------------------+解決亂碼問題其實就是將客戶端的字元編碼與服務端、資料庫的編碼統一。這裡的服務端與資料庫編碼都是latin1，所有set names latin1即可暫時解決亂碼問題。
#UTF8支援許多語言體系，因此在生產中強烈建議將字元編碼設定UTF8。開啟資料庫的設定文件，分別在[client]，[mysql]，[mysqld]下新增如下內容。 #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
[root@test ~]# systemctl restart mysqld
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) 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/ |
+--------------------------+----------------------------+以上是解決MySQL會出現中文亂碼問題的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！
