09月22日, 2014 163次
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怎么用”这篇文章就分享到这里了,希望