MYSQL的binary解決mysql數(shù)據(jù)大小寫敏感問題的方法

mysql> select binary 'ABCD'='abcd' COM1, 'ABCD'='abcd' COM2;

+--------+-----------+

| COM1 | COM2 |

+--------+-----------+

| 0 | 1 |

+---------+-----------+

1 row in set (0.00 sec)

(僅僅有些而已！4.以前)
因?yàn)橛械腗ySQL特別是4.以前的對于中文檢索會有不準(zhǔn)確的問題摔癣，可以在檢索的時候加上binary奴饮。
建表：

代碼如下:

create TABLE usertest (

id int(9) unsigned NOT NULL auto_increment,

username varchar(30) NOT NULL default '',

primary key (id)

)

代碼如下:

insert into usertest (username) VALUES('美文');

insert into usertest (username) VALUES('美國項(xiàng)目');

insert into usertest (username) VALUES('李文');

insert into usertest (username) VALUES('老唐');

insert into usertest (username) VALUES('夢漂');

insert into usertest (username) VALUES('龍武');

insert into usertest (username) VALUES('夏');

如果使用=而不是like的時候，select * from usertest where username = '夏' 琢岩，只出現(xiàn)一個結(jié)果投剥。因?yàn)閙ysql 的LIKE操作是按照ASCII 操作的，所以LIKE的時候是可能有問題的担孔。問題繼續(xù)：如果再加上：

代碼如下:insert into usertest (username) VALUES('文');

insert into usertest (username) VALUES('唐');

1.在create的時候就使用binary泳桦，而不是在query的時候加。

代碼如下:

username varchar(30) BINARY NOT NULL default '', 如果表已經(jīng)建好了娩缰，使用：

alter table usertest modify username varchar(32) binary; 來就該表的屬性。

char使用固定長度的空間進(jìn)行存儲，char(4)存儲4個字符泰鸡，根據(jù)編碼方式的不同占用不同的字節(jié)债蓝，gbk編碼方式，不論是中文還是英文盛龄，每個字符占用2個字節(jié)的空間饰迹，utf8編碼方式，每個字符占用3個字節(jié)的空間余舶。

如果需要存儲的字符串的長度跟所有值的平均長度相差不大啊鸭，適合用char，如MD5匿值。

對于經(jīng)常改變的值赠制，char優(yōu)于varchar，原因是固定長度的行不容易產(chǎn)生碎片挟憔。

對于很短的列钟些，char優(yōu)于varchar，原因是varchar需要額外一個或兩個字節(jié)存儲字符串的長度绊谭。

varchar保存可變長度的字符串政恍，使用額外的一個或兩個字節(jié)存儲字符串長度，varchar(10),除了需要存儲10個字符达传，還需要1個字節(jié)存儲長度信息(10),超過255的長度需要2個字節(jié)來存儲

例外：Myisam引擎中使用ROW_FORMAT=FIXED時抚垃，每行使用相同的空間，造成浪費(fèi)

char和varchar后面如果有空格趟大，char會自動去掉空格后存儲鹤树，varchar雖然不會去掉空格，但在進(jìn)行字符串比較時逊朽，會去掉空格進(jìn)行比較

代碼如下:

+-------+--------------+------+-----+---------+----------------+

+-------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| bn | varbinary(4) | YES | | NULL | |

| b | binary(8) | YES | | NULL | |

+-------+--------------+------+-----+---------+----------------+

+----------------------+----------------------+

| concat("$",name,"$") | concat("$",addr,"$") |

+----------------------+----------------------+

| $asdf$ | $a$ |

| $a $ | $a$ |

| $a$ | $a$ |

| $t a$ | $a$ |

+----------------------+----------------------+

mysql> select * from zcy where name='a '; //由于name是varchar罕伯，比較時，'a '自動轉(zhuǎn)換為'a'

+----+------+------+------+----------+

| id | name | addr | bn | b |

+----+------+------+------+----------+

| 3 | a | a | ab | ab |

| 4 | a | a | ab | a |

+----+------+------+------+----------+

2 rows in set (0.00 sec)

mysql> select * from zcy where name='a';

+----+------+------+------+----------+

| id | name | addr | bn | b |

+----+------+------+------+----------+

| 3 | a | a | ab | ab |

| 4 | a | a | ab | a |

+----+------+------+------+----------+

2 rows in set (0.00 sec)

+-------+--------------+------+-----+---------+----------------+

+-------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| bn | varbinary(4) | YES | | NULL | |

| b | binary(8) | YES | | NULL | |

+-------+--------------+------+-----+---------+----------------+

+--------------------+-------------------+

| concat("$",bn,"$") | concat("$",b,"$") |

+--------------------+-------------------+

| $ab a$ | NULL |

| $ab $ | $ab $ |

| $ab$ | $ab $ |

| $ab $ | $a $ |

| NULL | $a $ |

| NULL | $abcde $ |

| NULL | $abcd1234$ |

+--------------------+-------------------+

binary(8)可以保存8個字符，每個字符占1個字節(jié)岛蚤，共占8個字節(jié)

進(jìn)行比較時是按字節(jié)進(jìn)行比較邑狸，而不是按字符（char），按字節(jié)比較比字符簡單快速

按字符比較不區(qū)分大小寫涤妒，而binary區(qū)分大小寫单雾，結(jié)尾使用\0填充，而不是空格

代碼如下:

mysql> select * from zcy where b='a\0\0\0\0\0\0\0';

+----+------+------+------+----------+

| id | name | addr | bn | b |

+----+------+------+------+----------+

| 5 | t a | a | NULL | a |

+----+------+------+------+----------+

mysql> select * from zcy where b='a \0\0\0\0\0\0';

+----+------+------+------+----------+

| id | name | addr | bn | b |

+----+------+------+------+----------+

| 4 | a | a | ab | a |

+----+------+------+------+----------+

varbinary保存變長的字符串，后面不會補(bǔ)\0

mysql> select * from zcy where bn='ab';

+----+------+------+------+----------+

| id | name | addr | bn | b |

+----+------+------+------+----------+

| 3 | a | a | ab | ab |

+----+------+------+------+----------+

1 row in set (0.01 sec)

mysql> select * from zcy where bn='ab ';

+----+------+------+------+----------+

| id | name | addr | bn | b |

+----+------+------+------+----------+

| 2 | asdf | a | ab | ab |

+----+------+------+------+----------+

1 row in set (0.00 sec)

mysql> select * from zcy where bn='ab ';

+----+------+------+------+----------+

| id | name | addr | bn | b |

+----+------+------+------+----------+

| 4 | a | a | ab | a |

+----+------+------+------+----------+

1 row in set (0.00 sec)

MySql中Blob與Text的區(qū)別

BLOB是一個二進(jìn)制大對象硅堆，可以容納可變數(shù)量的數(shù)據(jù)屿储。有4種BLOB類型：TINYBLOB、BLOB渐逃、MEDIUMBLOB和LONGBLOB够掠。它們只是可容納值的最大長度不同。

有4種TEXT類型：TINYTEXT茄菊、TEXT疯潭、MEDIUMTEXT和LONGTEXT。這些對應(yīng)4種BLOB類型面殖，有相同的最大長度和存儲需求竖哩。

BLOB 列被視為二進(jìn)制字符串(字節(jié)字符串)。TEXT列被視為非二進(jìn)制字符串(字符字符串)畜普。BLOB列沒有字符集期丰，并且排序和比較基于列值字節(jié)的數(shù)值值。TEXT列有一個字符集吃挑，并且根據(jù)字符集的校對規(guī)則對值進(jìn)行排序和比較钝荡。

在TEXT或BLOB列的存儲或檢索過程中，不存在大小寫轉(zhuǎn)換舶衬。

當(dāng)未運(yùn)行在嚴(yán)格模式時埠通，如果你為BLOB或TEXT列分配一個超過該列類型的最大長度的值值，值被截取以保證適合逛犹。如果截掉的字符不是空格端辱，將會產(chǎn)生一條警告。使用嚴(yán)格SQL模式虽画，會產(chǎn)生錯誤舞蔽，并且值將被拒絕而不是截取并給出警告。

在大多數(shù)方面码撰，可以將BLOB列視為能夠足夠大的VARBINARY列渗柿。同樣，可以將TEXT列視為VARCHAR列脖岛。BLOB和TEXT在以下幾個方面不同于VARBINARY和VARCHAR：

·當(dāng)保存或檢索BLOB和TEXT列的值時不刪除尾部空格朵栖。(這與VARBINARY和VARCHAR列相同）。

請注意比較時將用空格對TEXT進(jìn)行擴(kuò)充以適合比較的對象柴梆，正如CHAR和VARCHAR陨溅。

·對于BLOB和TEXT列的索引，必須指定索引前綴的長度绍在。對于CHAR和VARCHAR门扇，前綴長度是可選的雹有。

·BLOB和TEXT列不能有默認(rèn)值。

LONG和LONG VARCHAR對應(yīng)MEDIUMTEXT數(shù)據(jù)類型悯嗓。這是為了保證兼容性件舵。如果TEXT列類型使用BINARY屬性卸察，將為列分配列字符集的二元校對規(guī)則脯厨。

MySQL連接程序/ODBC將BLOB值定義為LONGVARBINARY，將TEXT值定義為LONGVARCHAR坑质。

由于BLOB和TEXT值可能會非常長合武，使用它們時可能遇到一些約束：

·當(dāng)排序時只使用該列的前max_sort_length個字節(jié)。max_sort_length的默認(rèn)值是1024涡扼；該值可以在啟動mysqld服務(wù)器時使用--max_sort_length選項(xiàng)進(jìn)行更改稼跳。

運(yùn)行時增加max_sort_length的值可以在排序或組合時使更多的字節(jié)有意義。任何客戶端可以更改其會話max_sort_length變量的值：

代碼如下:

mysql> SET max_sort_length = 2000;

mysql> SELECT id, comment FROM tbl_name

-> ORDER BY comment;</font>

<font size="4" style="background-color: inherit; color: rgb(0, 0, 0); font-family: "Microsoft YaHei", STXihei; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">當(dāng)你想要使超過max_sort_length的字節(jié)有意義吃沪，對含長值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一種方式是將列值轉(zhuǎn)換為固定長度的對象汤善。標(biāo)準(zhǔn)方法是使用SUBSTRING函數(shù)。例如票彪，下面的語句對comment列的2000個字節(jié)進(jìn)行排序：

代碼如下:

mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name

-> ORDER BY SUBSTRING(comment,1,2000);</font>

<font size="4" style="background-color: inherit; color: rgb(0, 0, 0); font-family: "Microsoft YaHei", STXihei; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">·BLOB或TEXT對象的最大大小由其類型確定红淡，但在客戶端和服務(wù)器之間實(shí)際可以傳遞的最大值由可用內(nèi)存數(shù)量和通信緩存區(qū)大小確定。你可以通過更改max_allowed_packet變量的值更改消息緩存區(qū)的大小降铸，但必須同時修改服務(wù)器和客戶端程序在旱。例如，可以使用mysql和mysqldump來更改客戶端的max_allowed_packet值推掸。

每個BLOB或TEXT值分別由內(nèi)部分配的對象表示桶蝎。這與其它列類型形成對比，后者是當(dāng)打開表時為每1列分配存儲引擎谅畅。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末登渣，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子毡泻，更是在濱河造成了極大的恐慌胜茧，老刑警劉巖，帶你破解...
沈念sama閱讀 218,204評論 6贊 506
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件牙捉，死亡現(xiàn)場離奇詭異竹揍，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)邪铲，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,091評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門芬位，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人带到，你說我怎么就攤上這事昧碉。” “怎么了？”我有些...
開封第一講書人閱讀 164,548評論 0贊 354
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵被饿，是天一觀的道長四康。經(jīng)常有香客問我，道長狭握，這世上最難降的妖魔是什么闪金？我笑而不...
開封第一講書人閱讀 58,657評論 1贊 293
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮论颅，結(jié)果婚禮上哎垦，老公的妹妹穿的比我還像新娘。我一直安慰自己恃疯，他們只是感情好漏设，可當(dāng)我...
茶點(diǎn)故事閱讀 67,689評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著今妄，像睡著了一般郑口。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上盾鳞，一...
開封第一講書人閱讀 51,554評論 1贊 305
城市分裂傳說
那天犬性，我揣著相機(jī)與錄音，去河邊找鬼雁仲。笑死仔夺，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的攒砖。我是一名探鬼主播缸兔，決...
沈念sama閱讀 40,302評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼吹艇！你這毒婦竟也來了惰蜜？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 39,216評論 0贊 276
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤受神，失蹤者是張志新（化名）和其女友劉穎抛猖，沒想到半個月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體鼻听，經(jīng)...
沈念sama閱讀 45,661評論 1贊 314
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡财著，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,851評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了撑碴。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片撑教。...
茶點(diǎn)故事閱讀 39,977評論 1贊 348
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖醉拓，靈堂內(nèi)的尸體忽然破棺而出伟姐，到底是詐尸還是另有隱情收苏，我是刑警寧澤，帶...
沈念sama閱讀 35,697評論 5贊 347
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布愤兵，位于F島的核電站鹿霸，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏秆乳。R本人自食惡果不足惜懦鼠，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,306評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望矫夷。院中可真熱鬧葛闷，春花似錦憋槐、人聲如沸双藕。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,898評論 0贊 22
一樁弒父案阳仔，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽忧陪。三九已至，卻和暖如春近范，著一層夾襖步出監(jiān)牢的瞬間嘶摊，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,019評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工评矩，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留叶堆，地道東北人。一個月前我還...
沈念sama閱讀 48,138評論 3贊 370
代替公主和親
正文我出身青樓斥杜，卻偏偏與公主長得像虱颗，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子蔗喂，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,927評論 2贊 355

MYSQL的binary解決mysql數(shù)據(jù)大小寫敏感問題的方法

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