MySQL like match query out incorrect Chinese solution

This article describes a solution that matches queries incorrectly when using a like statement in MySQL

MySQL like search when found, with

Select title from Tb_name where title like '%a% '

The result of the results in addition to include a name of the Chinese "new" also appear in the search results, which makes me want to find out how MySQL matches the pattern and rules in the end, and in the match when the regular expression is also very common!

This problem occurs because MySQL is case insensitive when querying strings, and when Yi MySQL is generally used as the default character set for the ISO-8859 character set, this behavior is caused by the Chinese encoding character-case conversion during comparison.

Workaround:

1. When building a table, add the "binary" attribute to the field containing the Chinese, making it a binary comparison, for example, "name char (10)" to "name Char (TEN) binary". But this is case-sensitive when you match the field of the table.

2. If you compile MySQL using the source code, you can use the--WITH--CHARSET=GBK parameter when compiling, so that MySQL supports Chinese search and sorting directly.

3. Use MySQL's locate function to judge. Such as:

SELECT * FROM table WHERE Locate (SUBSTR,STR) >0;

Locate () has two forms: locate (SUBSTR,STR), locate (Substr,str,pos). Returns the position of substr in Str if STR does not contain substr returns 0. This function is also case insensitive.

4. Use SQL statements like this:

SELECT * from TABLE WHERE fields like BINARY '%find% '

But this is case-sensitive as 1, and if you want to make a case-insensitive query, use upper or lower to convert.

5. Use binary and UCase functions and concat functions. UCase is English all convert uppercase, concat to concatenate strings. The new SQL statements are as follows:

Select Id,title,name from achech_com.news where binary UCase (title) like Concat ('% ', UCase (' a '), '% ')

Can also be written as

Select Id,title,name from achech_com.news where binary UCase (title) Like UCase ('%a% ')

The results of the search are satisfactory, but the speed may be slow for n milliseconds. Because matching with like and% can have a certain effect on efficiency.

MySQL like match query out incorrect Chinese solution