仅在表为空时执行mysql插入 [英] Execute mysql insert only if table is empty

问题描述

下面的问题，我一直试图模仿正确的答案，仅在表为空的情况下才将数据插入表中.

Following this question, I've been trying to mimic the correct answer to insert data in my table only if it is empty.

如果表为空，则执行INSERT吗?

这是我的代码:

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

但是我没有得到如果表为空的元素，而是得到:

But instead of adding the elements if the table is empty, I just get:

#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`)'

我应该如何精确地编写查询，以便仅在表为空时才插入值?

How exactly am I supposed to write the query so that the values are inserted only if the table is empty?

推荐答案

您的语句中有语法错误:

You have a syntax error in your statement:

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两次，每个子查询一次.您也可以这样做:

You need to repeat the where twice in this case, once for each subquery. You can also do:

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`);

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

In this version, you need to assign names to the columns.

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

Or, you could just use two separate insert statements.

这篇关于仅在表为空时执行mysql插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！