MYSQL binary analysis what is the meaning

BINARY is not a function, is a type conversion operator, which is used to force the string after it to be a binary string, which can be understood as a case-sensitive comparison of strings as follows:
mysql> select binary 'ABCD' = 'abcd' COM1, 'ABCD' = 'abcd' COM2;
+ -------- + ----------- +
| COM1 | COM2 |
+ -------- + ----------- +
| 0 | 1 |
+ --------- + ----------- +
1 row in set (0.00 sec)

____________________________________________________________
(Just a little more! 4. * before)

Because some MySQL is especially 4. * Formerly Chinese search will be inaccurate, you can add binary in the search.

Build table:

create TABLE usertest (
id int (9) unsigned NOT NULL auto_increment,
username varchar (30) NOT NULL default '',
primary key (id)
)
Insert data:

insert into usertest (username) VALUES ('美文');
insert into usertest (username) VALUES ('American Project');
insert into usertest (username) VALUES ('Li Wen');
insert into usertest (username) VALUES ('Old Tang');
insert into usertest (username) VALUES ('dream drift');
insert into usertest (username) VALUES ('Long Wu');
insert into usertest (username) VALUES ('summer');

For example: select * from usertest where username like '% summer%', the results of seven records are out, more depressed.

If you use = instead of like, select * from usertest where username = 'summer', there is only one result. Because mysql LIKE operation is in accordance with the ASCII operation, so when LIKE may be a problem. The problem continues: If you add:

insert into usertest (username) VALUES ('文');

insert into usertest (username) VALUES ('Don');

Or use select * from usertest where username = 'summer', the result is still 3 records, but also depressed. The solution is as follows:

1. Use binary in create, not in the query.

username varchar (30) BINARY NOT NULL default '', if the table has been built, use:

alter table usertest modify username varchar (32) binary; To the properties of the table.

2. In the query with binary, select * from usertest where username like binary '% summer%', you can accurately check out a record.