Talk about mysql's multi-column combination query

sequence

This article mainly shows how to use mysql's multi-column combination query

What is a multi-column combined query, that is, the value of the query is no longer the value of a single column, but the value of the combined column. For example where (column1,column2) in ((a1,b1),(a2,b2),(a3,b3))

Example

build table

 create table t_demo(
   id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name varchar(10),
   score int
);

insert into t_demo(name,score) values('a',10);
insert into t_demo(name,score) values('b',20);
insert into t_demo(name,score) values('c',30);
insert into t_demo(name,score) values('d',40);
insert into t_demo(name,score) values('d',50);
insert into t_demo(name,score) values('e',60);

Multi-column in query

 select * from t_demo where (name,score) in (('c',30),('e',60));
+----+------+-------+
| id | name | score |
+----+------+-------+
| 3  | c    | 30    |
| 6  | e    | 60    |
+----+------+-------+
2 rows in set
Time: 0.112s

multiple columns = query

 select * from t_demo where (name,score) = ('c',30) or (name,score) = ('e',60);
+----+------+-------+
| id | name | score |
+----+------+-------+
| 3  | c    | 30    |
| 6  | e    | 60    |
+----+------+-------+
2 rows in set
Time: 0.119s

summary

Multi-column combination query is usually rare, and it is quite magical at first glance.

doc

• mysql-filtering-by-multiple-columns
• selecting-where-two-columns-are-in-a-set