.jpg)
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:?'xE5xAEx8BxE8x94x9A...'?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/?| ????+--------------------------+----------------------------+
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
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:?'xE5xAEx8BxE8x94x9A...'?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/?| ????+--------------------------+----------------------------+
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載。
THE END
.png)
