Work Summary: mysql uses strings as query conditions, mysql query Conditions

Work Summary: mysql uses strings as query conditions, mysql query Conditions

Preface: I encountered a problem during mysql query today. The scenario is like this. Assume that one of my tables has a field scode (indicating the product number ), then there is a string that stores multiple product numbers. For example, "0001ME, 0002BL" is a string. If it is str, it is intended to use in, that is to say, we want to use str as the condition of in, as shown in the following where scode in "0001ME, 0002BL", but it fails. Then we can refer to the mysql documentation and find various functions, the instr function is found and the problem is solved. The result process is described below.

1. At the beginning, str is being converted to a similar in parameter, as shown below:

SELECT * FROM stocks_orderlist s where s.scode in ("0001ME","0002BL") LIMIT 10;SELECT CONVERT(CONCAT("\'",REPLACE("0001ME,0002BL","\,","\'\,\'"),"\'"),CHAR);SELECT * FROM stocks_orderlist s where s.scode in (CONVERT(CONCAT("\"",REPLACE("0001ME,0002BL","\,","\"\,\""),"\""),CHAR)) LIMIT 10;

Although str is converted into what in requires, but later I thought that the in parameter is a list set, and I converted str into the so-called in parameter form in time, but it must be a string, so I had been busy for a long time and wanted to give up a bit. But when I thought about it, I certainly did not find a proper solution, so I found the instr function.

2. instr function. See the help document to find out the first problem that substr encountered in str. Try the following:

SELECT INSTR("0001ME,0002BL","0003ME");SELECT INSTR("0001ME,0002BL","0001ME");SELECT INSTR("0001ME,0002BL","0002BL");SELECT * FROM stocks_orderlist s where INSTR("0001ME,0002BL",s.scode);

Summary: It looks like a very small method. If you do not know the method, it is very painful.