顧穎17021223250
轉(zhuǎn)載自https://blog.csdn.net/qingqing7/article/details/78465858
【嵌牛導(dǎo)讀】:MySQL對于字符集的指定可以細(xì)化到一個數(shù)據(jù)庫曹质,一張表,一列茫死,應(yīng)該用什么字符集得封。 但是,傳統(tǒng)的程序在創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表時并沒有使用那么復(fù)雜的配置验辞,它們用的是默認(rèn)的配置凶硅,那么悠咱,默認(rèn)的配置從何而來呢峭沦?
文件系統(tǒng)字符集是固定的贾虽,系統(tǒng)、服務(wù)器的字符集在安裝時確定吼鱼,與亂碼問題無關(guān)蓬豁;亂碼的問題與客戶端、數(shù)據(jù)庫連接菇肃、數(shù)據(jù)庫地粪、查詢結(jié)果的字符集設(shè)置有關(guān)。
【嵌牛鼻子】:MySQL字符集 中文亂碼
【嵌牛提問】:MySQL語句都有什么琐谤?如何使用mysql語句操作mysql蟆技?
【嵌牛正文】:
1、MySQL的字符集問題
關(guān)于MySQL的字符集問題更詳細(xì)可參考
MySQL的字符集支持(Character Set Support)有兩個方面:
字符集(Character set)和排序方式(Collation)。
對于字符集的支持細(xì)化到四個層次:
服務(wù)器(server)付魔,數(shù)據(jù)庫(database)聊品,數(shù)據(jù)表(table)和連接(connection)飞蹂。
1.1几苍、MySQL默認(rèn)字符集
MySQL對于字符集的指定可以細(xì)化到一個數(shù)據(jù)庫,一張表陈哑,一列妻坝,應(yīng)該用什么字符集。
但是惊窖,傳統(tǒng)的程序在創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表時并沒有使用那么復(fù)雜的配置刽宪,它們用的是默認(rèn)的配置,那么界酒,默認(rèn)的配置從何而來呢圣拄?
(1)編譯MySQL 時,指定了一個默認(rèn)的字符集毁欣,這個字符集是 latin1庇谆;
(2)安裝MySQL 時,可以在配置文件 (my.cnf) 中指定一個默認(rèn)的的字符集凭疮,如果沒指定饭耳,這個值繼承自編譯時指定的;
(3)啟動mysqld 時执解,可以在命令行參數(shù)中指定一個默認(rèn)的的字符集寞肖,如果沒指定,這個值繼承自配置文件中的配置,此時 character_set_server 被設(shè)定為這個默認(rèn)的字符集衰腌;
(4)當(dāng)創(chuàng)建一個新的數(shù)據(jù)庫時新蟆,除非明確指定,這個數(shù)據(jù)庫的字符集被缺省設(shè)定為character_set_server右蕊;
(5)當(dāng)選定了一個數(shù)據(jù)庫時琼稻,character_set_database 被設(shè)定為這個數(shù)據(jù)庫默認(rèn)的字符集;
(6)在這個數(shù)據(jù)庫里創(chuàng)建一張表時尤泽,表默認(rèn)的字符集被設(shè)定為 character_set_database欣簇,也就是這個數(shù)據(jù)庫默認(rèn)的字符集;
(7)當(dāng)在表內(nèi)設(shè)置一欄時坯约,除非明確指定熊咽,否則此欄缺省的字符集就是表默認(rèn)的字符集;
簡單的總結(jié)一下闹丐,如果什么地方都不修改横殴,那么所有的數(shù)據(jù)庫的所有表的所有欄位的都用
latin1存儲,不過我們?nèi)绻惭b MySQL,一般都會選擇多語言支持衫仑,也就是說梨与,安裝程序會自動在配置文件中把
default_character_set設(shè)置為 UTF-8,這保證了缺省情況下文狱,所有的數(shù)據(jù)庫的所有表的所有欄位的都用 UTF-8 存儲粥鞋。
1.2、查看默認(rèn)字符集
(默認(rèn)情況下瞄崇,mysql的字符集是latin1(ISO_8859_1)
通常呻粹,查看系統(tǒng)的字符集和排序方式的設(shè)定可以通過下面的兩條命令:
show variables like 'character%';
結(jié)果如下:
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
show variables like 'collation%'
結(jié)果如下:
+----------------------+-------------------+
| Variable_name ???????| Value ????????????|
+----------------------+-------------------+
| collation_connection | utf8_general_ci ??|
| collation_database ??| latin1_swedish_ci |
| collation_server ????| latin1_swedish_ci |
+----------------------+-------------------+
MySQL中涉及的幾個字符集解釋:
character_set_server/default_character_set:服務(wù)器字符集,默認(rèn)情況下所采用的苏研。
character_set_database:數(shù)據(jù)庫字符集等浊。
character_set_table:數(shù)據(jù)庫表字符集。
優(yōu)先級依次增加摹蘑。所以一般情況下只需要設(shè)置character_set_server筹燕,而在創(chuàng)建數(shù)據(jù)庫和表時不特別指定字符集,這樣統(tǒng)一采用character_set_server字符集衅鹿。
character_set_client:客戶端的字符集撒踪。客戶端默認(rèn)字符集塘安。當(dāng)客戶端向服務(wù)器發(fā)送請求時糠涛,請求以該字符集進(jìn)行編碼。
character_set_results:結(jié)果字符集兼犯。服務(wù)器向客戶端返回結(jié)果或者信息時忍捡,結(jié)果以該字符集進(jìn)行編碼。
在客戶端切黔,如果沒有定義character_set_results砸脊,則采用character_set_client字符集作為默認(rèn)的字符集。所以只需要設(shè)置character_set_client字符集纬霞。
要處理中文凌埂,則可以將character_set_server和character_set_client均設(shè)置為GB2312,如果要同時處理多國語言诗芜,則設(shè)置為UTF8瞳抓。
1.3、修改默認(rèn)字符集
(1)最簡單的修改方法伏恐,就是修改mysql的my.cnf文件中的字符集鍵值孩哑,一般情況下my.cnfd在/etc文件夾下。如果沒有可以自己創(chuàng)建該文件翠桦。并添加如下內(nèi)容:(還沒驗(yàn)證)
default_character_set = utf8
character_set_server = utf8
修改完后横蜒,重啟mysql的服務(wù),service mysql restart
使用mysql> SHOW VARIABLES LIKE 'character%';查看,發(fā)現(xiàn)數(shù)據(jù)庫編碼均已改成utf8
(2)還有一種修改字符集的方法丛晌,就是使用mysql的命令
在登錄數(shù)據(jù)庫時仅炊,我們用mysql --default-character-set=字符集-u root -p進(jìn)行連接,這時我們再用show variables like '%char%';命令查看字符集設(shè)置情況澎蛛,可以發(fā)現(xiàn)客戶端抚垄、數(shù)據(jù)庫連接、查詢結(jié)果的字符集已經(jīng)設(shè)置成登錄時選擇的字符集了瓶竭;
如果是已經(jīng)登錄了督勺,可以使用set names字符集;命令來實(shí)現(xiàn)上述效果,等同于下面的命令:
mysql> set character_set_client=utf8;
mysql> set character_set_connection=utf8;
mysql> set character_set_database=utf8;
mysql> set character_set_results=utf8;
mysql> set character_set_server=utf8;
mysql> set character_set_system=utf8;
mysql> set collation_connection=utf8;
mysql> set collation_database=utf8;
mysql> set collation_server=utf8;
(3)其他的一些設(shè)置方法:
修改數(shù)據(jù)庫的字符集
mysql>use mydb
mysql>alter database mydb character set utf-8;
創(chuàng)建數(shù)據(jù)庫指定數(shù)據(jù)庫的字符集
mysql>create database mydb character set utf-8;
2斤贰、mysql中文亂碼問題
解決亂碼的方法是,在執(zhí)行SQL語句之前次询,將MySQL以下三個系統(tǒng)參數(shù)設(shè)置為與服務(wù)器字符集character-set-server相同的字符集荧恍。
character_set_client:客戶端的字符集。
character_set_results:結(jié)果字符集屯吊。
character_set_connection:連接字符集送巡。
關(guān)于編碼格式GBK、GB2312盒卸、UTF8
UTF- 8:Unicode Transformation Format-8bit骗爆,允許含BOM,但通常不含BOM蔽介。是用以解決國際上字符的一種多字節(jié)編碼摘投,它對英文使用8位(即一個字節(jié)),中文使用24為(三個字節(jié))來編碼虹蓄。UTF-8包含全世界所有國家需要用到的字符犀呼,是國際編碼,通用性強(qiáng)薇组。UTF-8編碼的文字可以在各國支持UTF8字符集的瀏覽器上顯示外臂。如,如果是UTF8編碼律胀,則在外國人的英文IE上也能顯示中文宋光,他們無需下載IE的中文語言支持包。
GBK是國家標(biāo)準(zhǔn)GB2312基礎(chǔ)上擴(kuò)容后兼容GB2312的標(biāo)準(zhǔn)炭菌。GBK的文字編碼是用雙字節(jié)來表示的罪佳,即不論中、英文字符均使用雙字節(jié)來表示娃兽,為了區(qū)分中文菇民,將其最高位都設(shè)定成1。GBK包含全部中文字符,是國家編碼第练,通用性比UTF8差阔馋,不過UTF8占用的數(shù)據(jù)庫比GBD大。
GBK娇掏、GB2312等與UTF8之間都必須通過Unicode編碼才能相互轉(zhuǎn)換:
GBK呕寝、GB2312--Unicode--UTF8
UTF8--Unicode--GBK、GB2312
對于一個網(wǎng)站婴梧、論壇來說下梢,如果英文字符較多,則建議使用UTF-8節(jié)省空間塞蹭。不過現(xiàn)在很多論壇的插件一般只支持GBK孽江。
GB2312是GBK的子集,GBK是GB18030的子集
GBK是包括中日韓字符的大字符集合
如果是中文的網(wǎng)站推薦GB2312 GBK有時還是有點(diǎn)問題
為了避免所有亂碼問題番电,應(yīng)該采用UTF-8岗屏,將來要支持國際化也非常方便
UTF-8可以看作是大字符集,它包含了大部分文字的編碼漱办。
使用UTF-8的一個好處是其他地區(qū)的用戶(如香港臺灣)無需安裝簡體中文支持就能正常觀看你的文字而不會出現(xiàn)亂碼这刷。
gb2312是簡體中文的碼
gbk支持簡體中文及繁體中文
big5支持繁體中文
utf-8支持幾乎所有字符
2.1、解決方法
首先分析亂碼的情況
1.寫入數(shù)據(jù)庫時作為亂碼寫入
2.查詢結(jié)果以亂碼返回
究竟在發(fā)生亂碼時是哪一種情況呢娩井?
我們先在mysql命令行下輸入
show variables like‘%char%’;
查看mysql字符集設(shè)置情況:
文件系統(tǒng)字符集是固定的暇屋,系統(tǒng)、服務(wù)器的字符集在安裝時確定洞辣,與亂碼問題無關(guān)咐刨;亂碼的問題與客戶端、數(shù)據(jù)庫連接屋彪、數(shù)據(jù)庫所宰、查詢結(jié)果的字符集設(shè)置有關(guān)。
*注:客戶端是看訪問mysql 數(shù)據(jù)庫的方式畜挥,通過命令行訪問仔粥,命令行窗口就是客戶端,通過JDBC 等連接訪問蟹但,程序就是客戶端躯泰。
我們在向mysql寫入中文數(shù)據(jù)時,在客戶端华糖、數(shù)據(jù)庫連接麦向、寫入數(shù)據(jù)庫時分別要進(jìn)行編碼轉(zhuǎn)換在執(zhí)行查詢時,在返回結(jié)果客叉、數(shù)據(jù)庫連接诵竭、客戶端分別進(jìn)行編碼轉(zhuǎn)換』案妫現(xiàn)在我們應(yīng)該清楚,亂碼發(fā)生在數(shù)據(jù)庫卵慰、客戶端沙郭、查詢結(jié)果以及數(shù)據(jù)庫連接這其中一個或多個環(huán)節(jié)。
為什么從命令行直接寫入中文不設(shè)置也不會出現(xiàn)亂碼裳朋?
可以明確的是從命令行下病线,客戶端、數(shù)據(jù)庫連接鲤嫡、查詢結(jié)果的字符集設(shè)置沒有變化輸入的中文經(jīng)過一系列轉(zhuǎn)碼又轉(zhuǎn)回初始的字符集送挑,我們查看到的當(dāng)然不是亂碼但這并不代表中文在數(shù)據(jù)庫里被正確作為中文字符存儲舉例來說,現(xiàn)在有一個utf8編碼數(shù)據(jù)庫暖眼,客戶端連接使用GBK 編碼惕耕,connection 使用默認(rèn)的ISO8859-1(也就是mysql 中的latin1),我們在客戶端發(fā)送“中文”這個字符串罢荡,客戶端將發(fā)送一串GBK 格式的二進(jìn)制碼給connection 層赡突,connection 層以ISO8859-1 格式將這段二進(jìn)制碼發(fā)送給數(shù)據(jù)庫,數(shù)據(jù)庫將這段編碼以utf8 格式存儲下來区赵,我們將這個字段以utf8格式讀取出來,肯定是得到亂碼浪南,也就是說中文數(shù)據(jù)在寫入數(shù)據(jù)庫時是以亂碼形式存儲的笼才,在同一個客戶端進(jìn)行查詢操作時,做了一套和寫入時相反的操作络凿,錯誤的utf8 格式二進(jìn)制碼又被轉(zhuǎn)換成正確的GBK 碼并正確顯示出來骡送。
我們可以通過之前講的方法來設(shè)置相應(yīng)的字符集。通過這樣的設(shè)置絮记,整個數(shù)據(jù)寫入讀出流程中都統(tǒng)一了字符集摔踱,就不會出現(xiàn)亂碼了。
如果是通過JDBC連接數(shù)據(jù)庫怨愤,可以這樣寫URL:
URL=jdbc:mysql://localhost:3306/abs?useUnicode=true&characterEncoding=字符集派敷,JSP 頁面等終端也要設(shè)置相應(yīng)的字符集。
2.2撰洗、R連接mysql亂碼問題
在R讀入mysql數(shù)據(jù)庫出現(xiàn)中文亂碼篮愉,首先確保mysql數(shù)據(jù)庫中字符集編碼格式為utf-8;然后在本地電腦上(windows系統(tǒng))的數(shù)據(jù)源設(shè)置中(控制面板–管理工具–數(shù)據(jù)源–配置–detail)設(shè)置好對應(yīng)mysql數(shù)據(jù)庫連接的字符集編碼為utf-8差导,然后在R中channel_temp=odbcConnect(“數(shù)據(jù)庫連接的名稱”,uid =”xx” ,pwd =”xx”,DBMSencoding=”UTF-8”)试躏,指定編碼,則不會出現(xiàn)亂碼设褐。
mysq NULL值替換
將mysql數(shù)據(jù)庫中的NULL值替換為空
UPDATE qrt_order_info SET order_num=” WHERE order_num=”NULL”;
原本在數(shù)據(jù)庫的NULL是被當(dāng)成字符串了颠蕴,不同的語言泣刹,表示空的方法都不一樣,mysql中是”\N”;所有可以在加載數(shù)據(jù)前犀被,對NULL作替換椅您,這樣一次性導(dǎo)入后就自動為空,而不需要像上面那樣去數(shù)據(jù)庫里UPDATE了弱判。
mysql內(nèi)NULL判斷的是空襟沮,就是沒有任何值,此時用navicat查看的時候可以看淡灰色的”(Null)”字樣昌腰;有時會在數(shù)據(jù)庫記錄的某一欄看不到任務(wù)記錄开伏,此時未必是NULL,有可能是空字符遭商,”固灵,只不過字符串里沒任何內(nèi)容,此時要用where xx=”劫流;