Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to solve the problem that mysql in conditional statements read only one piece of information". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn this article "how to solve the problem of reading only one piece of information in mysql in conditional statements".

There is a strange problem when writing MYSQL query statements. Using mysql multi-table query, a field in one table can be 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 you use a number directly.

The SQL statement is as follows:

Select a.idb.idlist a title and b.aid=2 b.idlist where a.id in b.aid from table a journal table 2b (title) and b.aid=2

The idlist field in the table2 table is of varchar type, and multiple id information in the table table is saved, that is, 1mem2pence3mem4.

You can query normally with the above statement, but only one piece of information can be queried, but it can be read normally with the following statement.

Select a.idand b.aid=2 a.title and b.aid=2 b.idlistreb .aid from table a dagger table 2b where a.id in

A very strange problem, at first I thought it was a problem of data type, but in order to save the data in this format, I can only use character types. I thought of a lot of ways but did not solve the problem until I found the FIND_IN_SET () function in MYSQL. Here 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.

two。 A list of strings is a string made up of self-chains 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 be calculated in bits.

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 the English comma "," for example: "a SET bpcec _ r d", a string that is formally similar to the value of the comma type is linked.

Example: SELECT FIND_IN_SET ('brecrature, pencycline, pd`); / / return value is 2, that is, the second value

All right, let's see if it works. Change the original SQL statement to

Select a.idwhere FIND_IN_SET a.titlere.idlistreb.aid from table a makeshift table2b where FIND_IN_SET (a.idmatheb.idlist) and b.aid=2

After executing the modified statement, we can finally read it normally and analyze the reason. In the end, it is because of the data type. When we in directly (b.idlist), the b.idlist read is a character type, while in only accepts numbers. Although they all have "," signs, they are actually completely different.

The above is all the contents of this article entitled "how to solve the problem of reading only one piece of information in mysql in conditional statements". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.