MySQL的exists

相较于 in，exists 其实没有特别高频的使用。exists 语句并不那么容易读。今天我们来使用一下:

mock data

CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `user_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `content` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

插入测试数据:

INSERT INTO `users`(id, name) VALUES(1, 'sai');
INSERT INTO `users`(id, name) VALUES(2, 'os');
INSERT INTO `users`(id, name) VALUES(3, 'jack');
INSERT INTO `users`(id, name) VALUES(4, 'kai');
INSERT INTO `users`(id, name) VALUES(5, 'tom');
INSERT INTO `users`(id, name) VALUES(6, 'peter');
INSERT INTO `users`(id, name) VALUES(7, 'li');
INSERT INTO `users`(id, name) VALUES(8, 'bob');
INSERT INTO `users`(id, name) VALUES(9, 'wuliuqi');
INSERT INTO `users`(id, name) VALUES(10, 'awesome');
INSERT INTO `user_logs`(user_id, content) VALUES(1, 'create post');
INSERT INTO `user_logs`(user_id, content) VALUES(1, 'delete post: 1');
INSERT INTO `user_logs`(user_id, content) VALUES(1, 'delete post: 55');
INSERT INTO `user_logs`(user_id, content) VALUES(2, 'delete post: 23');
INSERT INTO `user_logs`(user_id, content) VALUES(3, 'delete post:34');
INSERT INTO `user_logs`(user_id, content) VALUES(4, 'delete post:40');
INSERT INTO `user_logs`(user_id, content) VALUES(4, 'add post');
INSERT INTO `user_logs`(user_id, content) VALUES(4, 'delete post:99');
INSERT INTO `user_logs`(user_id, content) VALUES(4, 'delete post:140');

判断是否存在

我们使用 exists 判断某条记录是否存在:

mysql> select exists (select * from `users` where id = 1);
+---------------------------------------------+
| exists (select * from `users` where id = 1) |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select exists (select * from `users` where id = 13);
+----------------------------------------------+
| exists (select * from `users` where id = 13) |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (0.00 sec)

子查询

exists 更多用于子查询:

SELECT * FROM `user_logs` 
WHERE EXISTS (
    SELECT * FROM `users` WHERE `users`.id = `user_logs`.user_id and `users`.id = 1
);

你知道会输出什么吗?

当我第一次看到这个语句时是这么理解的, where 后面的是1，所以会列出所有的 user_logs 。

显然我的理解是:

SELECT * FROM `user_logs` 
WHERE 1;

但输出打脸了 😢

+----+---------+-----------------+---------------------+
| id | user_id | content         | created_at          |
+----+---------+-----------------+---------------------+
|  1 |       1 | create post     | 2023-02-14 08:54:59 |
|  2 |       1 | delete post: 1  | 2023-02-14 08:54:59 |
|  3 |       1 | delete post: 55 | 2023-02-14 08:54:59 |
+----+---------+-----------------+---------------------+
3 rows in set (0.00 sec)

其实上面的语句与下面的语句结果一致的。

SELECT * FROM `user_logs` 
WHERE user_id in (
    SELECT id FROM `users` WHERE `users`.id = 1
);

SELECT `user_logs`.* FROM `user_logs` 
LEFT JOIN `users` ON `users`.id = `user_logs`.user_id WHERE `users`.id = 1;

in 还是 exists

对于很多博客中提到的 in 适合于外表大而子查询表小的情况，exists 适合于外表小而子查询表大的情况，其实并不准确。MySQL 手册中提到，如果没有使用 materialization ，优化器又是会重写查询，即可能会将 in 转成 exists。那么这种情况性能其实是没什么差别的。但是给大表加索引一般是有效的。

参考

• Optimizing Subqueries with Materialization