《Mysql必讀MYSQL的binary解決mysql數(shù)據(jù)大小寫敏感問題的方法》要點：
本文介紹了Mysql必讀MYSQL的binary解決mysql數(shù)據(jù)大小寫敏感問題的方法，希望對您有用。如果有疑問，可以聯(lián)系我們。
代碼如下:
mysql> select binary 'ABCD'='abcd' COM1, 'ABCD'='abcd' COM2;
+--------+-----------+
| COM1 | COM2 |
+--------+-----------+
|????? 0???? |????? 1????? |
+---------+-----------+
1 row in set (0.00 sec)
(僅僅有些而已!4.*以前)
因為有的MySQL特別是4.*以前的對于中文檢索會有不準確的問題,可以在檢索的時候加上binary.
建表：
MYSQL學(xué)習
代碼如下:
create TABLE usertest (
id int(9) unsigned NOT NULL auto_increment,
username varchar(30) NOT NULL default '',
primary key (id)
)
插入數(shù)據(jù)：
代碼如下:
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('夢漂');
insert into usertest (username) VALUES('龍武');
insert into usertest (username) VALUES('夏');
例如：select * from usertest where username like '%夏%' ,結(jié)果七條記錄都出來了,比較郁悶.
如果使用=而不是like的時候,select * from usertest where username = '夏' ,只出現(xiàn)一個結(jié)果.因為mysql 的LIKE操作是按照ASCII 操作的,所以LIKE的時候是可能有問題的.問題繼續(xù)：如果再加上：
代碼如下:
insert into usertest (username) VALUES('文');
insert into usertest (username) VALUES('唐');
還是使用select * from usertest where username = '夏' ,結(jié)果還是出現(xiàn)3條記錄,又郁悶了.解決辦法如下：
1.在create的時候就使用binary,而不是在query的時候加.
代碼如下:
username varchar(30) BINARY NOT NULL default '', 如果表已經(jīng)建好了,使用：
alter table usertest modify username varchar(32) binary; 來就該表的屬性.
2.在query的時候加上binary,select * from usertest where username like binary? '%夏%' ,就可以準確的查詢出一條記錄來.
char使用固定長度的空間進行存儲,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時,每行使用相同的空間,造成浪費
char和varchar后面如果有空格,char會自動去掉空格后存儲,varchar雖然不會去掉空格,但在進行字符串比較時,會去掉空格進行比較
代碼如下:
+-------+--------------+------+-----+---------+----------------+
| Field | Type???????? | Null | Key | Default | Extra????????? |
+-------+--------------+------+-----+---------+----------------+
| id??? | int(11)????? | NO?? | PRI | NULL??? | auto_increment |
| name? | varchar(4)?? | YES? |???? | NULL??? |??????????????? |
| addr? | char(8)????? | YES? |???? | NULL??? |??????????????? |
| bn??? | varbinary(4) | YES? |???? | NULL??? |??????????????? |
| b???? | binary(8)??? | YES? |???? | NULL??? |??????????????? |
+-------+--------------+------+-----+---------+----------------+
+----------------------+----------------------+
| concat("$",name,"$") | concat("$",addr,"$") |
+----------------------+----------------------+
| $asdf$?????????????? | $a$????????????????? |
| $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)
?
+-------+--------------+------+-----+---------+----------------+
| Field | Type???????? | Null | Key | Default | Extra????????? |
+-------+--------------+------+-----+---------+----------------+
| id??? | int(11)????? | NO?? | PRI | NULL??? | auto_increment |
| name? | varchar(4)?? | YES? |???? | NULL??? |??????????????? |
| addr? | char(8)????? | YES? |???? | NULL??? |??????????????? |
| 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保存二進制字符串,它保存的是字節(jié)而不是字符,沒有字符集限制
binary(8)可以保存8個字符,每個字符占1個字節(jié),共占8個字節(jié)
進行比較時是按字節(jié)進行比較,而不是按字符(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保存變長的字符串,后面不會補\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是一個二進制大對象,可以容納可變數(shù)量的數(shù)據(jù).有4種BLOB類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB.它們只是可容納值的最大長度不同.
有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT.這些對應(yīng)4種BLOB類型,有相同的最大長度和存儲需求.
BLOB 列被視為二進制字符串(字節(jié)字符串).TEXT列被視為非二進制字符串(字符字符串).BLOB列沒有字符集,并且排序和比較基于列值字節(jié)的數(shù)值值.TEXT列有一個字符集,并且根據(jù)字符集的 校對規(guī)則對值進行排序和比較.
在TEXT或BLOB列的存儲或檢索過程中,不存在大小寫轉(zhuǎn)換.
當未運行在嚴格模式時,如果你為BLOB或TEXT列分配一個超過該列類型的最大長度的值值,值被截取以保證適合.如果截掉的字符不是空格,將會產(chǎn)生一條警告.使用嚴格SQL模式,會產(chǎn)生錯誤,并且值將被拒絕而不是截取并給出警告.
在大多數(shù)方面,可以將BLOB列視為能夠足夠大的VARBINARY列.同樣,可以將TEXT列視為VARCHAR列.BLOB和TEXT在以下幾個方面不同于VARBINARY和VARCHAR：
?當保存或檢索BLOB和TEXT列的值時不刪除尾部空格.(這與VARBINARY和VARCHAR列相同).
請注意比較時將用空格對TEXT進行擴充以適合比較的對象,正如CHAR和VARCHAR.
?對于BLOB和TEXT列的索引,必須指定索引前綴的長度.對于CHAR和VARCHAR,前綴長度是可選的.
?BLOB和TEXT列不能有 默認值.
LONG和LONG VARCHAR對應(yīng)MEDIUMTEXT數(shù)據(jù)類型.這是為了保證兼容性.如果TEXT列類型使用BINARY屬性,將為列分配列字符集的二元 校對規(guī)則.
MySQL連接程序/ODBC將BLOB值定義為LONGVARBINARY,將TEXT值定義為LONGVARCHAR.
由于BLOB和TEXT值可能會非常長,使用它們時可能遇到一些約束：
?當排序時只使用該列的前max_sort_length個字節(jié).max_sort_length的 默認值是1024；該值可以在啟動mysqld服務(wù)器時使用--max_sort_length選項進行更改.
運行時增加max_sort_length的值可以在排序或組合時使更多的字節(jié)有意義.任何客戶端可以更改其會話max_sort_length變量的值：
MYSQL學(xué)習
代碼如下:
mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM tbl_name
??? -> ORDER BY comment;
當你想要使超過max_sort_length的字節(jié)有意義,對含長值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一種方式是將列值轉(zhuǎn)換為固定長度的對象.標準方法是使用SUBSTRING函數(shù).例如,下面的語句對comment列的2000個字節(jié)進行排序：
代碼如下:
mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name
??? -> ORDER BY SUBSTRING(comment,1,2000);
?BLOB或TEXT對象的最大大小由其類型確定,但在客戶端和服務(wù)器之間實際可以傳遞的最大值由可用內(nèi)存數(shù)量和通信緩存區(qū)大小確定.你可以通過更改max_allowed_packet變量的值更改消息緩存區(qū)的大小,但必須同時修改服務(wù)器和客戶端程序.例如,可以使用mysql和mysqldump來更改客戶端的max_allowed_packet值.
每個BLOB或TEXT值分別由內(nèi)部分配的對象表示.這與其它列類型形成對比,后者是當打開表時為每1列分配存儲引擎.
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/3697.html
