小編給大家分享一下MySQL會出現中文亂碼問題怎么辦，希望大家閱讀完這篇文章后大所收獲，下面讓我們一起去探討吧！
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/ |
+--------------------------+----------------------------+undefined
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> 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會出現中文亂碼問題怎么辦有了一定的了解，想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
