Shulou(Shulou.com)05/31 Report--

This article is about how to use BINARY in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Database version:

MySQL 5.6.26

An online business table uses the BINARY keyword to be case-sensitive. Normally, this keyword is indexed. The test process is as follows:

Create a test table and insert data:

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 ('1students,' michael')

Insert into `student` (`id`, `name`) values ('2students,' lucy')

Insert into `student` (`id`, `name`) values ('3students,' nacy')

Insert into `student` (`id`, `name`) values ('4students,' mike')

Insert into `student` (`id`, `name`) values (null, 'guo')

Insert into `student` (`id`, `name`) values ('6students,' Guo')

You can go to the index without the BINARY keyword:

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)

Normally, the BINARY keyword can be indexed:

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)

Not using the BINARY keyword defaults to case insensitivity:

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)

Use the BINARY keyword to be case sensitive:

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 >

All of the above is fine, but the point is that the table structure of the business is greater than the maximum length of the index, that is, the string length is more than 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)

Add the BINARY keyword to stop indexing:

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.05sec)

Mysql >

At this point, you need to add BINARY to the table structure

Mysql > ALTER TABLE student MODIFY COLUMN name VARCHAR (20) BINARY

Query OK, 6 rows affected (0.06 sec)

The database is automatically converted to COLLATE utf8_bin

The collate keyword is the proofreading set, which is mainly used to compare and sort the character sets. You can view all proofing sets through 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 >

You can be case sensitive:

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 >

Thank you for reading! This is the end of the article on "how to use BINARY in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.