Shulou(Shulou.com)06/01 Report--

Today, the editor shares with you how MySQL determines whether a field contains a certain string. I believe many people don't know much about it. In order to make you understand better, I summarized the following contents for you. Let's look down together. I'm sure you'll get something.

The method of determining whether a field contains a string by MySQL

Method 1: like

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

Method 2: find_in_set ()

Using mysql string function find_in_set ()

SELECT * FROM users WHERE find_in_set ('character', field name)

This is OK, how to understand it?

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

Note: when str2 is NO1: "3Magne6Magne13jor24jor33Magne36", NO2: "13recedence 33Power36page39", judge whether the str2 field in the two data contains' 3seconds, this function can solve perfectly.

Mysql > SELECT find_in_set () (as test;- > 1mysql > SELECT find_in_set ()) as test;- > 1mysql > SELECT find_in_set ()

Method 3: locate (character, field name)

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

Its alias is position in.

Select * from table name where locate (character, field) select * from table name where position (character in field)

Example: determine whether the url in the site table contains a 'http://' substring, and if not, concatenate at the beginning of the url string

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

Note that string concatenation in mysql cannot use the plus sign +, instead, use the concat function

Method 4: INSTR (field, character)

Select * from table name where INSTR (field, character)

In addition, the author looks at the execution plan of the above SQL (excluding find_in_set) and finds that all of them are:

It is said on the Internet that the speed of fuzzy query is fast with locate. I don't know where the conclusion comes from. It may be in the case of a large amount of data.

This is how MySQL determines whether a field contains the details of a string, and does it yield anything after reading it? If you want to know more about it, welcome to the industry information!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.