在 MySQL 中使用 BINARY 是什么意思

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

数据库版本:

MySQL 5.6.26

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

创建测试表，插入数据：

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关键字可以走索引：

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关键字是可以走索引的：

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关键字默认不会区分大小写：

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怎么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!