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

What is the meaning of mysql in, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Mysql in is often used in where expressions to query data within a range, using syntax such as "select * from where field in (value1,value2,value3, …)" .

The operating environment of this paper: Windows7 system, mysql5.7, Dell G3.

What does mysql in mean?

Detailed explanation of the usage of in in mysql

I. basic usage

In in mysql is often used in where expressions to query data within a range.

Select * from where field in (value1,value2,value3, …)

When IN is preceded by the NOT operator, it means the opposite of IN, that is, do not select in these list items

Select * from where field not in (value1,value2,value3, …)

2. IN subquery

More often, the value of the IN list item is ambiguous and may be obtained through a subquery:

SELECT * FROM article WHERE uid IN (SELECT uid FROM user WHERE status=0)

In this SQL example, we have implemented all articles that find out all users with a status of 0 (possibly prohibited). First, get all the users of status=0 through one query:

SELECT uid FROM user WHERE status=0

Then use the query result as a list item of IN to achieve the final query result, note that the result returned in the subquery must be a field list item.

The problem of low query efficiency is often encountered in the subquery of in. The solution is as follows:

1. Still use in subquery and query one more time

SELECT * FROM basic_zdjbxx WHERE suiji IN (SELECT zdcode FROM (SELECT zdcode FROM basic_h WHERE zdcode! = ") AS h)

2. Use LEFT JOIN

SELECT zd.* FROM (SELECT DISTINCT zdcode FROM basic_h WHERE zdcode! = "") AS h LEFT JOIN basic_zdjbxx zd ON zd.suiji = h.zdcode

III. Supplementary explanation of IN operator

IN list items support not only numbers, but also characters and even time and date types, and you can mix these different types of data items without having to be consistent with the type of column:

SELECT * FROM user WHERE uid IN (1, 2, 1, 2, 3, 4, 5, 5, 5, 3, 4, 3, 4, 4, 3, 4, 3, 4, 3, 4, 4, 4, 3, 4, 4, 4, 3, 4, 4, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 4, 6, 6, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12,

An IN can only compare the range of one field, and if you want to specify more fields, you can use the AND or OR logical operator:

SELECT * FROM user WHERE uid IN (1, 2) OR username IN ('admin','manong')

After using the AND or OR logical operators, IN can also be used with other operators such as LIKE, > =, =, and so on.

IV. On the efficiency of IN operators

If the list item for IN is determined, you can use multiple OR instead:

SELECT * FROM user WHERE uid IN (2, 3, 5)

/ / equivalent to:

SELECT * FROM user WHERE (uid=2 OR aid=3 OR aid=5)

It is generally believed that:

1. If you are operating on an index field, using OR is more efficient than IN, but when the list item is uncertain (if you need a subquery to get a result), you must use the IN operator. In addition, the IN operator is also applicable when the data of the subquery table is smaller than that of the main query.

The more fields connected by 2.in or or without adding an index (1 or 2 or 3 or 4 or …) Or is much less efficient than in

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.