How to solve the problem of MySQL fuzzy search _ MySQL-mysql tutorial

Source: Internet
Author: User
Tags mysql text mysql tutorial
The solution to the MySQL text fuzzy search problem is in MySQL. during Chinese fuzzy search, some irrelevant records are often returned, such as searching for "-0x1. when ebea4bfbffaacp-4 % ", 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:

Select id, title, name from achech_com.news where title like ''-0x1. results returned by ebea4bfbebe84p-4 % '', 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 ''-0x1. the results returned by the ebea4bfbebe84p-4 % ''are more accurate than before, but the English letters are case sensitive, so sometimes the results for retrieval 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 the UCASE and CONCAT functions supported by MySQL. UCASE converts all English words into uppercase letters, the function CONCAT is used to connect characters. The following is the SQL statement after we have completely solved 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 (''0x0. 00020bfbe08p-1022% '') the search results are satisfactory, but the speed may be slow for N milliseconds.

Related information:

Relate:
20.16 Case Sensitivity in Searches
By default, MySQL searches are case-insensitive (although there are some character sets that are never case insensitive, such as czech ). that means that if you search with col_name LIKE 'a % '', you will get all column values that start with a or. if you want to make this search case-sensitive, use something like INDEX (col_name, "A") = 0 to check a prefix. or use STRCMP (col_name, "A") = 0 if the column value must be exactly "".

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.