MYSQL 条件插入多行

Question

是否可以在根据条件检查每个新行的同时插入多行？我知道您可以运行包含多行的简单插入

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9)
AND
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9)
ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c)

我正在尝试做这样的事情

INSERT INTO x_table(a,b,c) 
SELECT VALUES(a),VALUES(b),VALUES(c)
FROM y_table
WHERE NOT EXISTS (SELECT * FROM x_table
WHERE a != VALUES(a) 
AND b != VALUES(b))

测试示例(已测试和工作(

INSERT INTO `table` (id, timestamp, v1, v2, v3, v4, v5)
SELECT id, timestamp, v1, v2, v3, v4, v5
FROM (SELECT 6783 AS id, FROM_UNIXTIME('1580194194') AS timestamp, 1 AS v1, 0 AS v2, 1 AS v3, 0 AS v4, 45 AS v5
UNION ALL
SELECT 6845,FROM_UNIXTIME('1580194194'),1,0,1,0,107
UNION ALL
SELECT 6973,FROM_UNIXTIME('1580194194'),1,0,1,0,234
) y
WHERE NOT EXISTS (SELECT * FROM `table` x WHERE x.v1 = y.v1 AND x.v3 = y.v3 AND x.v5 = y.v5 ORDER BY TIMESTAMP DESC LIMIT 1)

Answer 1

你可以写这样的东西：

INSERT INTO x_table
SELECT a, b, c
FROM (SELECT 1 AS a, 2 AS b, 5 AS c
UNION ALL
SELECT 4, 5, 6
UNION ALL
SELECT 7, 8, 9
) y
WHERE NOT EXISTS (SELECT * FROM x_table x WHERE x.a = y.a AND x.b = y.b)

请注意，我认为您的EXISTS子句需要使用=，而不是!=否则它将是正确的，除非x_table中只有 1 行具有匹配的a和b值到要插入的第一行。

在 dbfiddle 上演示

Answer 2

您可以在插入触发器之前进行一些检查。像这样：

CREATE TRIGGER `TRG_x_table_before_insert` BEFORE INSERT ON `x_table` FOR EACH ROW BEGIN
SET @found = NULL;
SELECT
a INTO @found
FROM
y_table
WHERE
a = NEW.a
AND b = NEW.b
;
IF @found IS NOT NULL THEN
SET @msg = 'Insert failed';
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
END IF;
END