MYSQL的binary解決mysql數(shù)據(jù)大小寫敏感問題的方法
更新時間：2013年09月22日 15:25:20 作者：
BINARY不是函數(shù)，是類型轉(zhuǎn)換運算符，它用來強制它后面的字符串為一個二進制字符串，可以理解為在字符串比較的時候區(qū)分大小寫
復制代碼 代碼如下:
mysql> select binary 'ABCD'='abcd' COM1, 'ABCD'='abcd' COM2;
+--------+-----------+
| COM1 | COM2 |
+--------+-----------+
| 0 | 1 |
+---------+-----------+
1 row in set (0.00 sec)
(僅僅有些而已！4.*以前)
因為有的MySQL特別是4.*以前的對于中文檢索會有不準確的問題，可以在檢索的時候加上binary。
建表：
復制代碼 代碼如下:
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。這些對應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對應MEDIUMTEXT數(shù)據(jù)類型。這是為了保證兼容性。如果TEXT列類型使用BINARY屬性，將為列分配列字符集的二元 校對規(guī)則。
MySQL連接程序/ODBC將BLOB值定義為LONGVARBINARY，將TEXT值定義為LONGVARCHAR。
由于BLOB和TEXT值可能會非常長，使用它們時可能遇到一些約束：
·當排序時只使用該列的前max_sort_length個字節(jié)。max_sort_length的 默認值是1024；該值可以在啟動mysqld服務器時使用--max_sort_length選項進行更改。
運行時增加max_sort_length的值可以在排序或組合時使更多的字節(jié)有意義。任何客戶端可以更改其會話max_sort_length變量的值：
復制代碼 代碼如下:
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對象的最大大小由其類型確定，但在客戶端和服務器之間實際可以傳遞的最大值由可用內(nèi)存數(shù)量和通信緩存區(qū)大小確定。你可以通過更改max_allowed_packet變量的值更改消息緩存區(qū)的大小，但必須同時修改服務器和客戶端程序。例如，可以使用mysql和mysqldump來更改客戶端的max_allowed_packet值。
每個BLOB或TEXT值分別由內(nèi)部分配的對象表示。這與其它列類型形成對比，后者是當打開表時為每1列分配存儲引擎。
相關文章
win2008下mysql8.0.11升級mysql8.0.17版本詳細步驟
這篇文章主要為大家詳細介紹了win2008下mysql8.0.11升級mysql8.0.17版本詳細步驟，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-08-08
這篇文章主要介紹了SQL-?join多表關聯(lián)問題，具有很好的參考價值，希望對大家有所幫助。2022-12-12
這篇文章主要介紹了微信公眾平臺開發(fā) 數(shù)據(jù)庫操作的相關資料,需要的朋友可以參考下2016-10-10
這篇文章主要介紹了mysql使用報錯1142(42000)的問題及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
一臺linux主機啟動多個MySQL數(shù)據(jù)庫的方法
這篇文章主要介紹了一臺linux主機啟動多個MySQL數(shù)據(jù)庫的方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-03-03
這篇文章主要介紹了MySQL聚合查詢案例講解,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-03-03
mysql 5.7.17 winx64解壓版安裝配置方法圖文教程
這篇文章主要為大家詳細介紹了mysql 5.7.17 winx64解壓版安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-06-06
這篇文章主要為大家詳細介紹了mysql 8.0.15 安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-03-03
這篇文章主要為大家詳細介紹了CentOS6.8使用cmake安裝MySQL5.7.18的相關資料，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-04-04
本文主要介紹了MySQL 分庫分表實踐，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2022-03-03