How to query the contained string in mysql

Method: 1. Use "table name where field like '% string%'" to query; 2. Use "table name where find_in_set (string, field)" to query; 3. Use "table name where field like '% string%'" to query locate(string,field)" query.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to query the contained string in mysql

Summarize the 3 methods to determine whether a certain string contains a certain string in MySQL.

Let’s take a simple scenario first, such as querying the user table for records with yanggb in the hobby.

Method 1: Use wildcard character %.

Wildcard is also a fuzzy matching, which can be divided into leading fuzzy query, trailing fuzzy query and total leading matching query. It is suitable for the scenario of querying whether a certain string contains another fuzzy query.

select * from user where hobby like '%yanggb%';

The usage scenario is limited to finding records that exist in yanggb in hobby (hobby is multiple values ​​separated by commas), but not vice versa.

Method 2: Use the string function find_in_set() provided by MySQL.

MySQL provides a string function find_in_set(str1,str2) function, which is used to return the position index of str1 in str2. If it is found, it returns true (1), otherwise it returns false ( 0), where str2 must be separated by half-width commas [,].

select * from user where find_in_set('yanggb', hobby);

When the matching string is used as the first parameter, the applicable scenario is to find the records of yanggb in hobby (hobby is multiple values ​​separated by commas).

select * from user where find_in_set(hobby, 'yanggb1,yanggb2,yanggb3');

When the matched string is used as the second parameter, the applicable scenario is to find records with one of yanggb1, yanggb2 and yanggb3 in hobby (hobby is a single value).

Method 3: Use the string function locate() function provided by MySQL.

MySQL also provides a string function locate(substr, str) function, which is used to return the position index of substr in str. If it is found, it returns a number greater than 0, otherwise it returns 0 .

select * from user where locate('yanggb', hobby) > 0;

The applicable scenarios are similar to the find_in_set() function. The only difference between the two functions is the return value.

Recommended learning: mysql video tutorial

The above is the detailed content of How to query the contained string in mysql. For more information, please follow other related articles on the PHP Chinese website!