《Mysql必讀MYSQL的binary解決mysql數據大小寫敏感問題的方法》要點：
本文介紹了Mysql必讀MYSQL的binary解決mysql數據大小寫敏感問題的方法，希望對您有用。如果有疑問，可以聯系我們。
代碼如下:
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學習
代碼如下:
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('美國項目');
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 '%夏%' ,結果七條記錄都出來了,比較郁悶.
如果使用=而不是like的時候,select * from usertest where username = '夏' ,只出現一個結果.因為mysql 的LIKE操作是按照ASCII 操作的,所以LIKE的時候是可能有問題的.問題繼續：如果再加上：
代碼如下:
insert into usertest (username) VALUES('文');
insert into usertest (username) VALUES('唐');
還是使用select * from usertest where username = '夏' ,結果還是出現3條記錄,又郁悶了.解決辦法如下：
1.在create的時候就使用binary,而不是在query的時候加.
代碼如下:
username varchar(30) BINARY NOT NULL default '', 如果表已經建好了,使用：
alter table usertest modify username varchar(32) binary; 來就該表的屬性.
2.在query的時候加上binary,select * from usertest where username like binary? '%夏%' ,就可以準確的查詢出一條記錄來.
char使用固定長度的空間進行存儲,char(4)存儲4個字符,根據編碼方式的不同占用不同的字節,gbk編碼方式,不論是中文還是英文,每個字符占用2個字節的空間,utf8編碼方式,每個字符占用3個字節的空間.
如果需要存儲的字符串的長度跟所有值的平均長度相差不大,適合用char,如MD5.
對于經常改變的值,char優于varchar,原因是固定長度的行不容易產生碎片.
對于很短的列,char優于varchar,原因是varchar需要額外一個或兩個字節存儲字符串的長度.
varchar保存可變長度的字符串,使用額外的一個或兩個字節存儲字符串長度,varchar(10),除了需要存儲10個字符,還需要1個字節存儲長度信息(10),超過255的長度需要2個字節來存儲
例外：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 '自動轉換為'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保存二進制字符串,它保存的是字節而不是字符,沒有字符集限制
binary(8)可以保存8個字符,每個字符占1個字節,共占8個字節
進行比較時是按字節進行比較,而不是按字符(char),按字節比較比字符簡單快速
按字符比較不區分大小寫,而binary區分大小寫,結尾使用\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的區別
BLOB是一個二進制大對象,可以容納可變數量的數據.有4種BLOB類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB.它們只是可容納值的最大長度不同.
有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT.這些對應4種BLOB類型,有相同的最大長度和存儲需求.
BLOB 列被視為二進制字符串(字節字符串).TEXT列被視為非二進制字符串(字符字符串).BLOB列沒有字符集,并且排序和比較基于列值字節的數值值.TEXT列有一個字符集,并且根據字符集的 校對規則對值進行排序和比較.
在TEXT或BLOB列的存儲或檢索過程中,不存在大小寫轉換.
當未運行在嚴格模式時,如果你為BLOB或TEXT列分配一個超過該列類型的最大長度的值值,值被截取以保證適合.如果截掉的字符不是空格,將會產生一條警告.使用嚴格SQL模式,會產生錯誤,并且值將被拒絕而不是截取并給出警告.
在大多數方面,可以將BLOB列視為能夠足夠大的VARBINARY列.同樣,可以將TEXT列視為VARCHAR列.BLOB和TEXT在以下幾個方面不同于VARBINARY和VARCHAR：
?當保存或檢索BLOB和TEXT列的值時不刪除尾部空格.(這與VARBINARY和VARCHAR列相同).
請注意比較時將用空格對TEXT進行擴充以適合比較的對象,正如CHAR和VARCHAR.
?對于BLOB和TEXT列的索引,必須指定索引前綴的長度.對于CHAR和VARCHAR,前綴長度是可選的.
?BLOB和TEXT列不能有 默認值.
LONG和LONG VARCHAR對應MEDIUMTEXT數據類型.這是為了保證兼容性.如果TEXT列類型使用BINARY屬性,將為列分配列字符集的二元 校對規則.
MySQL連接程序/ODBC將BLOB值定義為LONGVARBINARY,將TEXT值定義為LONGVARCHAR.
由于BLOB和TEXT值可能會非常長,使用它們時可能遇到一些約束：
?當排序時只使用該列的前max_sort_length個字節.max_sort_length的 默認值是1024；該值可以在啟動mysqld服務器時使用--max_sort_length選項進行更改.
運行時增加max_sort_length的值可以在排序或組合時使更多的字節有意義.任何客戶端可以更改其會話max_sort_length變量的值：
MYSQL學習
代碼如下:
mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM tbl_name
??? -> ORDER BY comment;
當你想要使超過max_sort_length的字節有意義,對含長值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一種方式是將列值轉換為固定長度的對象.標準方法是使用SUBSTRING函數.例如,下面的語句對comment列的2000個字節進行排序：
代碼如下:
mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name
??? -> ORDER BY SUBSTRING(comment,1,2000);
?BLOB或TEXT對象的最大大小由其類型確定,但在客戶端和服務器之間實際可以傳遞的最大值由可用內存數量和通信緩存區大小確定.你可以通過更改max_allowed_packet變量的值更改消息緩存區的大小,但必須同時修改服務器和客戶端程序.例如,可以使用mysql和mysqldump來更改客戶端的max_allowed_packet值.
每個BLOB或TEXT值分別由內部分配的對象表示.這與其它列類型形成對比,后者是當打開表時為每1列分配存儲引擎.
轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/3697.html
