MySQL method to determine whether a field contains a string

MySQL method to determine whether a field contains a string

Method 1: like

SELECT * FROM table name WHERE field name like "% character %";

Method 2: find_in_set()

Use mysql string Function find_in_set();

SELECT * FROM users WHERE find_in_set('字符', 字段名);

This is OK, how do you understand it?

Mysql has many string functions. The find_in_set(str1,str2) function returns the position index of str1 in str2. str2 must be separated by ",".

Note: When str2 is NO1: "3,6,13,24,33,36", NO2: "13,33,36,39", determine whether the str2 field in the two data contains ' 3', this function can perfectly solve

mysql > SELECT find_in_set()('3','3,6,13,24,33,36') as test;
-> 1
mysql > SELECT find_in_set()('3','13,33,36,39') as test;
-> 0

Method three: locate(character, field name)

Use locate(character, field name ) function, if included, returns a number of >0, otherwise returns 0,

Its alias is position in

select * from 表名 where locate(字符,字段)
select * from 表名 where position(字符 in 字段);

Example: Determine whether the url in the site table contains 'http:/ /' substring, if it is not included, it will be spliced ​​at the beginning of the url string

update site set url =concat('http://',url) where locate('http://',url)=0

Note that the plus sign cannot be used to splice strings in mysql, use the concat function

Method 4: INSTR (field, characters)

select * from table name where INSTR (field, characters)

In addition, the author checked the execution plan of the above SQL (not Including find_in_set), I found that they are all:

It is said on the Internet that using locate for fuzzy query is fast. I don’t know how the conclusion came about. It may be due to the large amount of data.

(Free learning video tutorial recommendation: mysql video tutorial)

The above is the detailed content of MySQL method to determine whether a field contains a string. For more information, please follow other related articles on the PHP Chinese website!