MySQL fuzzy query (like) case-sensitive

Problem Description: Through the above statement, you will find that the MySQL like query is not case-sensitive, because my mistake, Joe was written to find this stuff. But, sometimes, we need to be case-sensitive, what should we do? Here's how to fix it:

Method One (Specify case-sensitive when querying)

Quite simply, add a binary to the back of like and apply to the case where the table structure is not easy to change. Most people find this problem, often the structure of the table can not be changed, so this method is very good.

Then there are other ways to set the size of the table.

can also be identified when the table is built

CREATE TABLE table_name (

A varchar (a) binary

)

In-depth understanding:

The first thing you need to know is that in MySQL, the naming method for column collate's conventions is as follows:

*_bin: Indicates a binary case sensitive collation, which means

*_cs:case sensitive collation, case-sensitive

*_ci:case insensitive collation, case insensitive

Set the character to be case-sensitive during the construction of the table, and then insert the 3 record test.

Most of the time, our first build table is not comprehensive, I think, can be added later, modify the characteristics of the table, this is possible.

Just the Student_web watch.

But, it failed. I don't know why, I hope you can give me help.

The above is case-sensitive using _bin, and you can also use the

CREATE TABLE table_name (Word VARCHAR) CHARACTER SET latin1 COLLATE latin1_general_cs;

Optionally, specify collation at query time

When the table is built:mysql> CREATE TABLE table_name (Word VARCHAR) CHARACTER SET latin1;

Query:mysql> SELECT * FROM table_name WHERE word COLLATE latin1_bin like ' f% '; Put it in front of like

or:mysql> SELECT * FROM table_name WHERE word like ' f% ' COLLATE latin1_bin; Put it in the last

or:mysql> SELECT * from case_test WHERE word like ' f% ' COLLATE latin1_general_cs;

Reference: http://www.cnblogs.com/pinnasky/archive/2012/09/11/2680264.html

MySQL fuzzy query (like) case-sensitive