Shulou(Shulou.com)06/01 Report--

In this article Xiaobian for you to introduce in detail "mysql how to use instr to achieve the effect of in", the content is detailed, the steps are clear, and the details are handled properly. I hope this article "mysql how to use instr to achieve the effect of in" can help you solve your doubts.

You encounter a situation when using mysql:

You need to find out the record of id in an id spliced string, and use id in (id1,id2,...) in this case. It doesn't work, because the parameters in in syntax are numeric, so you should use instr syntax, instr (idsStr,id), where idsStr='id1,id2,id3.'

The following examples are given:

1 create table create table t_city (id int (10), name varchar (20))

Insert into t_city values (1) Beijing), (2) Shanghai), (3) Guangzhou), (4) Shenzhen), (5) Hangzhou), (12) Wuhan)

2 query

Now I need to query the city of id in the string '1jue 2je 3', but it is not possible to use in syntax, because the parameter of in syntax is not a string, you can achieve the same effect in the following way

Select * from t_city where instr ('1, 2, 1, 2, 3, 3)

The result is correct. But if I want to query the cities of id in the string '3dy4, 5, 12', I will find that there is a deviation in the results, and 6 cities have been found.

Select * from t_city where instr ('3, 4, 5, 5, 12, 4, 13, 5, 12, 4, 4, 5, 12, 4, 10, 12, 6, 4, 4, 4, 4, 4, 5, 12, 12, 10, and 12 months).

It turns out that because the id of Wuhan is 12, the id of Beijing is 1, and the id of Shanghai is 2, according to the semantics of instr, id=1 or 2 or 12 can be found, so modify the search method

Select * from t_city where instr (', 3, 4, 5, 12, id,','))

After reading this, the article "how mysql uses instr to achieve the effect of in" has been introduced. If you want to master the knowledge of this article, you still need to practice and use it yourself to understand it. If you want to know more about related articles, 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.