溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下MySQL會(huì)出現(xiàn)中文亂碼問題怎么辦，希望大家閱讀完這篇文章后大所收獲，下面讓我們一起去探討吧！
1、MySQL會(huì)出現(xiàn)中文亂碼的原因
當(dāng)我們?cè)谑褂肕ySQL數(shù)據(jù)庫的時(shí)候，經(jīng)常會(huì)碰到亂碼的問題，看下面的代碼。
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>相關(guān)學(xué)習(xí)推薦：mysql視頻教程
很顯然，當(dāng)插入中文的時(shí)候報(bào)錯(cuò)，原因在哪呢？
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會(huì)出現(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)一就不會(huì)出現(xiàn)亂碼。
解決亂碼問題其實(shí)就是將客戶端的字符編碼與服務(wù)端、數(shù)據(jù)庫的編碼統(tǒng)一。這里的服務(wù)端與數(shù)據(jù)庫編碼都是latin1，所有set names latin1即可臨時(shí)解決亂碼問題。
方法二：修改數(shù)據(jù)庫配置文件字符集為UTF8
UTF8支持很多語言體系，因此在生產(chǎn)中強(qiáng)烈建議將字符編碼設(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/ |
+--------------------------+----------------------------+undefined
1、MySQL會(huì)出現(xiàn)中文亂碼的原因
當(dāng)我們?cè)谑褂肕ySQL數(shù)據(jù)庫的時(shí)候，經(jīng)常會(huì)碰到亂碼的問題，看下面的代碼。
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>很顯然，當(dāng)插入中文的時(shí)候報(bào)錯(cuò)，原因在哪呢？
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會(huì)出現(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)一就不會(huì)出現(xiàn)亂碼。
解決亂碼問題其實(shí)就是將客戶端的字符編碼與服務(wù)端、數(shù)據(jù)庫的編碼統(tǒng)一。這里的服務(wù)端與數(shù)據(jù)庫編碼都是latin1，所有set names latin1即可臨時(shí)解決亂碼問題。
方法二：修改數(shù)據(jù)庫配置文件字符集為UTF8
UTF8支持很多語言體系，因此在生產(chǎn)中強(qiáng)烈建議將字符編碼設(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/ |
+--------------------------+----------------------------+看完了這篇文章，相信你對(duì)MySQL會(huì)出現(xiàn)中文亂碼問題怎么辦有了一定的了解，想了解更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼