生活随笔
收集整理的這篇文章主要介紹了
mysql的collation区分大小写设置
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
mysql的collation區分大小寫設置
?
????? mysql數據庫在做查詢時候，有時候是英文字母大小寫敏感的，有時候又不是的，主要是由于mysql的字符校驗規則的設置。通常默認是不支持的大小寫字母敏感的，在主動設置mysql數據庫的collation后，可以使得數據庫滿足大小寫敏感，適合客戶的一定要求。通過下面的試驗進行理解學習.....
?
Sql代碼??
mysql>?select?version();?? +?? |?version()?|?? +?? |?5.5.25????|?? +?? 1?row?in?set?(0.00?sec)??
??
????
Sql代碼??
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???????|?D:\database\mysql\mysql-5.5.25-winx64\share\charsets\??
?
???
Sql代碼??
mysql>?show?variables?like?'%collation%';?? +?? |?Variable_name????????|?Value?????????????|?? +?? |?collation_connection?|?utf8_general_ci???|?? |?collation_database???|?latin1_swedish_ci?|?? |?collation_server?????|?latin1_swedish_ci?|?? +?? 3?rows?in?set?(0.00?sec)??
??? 默認即為：collation_connection = utf8_general_ci?? 大小寫不敏感校驗規則；
??
Sql代碼??
mysql>?show?collation?like?'%utf8%';?? +?? |?Collation????????????????|?Charset?|?Id??|?Default?|?Compiled?|?Sortlen?|?? +?? |?utf8_general_ci??????????|?utf8????|??33?|?Yes?????|?Yes??????|???????1?|?? |?utf8_bin?????????????????|?utf8????|??83?|?????????|?Yes??????|???????1?|?? |?utf8_unicode_ci??????????|?utf8????|?192?|?????????|?Yes??????|???????8?|?? |?utf8_icelandic_ci????????|?utf8????|?193?|?????????|?Yes??????|???????8?|??
??? 客戶端字符集：utf8, 校驗規則: utf8_general_ci, 默認為yes，即不是大小寫敏感的匹配；
??? 而utf8_bin是區分大小寫的校驗規則；
??? 創建表做測試，看數據效果：
?
???
Sql代碼??
mysql>?create?table?T_collation(first?varchar(30)?character?set?utf8?? ????->?collate?utf8_bin,second?varchar(30)?character?set?utf8?collate?? ????->?utf8_general_ci);?? Query?OK,?0?rows?affected?(0.32?sec)?? ?? mysql>?show?create?table?t_collation\G;?? ***************************?1.?row?***************************?? ???????Table:?t_collation?? Create?Table:?CREATE?TABLE?`t_collation`?(?? ??`first`?varchar(30)?CHARACTER?SET?utf8?COLLATE?utf8_bin?DEFAULT?NULL,?? ??`second`?varchar(30)?CHARACTER?SET?utf8?DEFAULT?NULL?? )?ENGINE=InnoDB?DEFAULT?CHARSET=latin1?? 1?row?in?set?(0.00?sec)?? ?? ERROR:?? No?query?specified?? ?? mysql>?insert?into?t_collation?values('M','M'),('N','N'),('a','a'),('b','b');?? Query?OK,?4?rows?affected?(0.13?sec)?? Records:?4??Duplicates:?0??Warnings:?0?? ?? mysql>?select?*?from?t_collation;?? +?? |?first?|?second?|?? +?? |?M?????|?M??????|?? |?N?????|?N??????|?? |?a?????|?a??????|?? |?b?????|?b??????|?? +?? 4?rows?in?set?(0.00?sec)??
??? 比較查詢結果：
???
Sql代碼??
mysql>?insert?into?t_collation?values('m','m'),('n','n');?? Query?OK,?2?rows?affected?(0.10?sec)?? Records:?2??Duplicates:?0??Warnings:?0?? ?? mysql>?select?*?from?t_collation;?? +?? |?first?|?second?|?? +?? |?M?????|?M??????|?? |?N?????|?N??????|?? |?a?????|?a??????|?? |?b?????|?b??????|?? |?m?????|?m??????|?? |?n?????|?n??????|?? +?? 6?rows?in?set?(0.00?sec)?? ?? mysql>?select?*?from?t_collation?where?first='m';?? +?? |?first?|?second?|?? +?? |?m?????|?m??????|?? +?? 1?row?in?set?(0.02?sec)?? ?? mysql>?select?*?from?t_collation?where?second='m';?? +?? |?first?|?second?|?? +?? |?M?????|?M??????|?? |?m?????|?m??????|?? +?? 2?rows?in?set?(0.00?sec)?? mysql>?select?*?from?t_collation?where?second='M';?? +?? |?first?|?second?|?? +?? |?M?????|?M??????|?? |?m?????|?m??????|?? +?? 2?rows?in?set?(0.00?sec)?? ?? mysql>?select?*?from?t_collation?where?first='M';?? +?? |?first?|?second?|?? +?? |?M?????|?M??????|?? +?? 1?row?in?set?(0.00?sec)??
??? 比較各自的校驗規則，utf8_bin是區分大小寫的，而utf8_general_ci是不區分的，默認的。
??? 還可以從排序語句中進行比較，看看測試效果的.....
?
???
Sql代碼??
mysql>?select?*?from?t_collation;?? +?? |?first?|?second?|?? +?? |?M?????|?M??????|?? |?N?????|?N??????|?? |?a?????|?a??????|?? |?b?????|?b??????|?? |?m?????|?m??????|?? |?n?????|?n??????|?? +?? 6?rows?in?set?(0.00?sec)?? ?? mysql>?select?*?from?t_collation?order?by?first;?? +?? |?first?|?second?|?? +?? |?M?????|?M??????|?? |?N?????|?N??????|?? |?a?????|?a??????|?? |?b?????|?b??????|?? |?m?????|?m??????|?? |?n?????|?n??????|?? +?? 6?rows?in?set?(0.00?sec)?? ?? mysql>?select?*?from?t_collation?order?by?second;?? +?? |?first?|?second?|?? +?? |?a?????|?a??????|?? |?b?????|?b??????|?? |?M?????|?M??????|?? |?m?????|?m??????|?? |?N?????|?N??????|?? |?n?????|?n??????|?? +?? 6?rows?in?set?(0.00?sec)??
?? 同樣符合校驗規則的檢查。
? 結論： 在MYSQL數據庫中，根據實際業務需要，適當可以調整字符集的(collation)校驗規則，修改默認的大小寫敏感問題，滿足實際需要，這本身就是數據庫的一種設置，熟悉標準、規則，適當利用為項目所用，可以針對具體的數據庫或者表或者表的列進行設置。
from:?http://xiaobo.iteye.com/blog/1830942
總結
以上是生活随笔為你收集整理的mysql的collation区分大小写设置的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
