In MySQL, the statement locate position instr find_in_set and locatefind_in_set are more efficient than the like statement.

In MySQL, the statement locate position instr find_in_set and locatefind_in_set are more efficient than the like statement.
Are you always looking for more efficient methods than the LIKE Statement of MySQL? I will introduce you to several methods below.

LIKE statement
SELECT 'column' FROM 'table' where 'condition' like '% keyword %'

In fact, you can use the locate (position) and instr functions instead.

LOCATE statement
SELECT 'column' from 'table' where locate ('keyword', 'condition ')> 0

Or locate's position
POSITION Statement
SELECT 'column' from 'table' where position ('keyword' IN 'condition ')

Or
INSTR statement
SELECT 'column' from 'table' where instr ('condition ', 'keyword')> 0

The difference between locate, position, and instr is not only the location of the parameter, but also the same as the parameter of the starting position.
Mysql> select locate ('bar', 'foobarbarbar ', 5 );

-> 7

In terms of speed, these three are a little faster than using like.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Gorgeous split line ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
We also want to introduce a new member, that is, find_in_set.

Find_in_set (str1, str2) function: returns the index of the position of str1 in str2, where str2 must be separated. Table: mysql> select * from region; + ---- + ------------- + | id | name      | + ---- + ------------- + | 1| Name1, nam2| 2| Name1     | 3| Name3| 4| Name2, name4 | 5| Name3, name5 | + ---- + ------------- + 5 rows in set (0.00 sec) FIND_IN_SET statement mysql> select * from test where find_in_set ('name1', name ); + ---- + ------------ + | id | name| + ---- + ------------ + | 1| Name1, nam2 | 2| Name1 | + ---- + ------------ + 2 rows in set (0.02 sec) Thank you for following the websites blog.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.