Two Perfect Solutions to the Problem of mysql in Conditional Statement Reading Only One Message

Today, colleagues encountered a very strange problem when writing MYSQL query statement. Using mysql multi-table query, a field in one table is used as the in query condition of another table, and only one piece of information can be read, but it can be read normally if it is directly used with numbers

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 the table2 table is of type varchar, and stores multiple id information in the table table, namely 1,2,3,4

With the above statement, you can query normally, but you can only query 1 piece of information, but with the following statement, you can read it 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

Very strange problem, at first thought it was a data type problem, but it should be saved as 1,2,3,4 The data in this format can only be of character type, and many attempts have not solved the problem until the FIND_IN_SET () function in MYSQL is found. 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 made up of N subchains, the return value ranges from 1 to N.

2. A string list is a string made up of a set of self-chains separated by the ',' symbol.

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

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

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

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

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

Ok, try to use 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

After executing the modified statement for 1 time, it can finally be read normally. After analyzing the reason for 1 time, it is finally due to the data type problem. When we directly in (b. idlist), the read b. idlist is a character type, while in only accepts numbers. Although they all have "," signs, they are actually completely different.

Ok, the problem is solved. If you want to know more about the use of FIND_IN_SET () function, you can see the related articles on the next page of 1