If the MySQL string field contains a string, use the Find_in_set

There is a requirement that, in the MySQL Database string field (permission), a range of values representing different permissions between 1 and N, separated by ', ', is now removed from the list of all members with a certain permission.

To create a table:

1	CREATE TABLE users(id int(6) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(20) NOT NULL,limits VARCHAR(50) NOT NULL);

Add Data:

1	INSERT INTO users(name, limits) VALUES(‘小张‘,‘1,2,12‘);

2	INSERT INTO users(name, limits) VALUES(‘小王‘,‘11,22,32‘);

Some of the fields in Mysql are string types, how do you find records that contain some characters?

Method One:

1	mysql> SELECT * FROM users WHERE limits like "%2%";

2	+----+----+--------+

3	| id |name| limits |

4	+----+----+--------+

5	|  1 |小张| 1,2,12 |

6	+----+----+--------+

7	|  2 |小王|11,22,32|

8	+----+----+--------+

9	2 row inset

So that the second data does not have permission ' 2 ' user also found out, does not meet the expectations. So we looked at the manual, using the MySQL string function Find_in_set ().

Method Two:

1	mysql> SELECT * FROM users WHERE find_in_set(‘2‘, limits);

2	+----+----+--------+

3	| id |name| limits |

4	+----+----+--------+

5	|  1 |小张| 1,2,12 |

6	+----+----+--------+

7	1 row inset

This will be able to achieve our expected effect, the problem is solved!

Note : The MySQL string function find_in_set (STR1,STR2) function is the index of the location where STR1 is returned in str2, and the str2 must be separated by ",".

If the MySQL string field contains a string, use the Find_in_set