mysql - 插入多行而不重复它们 - MySQL

Question

我正在尝试执行此查询，但它似乎返回错误:

INSERT INTO `categories` (`name`,`path`) VALUES ('TEST 1' , 'test-1'),('TEST 2' , 'test-2'),('TEST 3' , 'test-3')
WHERE (`name`,`path`) NOT IN (SELECT `name`,`path` FROM `categories`);

对此有什么帮助吗？非常感谢。

Answer 1

您尝试的逻辑是使用如下语法实现的:

INSERT INTO `categories` (`name`,`path`)
     SELECT name, path
     FROM (SELECT 'TEST 1' as name, 'test-1' as path UNION ALL
           SELECT 'TEST 2', 'test-2' UNION ALL
           SELECT 'TEST 3' , 'test-3'
          ) t
     WHERE NOT EXISTS (SELECT 1
                       FROM categories c
                       WHERE c.name = t.name and c.path = t.path
                      );

但是，您应该使用唯一索引在数据库中进行检查。所以更好的解决方案是进行插入并仅具有:

create unique index idx_categories_name_path on categories(name, path)

使用插入，例如:

INSERT INTO `categories` (`name`,`path`)
     SELECT name, path
     FROM (SELECT 'TEST 1' as name, 'test-1' as path UNION ALL
           SELECT 'TEST 2', 'test-2' UNION ALL
           SELECT 'TEST 3' , 'test-3'
          ) t
     ON DUPLICATE KEY UPDATE name = VALUES(name);