The query results of the MySQL query in operation are displayed in the in set order.

MySQL queries the in operation. The query results are displayed in the in set order.
Copy codeThe Code is as follows:
Select * from test where id in (3, 1, 5) order by find_in_set (id, '3, 1, 5 ');
Select * from test where id in (3, 1, 5) order by substring_index ('3, 1, 2 ', id, 1 );


Occasionally seen... Some people may have noticed it, but I did not know it before.
SQL: select * from table where id IN (3, 6, 9, 1, 2, 5, 8, 7 );

After such a situation is obtained, the IDs are actually sorted by 1, 2, 3, 4, 5, 6, 7, 8, 9, but what if we really want to sort IN order? Can SQL be completed? Do you need to get it back before foreach? In fact, mysql has this method.

SQL: select * from table where id IN (,) order by field (id );

The order is the specified order .... This is something that I have never used before and can be seen occasionally, So I recorded it. One is to take a note, and the other is to show it to more people.

Processing NULL values by the not in statement IN MySQL

Mysql> select count (name) from cve where name not in ('cve-2017-0001 ', 'cve-2017-0002 ');
+ ------------- +
| Count (name) |
+ ------------- +
| 1, 17629 |
+ ------------- +
1 row in set (0.02 sec)
Mysql> select count (name) from cve where name not in ('cve-2017-0001 ', 'cve-2017-0002', NULL );
+ ------------- +
| Count (name) |
+ ------------- +
| 0 |
+ ------------- +
1 row in set (0.01 sec)
When the subquery returns NULL, the result must be 0. Check the manual. Therefore, the following query is actually used:
Select count (DISTINCT name)
FROM CVE
WHERE name not in (SELECT cveID FROM cve_sig WHERE cveID is not null)
By the way, it is easy to use regular expression matching in MySQL:
Select count (alarmID)
FROM Alarm
WHERE (cve not rlike '^ CVE-[0-9] {4}-[0-9] {4} $' or cve is null)
Of course, RLIKE can also write REGEXP. I personally prefer to use RLIKE, because the spelling is close to LIKE and can be seen in terms of name and meaning.

Mysql-not in
Table: info primary key (id, info_type_id)
Id, info_type_id, programme_id, episode_id
3, 4,382,100 034
3, 8,382,100 034
4, 8,382,100 034
6, 8,382,100 034
7, 8,382,100 034
8, 8,382,100 034
9, 8,382,100 034
10, 8,382,100 034
11, 8,382,100 034
12, 8,382,100 034
13, 8,382,100 034
100001, 4,382,100 034
100002, 4,382,100 034

Exclude (id = 3 & info_type_id = 8) and (id = 4 & info_type_id = 8) to find other
Error: select * from info where episode_id = 100034 and id not in (3, 4) and info_type_id not in (8 );
Error result:
Id, info_type_id, programme_id, episode_id
100001, 4,382,100 034
100002, 4,382,100 034
Correct: select * from info where episode_id = 100034 and (id <> 3 or info_type_id <> 8) and (id <> 4 or info_type_id <> 8 );
Correct result:
Id, info_type_id, programme_id, episode_id
3, 4,382,100 034
6, 8,382,100 034
7, 8,382,100 034
8, 8,382,100 034
9, 8,382,100 034
10, 8,382,100 034
11, 8,382,100 034
12, 8,382,100 034
13, 8,382,100 034
100001, 4,382,100 034
100002, 4,382,100 034
Understanding: id <> 3 or info_type_id <> 8 excludes id = 3 & info_type_id = 8. When there is more than one primary node in the table, you cannot simply use key1 not in (......) AND key2 not in (......) ..