只有在不存在的情况下插入MYSQL
问题描述:
我目前使用SELECT而不是VALUES的方式插入数据,因为它是默认的。问题是,我找不到只有在数据库中才插入数据的方法。只有在不存在的情况下插入MYSQL
这是我当前的查询:
INSERT INTO collectives_users(id_user,id_artistname,id_collective,users_type,status)
SELECT
(SELECT id_user FROM artistnames WHERE artistname = 'yoannis'),
(SELECT id FROM artistnames WHERE artistname = 'yoannis'),
('1'),(2),(0)
UNION ALL
SELECT
(SELECT id_user FROM artistnames WHERE artistname = 'paul'),
(SELECT id FROM artistnames WHERE artistname = 'paul'),
('1'),(4),(0)
我
INSERT IGNORE INTO
不工作时,并没有真正合适的(不返回错误)
试过我也试图与
AND NOT EXIST
不起作用。
如果你有一个想法不要犹豫。
答
您是否试过
REPLACE
语法?它的工作原理与INSERT相同,只使用
REPLACE
而不是
INSERT
。
如何在collectives_users表中设置一个虚拟列,所以我们可以例如计算发现重复的次数。通过这种方式,我们可以使用INSERT ... ON DUPLICATE KEY UPDATE
语法:
INSERT INTO collectives_users(id_user,id_artistname,id_collective,users_type,status)
SELECT
(SELECT id_user FROM artistnames WHERE artistname = 'yoannis'),
(SELECT id FROM artistnames WHERE artistname = 'yoannis'),
('1'),(2),(0)
UNION ALL
SELECT
(SELECT id_user FROM artistnames WHERE artistname = 'paul'),
(SELECT id FROM artistnames WHERE artistname = 'paul'),
('1'),(4),(0)
ON DUPLICATE KEY UPDATE dummy_column = dummy_column + 1;
另一种方法是创建一个存储过程。这比较棘手。
代码示例:在MySQL参考手册
DELIMITER \\
DROP PROCEDURE IF EXISTS sp_test\\
CREATE PROCEDURE sp_test(in_id_user, in_id_artistname, in_id_collective, in_users_type, in_status)
BEGIN
CASE
WHEN NOT EXISTS (SELECT id_user FROM artistnames WHERE artistname = 'yoannis') OR ...
THEN INSERT ...
END CASE;
END\\
DELIMITER ;
答
当你有一个UNIQUE
可以使用ON DUPLICATE KEY UPDATE
运行没有风险,并且重复的值会更新,以便有没有重复的,与虚拟列的想法:
INSERT INTO collectives_users(id_user,id_artistname,id_collective,users_type,status)
SELECT
(SELECT id_user FROM artistnames WHERE artistname = 'yoannis'),
(SELECT id FROM artistnames WHERE artistname = 'yoannis'),
('1'),(2),(0)
UNION ALL
SELECT
(SELECT id_user FROM artistnames WHERE artistname = 'paul'),
(SELECT id FROM artistnames WHERE artistname = 'paul'),
('1'),(4),(0)
ON DUPLICATE KEY UPDATE dummy_column = dummy_column + 1;
我建议你使用WHERE NOT EXISTS
代替
INSERT INTO collectives_users(id_user,id_artistname,id_collective,users_type,status)
SELECT
(SELECT id_user FROM artistnames WHERE artistname = 'yoannis'),
(SELECT id FROM artistnames WHERE artistname = 'yoannis'),
('1'),(2),(0)
UNION ALL
SELECT
(SELECT id_user FROM artistnames WHERE artistname = 'paul'),
(SELECT id FROM artistnames WHERE artistname = 'paul'),
('1'),(4),(0)
WHERE NOT EXISTS(SELECT Id
FROM collectives_users t2
WHERE t2.Id = artistnames.Id
我没有尝试的代码,但它是你可以看看其中的味道。此外,更多关于'WHERE NOT EXISTS'in this thread。
表中的独特列是什么? – 2013-05-04 15:48:35
尝试像select * from(select ... union all select ...)_t where ... not in(select ... from collective_users)来尝试给括号加上括号 – 2013-05-04 15:51:10
@JW你的意思是主键。 – Juampi 2013-05-04 15:58:45