Mysql fuzzy search like implementation tutorial

In the evening, I want to write a message about member search. I will use mysql fuzzy search. Many friends may say that the full text search is complete, I think this is not friendly to Chinese mysql.

In the evening, I want to write a message about member search. I will use mysql fuzzy search. Many friends may say that the full text search is complete, I think this is not friendly to Chinese mysql.

In MySQL, some irrelevant fuzzy search results are often returned.
Record. For example, if "% a %" is found, Chinese characters may be returned, but no a character exists.
I have encountered similar problems before. After reading MySQL Manual in detail, I found that
There is a way to easily solve the problem and get satisfactory results.

Example:
· You want to search the news library using the title. The keywords may contain Chinese and English characters, such as the following SQL statement:
Select id, title, name from achech_com.news where title like '% a %'
In the returned results, some title fields are identified with the "a" keyword, while some are only Chinese, but are also returned in the search results.

Solution: Use the BINARY Attribute for retrieval, such:
Select id, title, name from achech_com.news where binary title like '% a %'
The returned results are correct, but the English letters are case-sensitive. Therefore, sometimes the results for searching such as "Achech" and "achech" are different.

Now that we know that using the BINARY Attribute can solve the preceding problem, let's look at what MySQL supports.
UCASE and CONCAT functions, in which UCASE converts all English letters into uppercase letters, while the CONCAT Function
Number is used to connect characters. The following is the SQL statement after we completely solve the problem:
Select id, title, name from achech_com.news where binary ucase (title) like concat ('%', ucase ('A'), '% ')
The search procedure is to first specify the attribute as BINARY for exact search results. The content of the like title may contain uppercase/lowercase letters, therefore, the ucase function is used to convert all the fields into uppercase letters and then perform the like operation. The like operation uses the fuzzy method, the advantage of using concat is that the input can be a direct keyword, without the "%" universal character, replace "'A'" with your variable directly, everything is easy in any language.

You can also write the following statement:
Select id, title, name from achech_com.news where binary ucase (title) like ucase ('% a % ')
Let's look at an implementation.

<? Php
Require_once ("Inc/Conn. php ");
Require_once ("Inc/function. php ");
$ Name = Get_value ('name', 0 );
 
If (empty ($ name) | strlen ($ name)> 20)
{
ShowMsg ('"result": "false "');
}
Else
{
$ SQL = "Select id, User_Name from aduse where binary ucase (User_Name) like ucase ('% $ name % ')";

$ Result = mysql_query ($ SQL) or die (mysql_error ());
If (mysql_num_rows ($ result ))
{
While ($ rs = mysql_fetch_array ($ result ))
{
$ Temp. = "<member memberid =" ". $ rs [0]." "name =" ". $ rs [1]." "url =" searchMember. php? Id = ". $ rs [0]." "/> n ";
}
Echo "<? Xml version = "1.0" encoding = "gb2312"?> N <channel> n ", $ temp," </channel> ";
}
Else
{
ShowMsg ('"result": "notfound "');
}
}
?>