Mysql regular gets the intersection of the field "Go"

Problem description

For example, there are two records in Table1.
Name No
A 2,9
b 8,10

Then there is a string of strings that is 0,1,2,3,4

Then, using a SQL, find out the record of No for 2,9.

Because there are 2 in the string, there are 2 in the data.

Detailed explanation
------------------------------
The table field is
Name No
A 2,9
b 8,10
String is str= "0,1,2,3,4"
The next step is to check the No field with a record of the intersection in Str.
The result of the query is Name=a, no=2,9
------------------------------

The answer is:

SQL code

1. SELECT * from table1 where concat (', ',No,', ') regexp concat (', 0,|,1,|,2,|,3,|,4, ');

Or:

SQL code

1. SELECT * from table1 where concat (', ',No,', ') regexp concat (', (',replace ('  0,1,2,3,4 ',', ', ' | '), ');

Here is the extended learning:

For some reason, sometimes we don't put some attributes into the same string field as the design guidelines for the paradigm. Like personal interests, sometimes we design tables for
CREATE TABLE members (UID int primary key,uname varchar), hobby varchar (100));


The contents of the table are as follows

Mysql> SELECT * from members;
+-----+-------+---------------------------------+
| UID | uname | Hobby |
+-----+-------+---------------------------------+
| 1 | AAAA | Music, movie, Internet, basketball, reading, table tennis |
| 2 | BBBB | Music, reading, table tennis, Daze, Weiqi, meditation |
| 3 | CCCC | Friends, Table Tennis |
| 4 | DDDD | Billiards, Internet, reading, travel |
| 5 | eeee | Music, Daze, next go, meditation |
+-----+-------+---------------------------------+
4 rows in Set (0.00 sec)

What if we want to find a member record that has the same hobby as a user X (reading, dating, go, football, skiing)?

In other databases, we can decompose this "read, make friends, go, football, ski" only through programs or stored procedures. The string is a separate hobby item, then one for the like '%xxxx% ' to query. But in MySQL we can directly use this regexp formal expression to construct SQL statements to implement.

First we turn ' reading, dating, go, football, skiing ' into a regular form of ' reading | friends | weiqi | football | skiing ' | The meaning of ' or ' in a regular expression

mysql> Select Replace (' reading, dating, Weiqi, football, skiing ', ', ', ' | ');
+---------------------------------------------+
| Replace (' reading, dating, Weiqi, football, skiing ', ', ', ' | ') |
+---------------------------------------------+
| Read | make friends | weiqi | football | skiing |
+---------------------------------------------+
1 row in Set (0.00 sec)

So we can use the SQL statement as follows.
Mysql> SELECT * from the members where Hobby regexp replace (' reading, dating, Weiqi, football, skiing ', ', ', ' | ');
+-----+-------+---------------------------------+
| UID | uname | Hobby |
+-----+-------+---------------------------------+
| 1 | AAAA | Music, movie, Internet, basketball, reading, table tennis |
| 2 | BBBB | Music, reading, table tennis, Daze, Weiqi, meditation |
| 3 | CCCC | Friends, Table Tennis |
| 5 | eeee | Music, Daze, next go, meditation |
+-----+-------+---------------------------------+
3 Rows in Set (0.00 sec)


As above statement we can get all the records of all hobby including ' reading, dating, go, football, skiing ' by a sentence of SQL.

But there is a small flaw in the above statement, that is, the ' Go ' is also selected, if the exact match, this record should not be selected. To avoid this, we make the following improvements to the SQL statement.


Change the regular style to ', (read | make friends | go | soccer | ski), ' that is, require a qualifier before and after the match, '

Mysql> Select Concat (', ('), replace (' reading, dating, go, soccer, skiing ', ', ', ' | '), ');
+---------------------------------------------------------------+
| Concat (', ('), replace (' reading, dating, Weiqi, soccer, skiing ', ', ', ' | '), '), ') |
+---------------------------------------------------------------+
| , (Reading | friends | weiqi | soccer | skiing), |
+---------------------------------------------------------------+
1 row in Set (0.00 sec)

Mysql> SELECT * FROM
--where concat (', ', hobby, ', ') regexp
-Concat (', ('), replace (' reading, dating, Weiqi, soccer, skiing ', ', ', ' | '), ');
+-----+-------+---------------------------------+
| UID | uname | Hobby |
+-----+-------+---------------------------------+
| 1 | AAAA | Music, movie, Internet, basketball, reading, table tennis |
| 2 | BBBB | Music, reading, table tennis, Daze, Weiqi, meditation |
| 3 | CCCC | Friends, Table Tennis |
+-----+-------+---------------------------------+
3 Rows in Set (0.00 sec)

This avoids the 5th record being selected.

Of course you can also use this regular style ', reading, |, dating, |, Weiqi, |, football, |, skiing, ' but obviously not as efficient ', (read | friends | go | soccer | skiing), ' this is it.

Mysql is getting the intersection of the fields "go"