mysql的校對規(guī)則引起的問題分析
更新時間:2008年10月25日 18:56:39 作者:
在以前用oracle的時候,很少關(guān)于它的collation方法,但是在mysql中,這點不加注意的話,卻有可能會出現(xiàn)問題。
問題是這樣的:
一張test的表,字符集采用的latin1。
select to_id from test where to_id='cn象_王';
+---------------+
| to_id |
+---------------+
| cn陶_陶 |
| cn象_王 |
+---------------+
2 rows in set (0.00 sec)
取cn象_王的數(shù)據(jù),居然把cn陶_陶的數(shù)據(jù)也取回來了。
這顯然是不允許的。
查看它們的編碼:
(root@im_offlog1a:)[test]> select hex('cn陶_陶');
+----------------+
| hex('cn陶_陶') |
+----------------+
| 636ECCD55FCCD5 |
+----------------+
1 row in set (0.00 sec)
(root@im_offlog1a:)[test]> select hex('cn象_王');
+----------------+
| hex('cn象_王') |
+----------------+
| 636ECFF35FCDF5 |
+----------------+
1 row in set (0.00 sec)
編碼的確是不一樣的,但是為什么mysql會認(rèn)為這兩條記錄是一樣的呢?
一開始我們就把問題定位于collation引起的問題。
show variables查看
| collation_connection | latin1_swedish_ci
| collation_database | latin1_swedish_ci
| collation_server | latin1_swedish_ci
手工把這些參數(shù)修改為latin1_bin,結(jié)果居然一樣。這下感覺真是奇怪了。
這里先解釋一下mysql collation的命名規(guī)則:
它們以其相關(guān)的字符集名開始,通常包括一個語言名,并且以_ci(大小寫不敏感)、_cs(大小寫敏感)或_bin(二元)結(jié)束
比如latin1字符集有以下幾種校正規(guī)則:
校對規(guī)則 含義
latin1_german1_ci 德國DIN-1
latin1_swedish_ci 瑞典/芬蘭
latin1_danish_ci 丹麥/挪威
latin1_german2_ci 德國 DIN-2
latin1_bin 符合latin1編碼的二進制
latin1_general_ci 多種語言(西歐)
latin1_general_cs 多種語言(西歐ISO),大小寫敏感
latin1_spanish_ci 現(xiàn)代西班牙
最后我們將表格重建,手工指定表格級別的collation為latin1_bin。
這個問題就得到了解決。
那么問題又來了,為什么我前面手工測試latin1_bin時不生效呢?
原來MySQL按照下面的方式選擇表字符集和 校對規(guī)則:
如果指定了CHARACTER SET X和COLLATE Y,那么采用CHARACTER SET X和COLLATE Y。
如果指定了CHARACTER SET X而沒有指定COLLATE Y,那么采用CHARACTER SET X和CHARACTER SET X的默認(rèn)校對規(guī)則。
否則,采用服務(wù)器字符集和服務(wù)器校對規(guī)則。
而我們在建表的時候指定了character set,所以它永遠(yuǎn)是采用對應(yīng)的默認(rèn)的校對規(guī)則。
當(dāng)然我們其實也沒必要重建表格,只需要alter table db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin這樣轉(zhuǎn)換即可。
另外建議collation都盡量采用字符集相應(yīng)的bin類型的校對規(guī)則,這樣不容易出錯
一張test的表,字符集采用的latin1。
select to_id from test where to_id='cn象_王';
+---------------+
| to_id |
+---------------+
| cn陶_陶 |
| cn象_王 |
+---------------+
2 rows in set (0.00 sec)
取cn象_王的數(shù)據(jù),居然把cn陶_陶的數(shù)據(jù)也取回來了。
這顯然是不允許的。
查看它們的編碼:
(root@im_offlog1a:)[test]> select hex('cn陶_陶');
+----------------+
| hex('cn陶_陶') |
+----------------+
| 636ECCD55FCCD5 |
+----------------+
1 row in set (0.00 sec)
(root@im_offlog1a:)[test]> select hex('cn象_王');
+----------------+
| hex('cn象_王') |
+----------------+
| 636ECFF35FCDF5 |
+----------------+
1 row in set (0.00 sec)
編碼的確是不一樣的,但是為什么mysql會認(rèn)為這兩條記錄是一樣的呢?
一開始我們就把問題定位于collation引起的問題。
show variables查看
| collation_connection | latin1_swedish_ci
| collation_database | latin1_swedish_ci
| collation_server | latin1_swedish_ci
手工把這些參數(shù)修改為latin1_bin,結(jié)果居然一樣。這下感覺真是奇怪了。
這里先解釋一下mysql collation的命名規(guī)則:
它們以其相關(guān)的字符集名開始,通常包括一個語言名,并且以_ci(大小寫不敏感)、_cs(大小寫敏感)或_bin(二元)結(jié)束
比如latin1字符集有以下幾種校正規(guī)則:
校對規(guī)則 含義
latin1_german1_ci 德國DIN-1
latin1_swedish_ci 瑞典/芬蘭
latin1_danish_ci 丹麥/挪威
latin1_german2_ci 德國 DIN-2
latin1_bin 符合latin1編碼的二進制
latin1_general_ci 多種語言(西歐)
latin1_general_cs 多種語言(西歐ISO),大小寫敏感
latin1_spanish_ci 現(xiàn)代西班牙
最后我們將表格重建,手工指定表格級別的collation為latin1_bin。
這個問題就得到了解決。
那么問題又來了,為什么我前面手工測試latin1_bin時不生效呢?
原來MySQL按照下面的方式選擇表字符集和 校對規(guī)則:
如果指定了CHARACTER SET X和COLLATE Y,那么采用CHARACTER SET X和COLLATE Y。
如果指定了CHARACTER SET X而沒有指定COLLATE Y,那么采用CHARACTER SET X和CHARACTER SET X的默認(rèn)校對規(guī)則。
否則,采用服務(wù)器字符集和服務(wù)器校對規(guī)則。
而我們在建表的時候指定了character set,所以它永遠(yuǎn)是采用對應(yīng)的默認(rèn)的校對規(guī)則。
當(dāng)然我們其實也沒必要重建表格,只需要alter table db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin這樣轉(zhuǎn)換即可。
另外建議collation都盡量采用字符集相應(yīng)的bin類型的校對規(guī)則,這樣不容易出錯
相關(guān)文章
MYSQL必知必會讀書筆記第四章之檢索數(shù)據(jù)
MySQL是一種開放源代碼的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)(RDBMS)。接下來通過本文給大家介紹MYSQL必知必會讀書筆記第四章之檢索數(shù)據(jù),感興趣的朋友一起學(xué)習(xí)吧2016-05-05mysql數(shù)據(jù)庫SQL子查詢(史上最詳細(xì))
這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫SQL子查詢的相關(guān)資料,子查詢指的是嵌套在某個語句中的SELECT語句, MySQL支持標(biāo)準(zhǔn)SQL所要求的所有子查詢形式和操作,此外還進行了一些擴展,需要的朋友可以參考下2024-05-05mysql判斷當(dāng)前時間是否在開始與結(jié)束時間之間且開始與結(jié)束時間允許為空
這篇文章主要介紹了mysql判斷當(dāng)前時間是否在開始與結(jié)束時間之間且開始與結(jié)束時間允許為空,文中通過示例代碼介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下2021-09-09使用canal監(jiān)控mysql數(shù)據(jù)庫實現(xiàn)elasticsearch索引實時更新問題
這篇文章主要介紹了使用canal監(jiān)控mysql數(shù)據(jù)庫實現(xiàn)elasticsearch索引實時更新,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2022-03-03一文帶你永久擺脫Mysql時區(qū)錯誤問題(idea數(shù)據(jù)庫可視化插件配置)
在MySQL啟動時會檢查當(dāng)前系統(tǒng)的時區(qū)并根據(jù)系統(tǒng)時區(qū)設(shè)置全局參數(shù)system_time_zone的值,下面這篇文章主要給大家介紹了關(guān)于如何永久擺脫Mysql時區(qū)錯誤問題(idea數(shù)據(jù)庫可視化插件配置)的相關(guān)資料,需要的朋友可以參考下2022-08-08mysql根據(jù)逗號將一行數(shù)據(jù)拆分成多行數(shù)據(jù)
本文主要介紹了mysql根據(jù)逗號將一行數(shù)據(jù)拆分成多行數(shù)據(jù),文中通過示例代碼介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下2021-12-12