有时候,需要往数据库里插入数据,这些数据,有些已经在MySQL中。但这种导入的工作,只是临时性的,又想专门写一个脚本去判断是否存在数据库,不存在就插入。这里提供一个骚操作,可以直接用MySQL查看哪些不在数据库的,然后再插入。
CREATE TABLE IF NOT EXISTS user (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)
);
INSERT INTO user (name) VALUES ('zhangsan'), ('wangwu'), ('zhaoliu');
SELECT * FROM user;
mysql> select * from user;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | wangwu |
| 3 | zhaoliu |
+----+----------+
3 rows in set (0.00 sec)
你可以使用以下查询语句来判断 "zhangsan" 和 "lisi" 是否在数据库中.如果 "zhangsan" 和 "lisi" 存在于数据库中,那么可以查询将返回它们的名字。
SELECT name FROM user WHERE name IN ('zhangsan', 'lisi');
mysql> SELECT name FROM user WHERE name IN ('zhangsan', 'lisi');
+----------+
| name |
+----------+
| zhangsan |
+----------+
1 row in set (0.00 sec)
如果你想知道哪个名字不在数据库中,可以稍作修改,使用 NOT IN
来找出不在数据库中的名字
SELECT 'zhangsan' AS name WHERE 'zhangsan' NOT IN (SELECT name FROM user)
UNION ALL
SELECT 'lisi' AS name WHERE 'lisi' NOT IN (SELECT name FROM user);
mysql> SELECT 'zhangsan' AS name WHERE 'zhangsan' NOT IN (SELECT name FROM user)
-> UNION ALL
-> SELECT 'lisi' AS name WHERE 'lisi' NOT IN (SELECT name FROM user);
+------+
| name |
+------+
| lisi |
+------+
1 row in set (0.00 sec)