MYSQL 數(shù)據(jù)亂碼和字符集問題

顧穎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=”劫流;

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末巫玻,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子祠汇,更是在濱河造成了極大的恐慌仍秤,老刑警劉巖,帶你破解...
    沈念sama閱讀 216,402評論 6 499
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件可很,死亡現(xiàn)場離奇詭異诗力,居然都是意外死亡,警方通過查閱死者的電腦和手機(jī)我抠,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,377評論 3 392
  • 文/潘曉璐 我一進(jìn)店門苇本,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人菜拓,你說我怎么就攤上這事瓣窄。” “怎么了纳鼎?”我有些...
    開封第一講書人閱讀 162,483評論 0 353
  • 文/不壞的土叔 我叫張陵俺夕,是天一觀的道長。 經(jīng)常有香客問我喷橙,道長啥么,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 58,165評論 1 292
  • 正文 為了忘掉前任贰逾,我火速辦了婚禮悬荣,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘疙剑。我一直安慰自己氯迂,他們只是感情好践叠,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,176評論 6 388
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著嚼蚀,像睡著了一般禁灼。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上轿曙,一...
    開封第一講書人閱讀 51,146評論 1 297
  • 那天弄捕,我揣著相機(jī)與錄音,去河邊找鬼导帝。 笑死守谓,一個胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的您单。 我是一名探鬼主播斋荞,決...
    沈念sama閱讀 40,032評論 3 417
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼虐秦!你這毒婦竟也來了平酿?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 38,896評論 0 274
  • 序言:老撾萬榮一對情侶失蹤悦陋,失蹤者是張志新(化名)和其女友劉穎蜈彼,沒想到半個月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體俺驶,經(jīng)...
    沈念sama閱讀 45,311評論 1 310
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡柳刮,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,536評論 2 332
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了痒钝。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 39,696評論 1 348
  • 序言:一個原本活蹦亂跳的男人離奇死亡痢毒,死狀恐怖送矩,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情哪替,我是刑警寧澤栋荸,帶...
    沈念sama閱讀 35,413評論 5 343
  • 正文 年R本政府宣布,位于F島的核電站凭舶,受9級特大地震影響晌块,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜帅霜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,008評論 3 325
  • 文/蒙蒙 一饵逐、第九天 我趴在偏房一處隱蔽的房頂上張望漓踢。 院中可真熱鬧,春花似錦役听、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,659評論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至铃辖,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間猪叙,已是汗流浹背娇斩。 一陣腳步聲響...
    開封第一講書人閱讀 32,815評論 1 269
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留穴翩,地道東北人犬第。 一個月前我還...
    沈念sama閱讀 47,698評論 2 368
  • 正文 我出身青樓,卻偏偏與公主長得像藏否,于是被迫代替她去往敵國和親瓶殃。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,592評論 2 353

推薦閱讀更多精彩內(nèi)容