Two solutions to the problem of MySQL in conditional statement only reading one piece of information

Today, my colleague encountered a very strange problem when writing MYSQL query statements. When using MySQL multi-table query, a field in one table is used as the in query condition of another table, only one piece of information can be read, but it can be read normally if a number is used directly.

The SQL statement is as follows:

select a.id,a.title,b.idlist,b.aid from table a,table2 b where a.id in(b.idlist) and b.aid=2

The idlist field in table2 is of varchar type and stores multiple id information in table, i.e. 1, 1,2,3,4

The above statement can be used to query normally, but only one piece of information can be queried. However, the following statement can be used to read normally.

select a.id,a.title,b.idlist,b.aid from table a,table2 b where a.id in(1,2,3,4) and b.aid=2

This is a very strange problem. I thought it was a data type problem at first, but to save data in the format of 1,2,3,4 , I can only use character type. I tried many ways but couldn't solve the problem until I found the FIND_IN_SET() function in MYSQL. The following is a basic introduction to the FIND_IN_SET() function.

Syntax: FIND_IN_SET(str,strlist)

definition:

1. If the string str is in the string list strlist consisting of N subchains, the return value ranges from 1 to N.

2. A string list is a string consisting of a number of self-links separated by ',' symbols.

3. If the first argument is a constant string and the second is a typeSET column, the FIND_IN_SET() function is optimized to use bitwise calculations.

4. If str is not in strlist or strlist is an empty string, the return value is 0.

5. If any parameter is NULL, the return value is NULL. This function will not work properly if the first argument contains a comma (',').

strlist: A string linked by English commas "," for example: "a,b,c,d". The string is similar to the SET type value linked by commas.

Example: SELECT FIND_IN_SET('b','a,b,c,d'); //The return value is 2, which is the second value

OK, let's try it first and modify the original SQL statement to

select a.id,a.title,b.idlist,b.aid from table a,table2 b where FIND_IN_SET(a.id,b.idlist) and b.aid=2

Execute the modified statement, and it can finally be read normally. After analyzing the reason, it is ultimately due to the data type problem. When we directly in(b.idlist), the b.idlist read is a character type, and in only accepts numbers. Although both have "," they are actually completely different.

Well, the problem is solved. If you want to know more about how to use the FIND_IN_SET() function, you can read the related articles on the page.