MySQL插入不存在的地方不工作

Question

我试图做一个MySQL插入查询只有当它不存在。这就是我尝试的方式，但它不起作用。我做错了什么?

INSERT INTO user_logs (log_date, log_ip, name) VALUES ('2021-09-13 00:00:00', '192.168.0.1', 'Bob') 
WHERE NOT EXISTS (SELECT * FROM user_logs WHERE log_ip='192.168.0.1' AND name='Bob') 

Answer 1

如果log_ip和name是唯一的，你可以使用ON DUPLICATE UPDATE

如果name和log_ip相同，将更新列log_date

INSERT INTO user_logs (log_date, log_ip, name) VALUES ('2021-09-13 00:00:00', '192.168.0.1', 'Bob') 
ON DUPLICATE KEY UPDATE
log_date = VALUES(log_date),log_ip = VALUES(log_ip), name = VAUES(name) ;

Answer 2

不能在INSERT语句中使用WHERE子句。但是，您可以在INSERT中使用SELECT语句，这将允许您使用WHERE条件。

INSERT INTO user_logs (log_date, log_ip, name) 
SELECT '2021-09-13 00:00:00', '192.168.0.1', 'Bob'
WHERE NOT EXISTS (SELECT * FROM user_logs WHERE log_ip = '192.168.0.1' AND name = 'Bob');

---

编辑:我包含了下面的测试语句和控制台输出来演示这个解决方案。

mysql> CREATE TABLE user_logs (
->     log_date TIMESTAMP,
->     log_ip VARCHAR(200),
->     name VARCHAR(200)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO user_logs (log_date, log_ip, name) VALUES
-> ('2021-09-11 00:00:00', '192.168.0.1', 'Bob'),
-> ('2021-09-12 00:00:00', '192.168.0.2', 'Mary'),
-> ('2021-09-13 00:00:00', '192.168.0.3', 'Tom');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> INSERT INTO user_logs (log_date, log_ip, name) 
-> SELECT '2021-09-14 00:00:00', '192.168.0.4', 'John'
-> WHERE NOT EXISTS (SELECT * FROM user_logs WHERE log_ip = '192.168.0.4' AND name = 'John');
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> INSERT INTO user_logs (log_date, log_ip, name) 
-> SELECT '2021-09-13 00:00:00', '192.168.0.3', 'Tom'
-> WHERE NOT EXISTS (SELECT * FROM user_logs WHERE log_ip = '192.168.0.3' AND name = 'Tom');
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0