MySQL中BINARY怎么用
发布时间：2021-12-28 12:03:10 所属栏目：MySql教程 来源：互联网
导读：这篇文章给大家分享的是有关MySQL中BINARY怎么用的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 数据库版本: MySQL 5.6.26 线上某业务表为了区分大小写，使用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 studentG *************************** 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怎么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！ （编辑：汉中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐