Solve the Problem of MySQL database Chinese fuzzy search

Source: Internet
Author: User
Tags mysql manual
In MySQL, some irrelevant records are often returned during fuzzy search of Chinese characters. For example, when % a % is found, the returned records may contain Chinese characters, but no a character exists. I have encountered similar problems before. After reading MySQL Manual in detail, I found that there is a convenient solution and satisfactory results. Example: You want to use the title

In MySQL, some irrelevant records are often returned during fuzzy search of Chinese characters. For example, when % a % is found, the returned records may contain Chinese characters, but no a character exists. I have encountered similar problems before. After reading MySQL Manual in detail, I found that there is a convenient solution and satisfactory results. Example: You want to use the title

In MySQL, some irrelevant records are often returned during fuzzy search of Chinese characters. For example, when "% 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 convenient solution and satisfactory results.


Example:


You want to search the news library using the "title". The keywords may contain Chinese and English. The following SQL statement:


QUOTE:


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:


QUOTE:


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 "Achech" and "achech" are different. Once you know that you can use the BINARY Attribute to solve the preceding problem, let's look at the UCASE and CONCAT functions supported by MySQL. UCASE converts all English words into uppercase letters, the function CONCAT is used to concatenate characters. The following is the SQL statement after we completely solve the problem:


QUOTE:


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:


QUOTE:


Select id, title, name from achech_com.news where binary ucase (title) like ucase ('% a % ')


The search results are satisfactory, but the speed may be slow for N milliseconds.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.