MySQL中BINARY怎么用

这篇文章给大家分享的是有关关系型数据库中二进制的怎么用的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

数据库版本：

MySQL 5.6.26

线上某业务表为了区分大小写，使用二进制的关键字，正常来说使用这个关键字是走索引的，测试过程如下：

创建测试表，插入数据：

如果学生存在，则删除表；

创建表格"学生"(

` id ` int(11)PRIMAry key auto _ increment，

` name` varchar(20) DEFAULT NULL，

键` idx_name`(`name `)

)ENGINE=Innodb DEFAULT CHARSET=ut F8；

插入“学生”(“身份证”、“姓名”)值(“1”、“迈克尔”)；

插入“学生”(“身份证”、“姓名”)值(“2”、“露西”)；

插入“学生”(“身份证”、“姓名”)值(“3”、“nacy”)；

插入“学生”(“身份证”、“姓名”)值(“4”、“麦克”)；

插入"学生”(“身份证”、“姓名”)值(空，“国”)；

插入"学生”(“身份证”、“姓名”)值("6"、"国");

不加二进制的关键字可以走索引：

mysql desc从学生中选择*其中姓名='郭'

- - - - - - - - - -

| id | select _ type | table | type | optional _ keys | key | key _ len | ref | row | Extra |

- - - - - - - - - -

| 1 | SIMPLE | student | ref | idx _ name | idx _ name | 63 | const | 2 |使用位置；使用索引|

- - - - - - - - - -

一行一组(0.03秒)

正常来说二进制的关键字是可以走索引的：

mysql desc从学生中选择*其中BINARY name=' guo '

- - - - - - - - - -

| id | select _ type | table | type | optional _ keys | key | key _ len | ref | row | Extranbs

p;              |
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
| 1  | SIMPLE      | student | index | NULL          | idx_name | 63      | NULL | 6    | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
1 rows in set (0.04 sec)
不使用BINARY关键字默认不会区分大小写：

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关键字可以区分大小写：

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>
到这里以上都没问题，但关键在于，业务的表结构大于索引的最大长度即字串长度超过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关键字不再走索引：

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>
这时需要在表结构里加上BINARY

mysql>ALTER TABLE student MODIFY COLUMN name VARCHAR(20) BINARY;
Query OK, 6 rows affected (0.06 sec)
数据库会自动转换成COLLATE utf8_bin
collate关键字为校对集，主要是对字符集之间的比较和排序，可以通过 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>
即可区分大小写：

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>

感谢各位的阅读！关于“MySQL中BINARY怎么用”这篇文章就分享到这里了，希望