MySQL中BINARY怎么用

這篇文章給大家分享的是有關(guān)MySQL中BINARY怎么用的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過來看看吧。

在寶安等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供網(wǎng)站建設(shè)、網(wǎng)站制作 網(wǎng)站設(shè)計(jì)制作定制開發(fā),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),成都品牌網(wǎng)站建設(shè),營銷型網(wǎng)站建設(shè),成都外貿(mào)網(wǎng)站建設(shè),寶安網(wǎng)站建設(shè)費(fèi)用合理。

數(shù)據(jù)庫版本:
MySQL 5.6.26

線上某業(yè)務(wù)表為了區(qū)分大小寫，使用BINARY關(guān)鍵字，正常來說使用這個(gè)關(guān)鍵字是走索引的，測試過程如下：

創(chuàng)建測試表，插入數(shù)據(jù)：

drop table  if EXISTS student;

CREATE TABLE `student` (
  `id` int(11) PRIMARY key auto_increment,
  `name` varchar(20) DEFAULT NULL,
key `idx_name`(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

insert into `student` ( `id`, `name`) values ( '1', 'michael');
insert into `student` ( `id`, `name`) values ( '2', 'lucy');
insert into `student` ( `id`, `name`) values ( '3', 'nacy');
insert into `student` ( `id`, `name`) values ( '4', 'mike');
insert into `student` ( `id`, `name`) values ( null, 'guo');
insert into `student` ( `id`, `name`) values ( '6', 'Guo');
不加BINARY關(guān)鍵字可以走索引：

mysql> desc select * from student where name = 'guo';
+----+-------------+---------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+----------+---------+-------+------+--------------------------+
| 1  | SIMPLE      | student | ref  | idx_name      | idx_name | 63      | const | 2    | Using where; Using index |
+----+-------------+---------+------+---------------+----------+---------+-------+------+--------------------------+
1 rows in set (0.03 sec)
正常來說BINARY關(guān)鍵字是可以走索引的：

mysql> desc select * from student where BINARY name = 'guo';
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
| 1  | SIMPLE      | student | index | NULL          | idx_name | 63      | NULL | 6    | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
1 rows in set (0.04 sec)
不使用BINARY關(guān)鍵字默認(rèn)不會(huì)區(qū)分大小寫：

mysql> select * from student where name = 'guo';
+----+------+
| id | name |
+----+------+
| 5  | guo  |
| 6  | Guo  |
+----+------+
2 rows in set (0.03 sec)

mysql> select * from student where name = 'Guo';
+----+------+
| id | name |
+----+------+
| 5  | guo  |
| 6  | Guo  |
+----+------+
2 rows in set (0.03 sec)
使用BINARY關(guān)鍵字可以區(qū)分大小寫：

mysql>  select * from student where BINARY name = 'guo';
+----+------+
| id | name |
+----+------+
| 5  | guo  |
+----+------+
1 rows in set (0.04 sec)

mysql>  select * from student where BINARY name = 'Guo';
+----+------+
| id | name |
+----+------+
| 6  | Guo  |
+----+------+
1 rows in set (0.03 sec)

mysql>
到這里以上都沒問題，但關(guān)鍵在于，業(yè)務(wù)的表結(jié)構(gòu)大于索引的最大長度即字串長度超過255。

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`(255))
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

mysql> desc select * from student where name = 'guo';
+----+-------------+---------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+----------+---------+-------+------+-------------+
| 1  | SIMPLE      | student | ref  | idx_name      | idx_name | 768     | const | 2    | Using where |
+----+-------------+---------+------+---------------+----------+---------+-------+------+-------------+
1 rows in set (0.04 sec)
加上BINARY關(guān)鍵字不再走索引：

mysql> desc select * from student where BINARY name = 'guo';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | student | ALL  | NULL          | NULL | NULL    | NULL | 6    | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 rows in set (0.05 sec)

mysql>
這時(shí)需要在表結(jié)構(gòu)里加上BINARY

mysql>ALTER TABLE student MODIFY COLUMN name VARCHAR(20) BINARY;
Query OK, 6 rows affected (0.06 sec)
數(shù)據(jù)庫會(huì)自動(dòng)轉(zhuǎn)換成COLLATE utf8_bin
collate關(guān)鍵字為校對集，主要是對字符集之間的比較和排序，可以通過 show collation查看所有的校對集

mysql> show create table student\G
*************************** 1. row ***************************
Table       : student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 rows in set (0.39 sec)

mysql>


mysql> desc select * from student where name = 'guo';
+----+-------------+---------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+----------+---------+-------+------+--------------------------+
| 1  | SIMPLE      | student | ref  | idx_name      | idx_name | 63      | const | 1    | Using where; Using index |
+----+-------------+---------+------+---------------+----------+---------+-------+------+--------------------------+
1 rows in set (0.07 sec)

mysql>
即可區(qū)分大小寫：

mysql> select * from student where name = 'guo';
+----+------+
| id | name |
+----+------+
| 5  | guo  |
+----+------+
1 rows in set (0.07 sec)

mysql> select * from student where name = 'Guo';
+----+------+
| id | name |
+----+------+
| 6  | Guo  |
+----+------+
1 rows in set (0.06 sec)

mysql>

感謝各位的閱讀！關(guān)于“MySQL中BINARY怎么用”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！

網(wǎng)站名稱：MySQL中BINARY怎么用
本文路徑：http://bm7419.com/article24/jjchje.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站制作、網(wǎng)站策劃、標(biāo)簽優(yōu)化、靜態(tài)網(wǎng)站、App設(shè)計(jì)、外貿(mào)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

小程序開發(fā)