mysql - 仅当表为空时才执行 mysql insert

Question

在这个问题之后，我一直在尝试模仿正确的答案，以便仅在表为空时才在表中插入数据。

Execute INSERT if table is empty?

这是我的代码:

INSERT INTO `statuses` SELECT 
    ('Something', 'Something else', 123),
    ('Something', 'Something else', 234)
    WHERE NOT EXISTS (SELECT * FROM `statuses`);

但是如果表是空的，我不会添加元素，而是得到:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT * FROM `statuses`)'

我究竟应该如何编写查询，以便只有在表为空时才插入值？

Answer 1

您的语句中存在语法错误:

INSERT INTO `statuses`
    (SELECT  'Something', 'Something else', 123
     WHERE NOT EXISTS (SELECT * FROM `statuses`)
    ) union all
    (SELECT 'Something', 'Something else', 234
     WHERE NOT EXISTS (SELECT * FROM `statuses`)
    );

在这种情况下，您需要重复 where 两次，每个子查询一次。您还可以:

INSERT INTO `statuses`
    select t.*
    from ((SELECT  'Something' as col1, 'Something else' as col2, 123 as col3
          ) union all
          (SELECT 'Something', 'Something else', 234
          )
         ) t
    WHERE NOT EXISTS (SELECT * FROM `statuses`);

在此版本中，您需要为列分配名称。

或者，您可以只使用两个单独的插入语句。