MySQL使用SELECT FROM DUAL WHERE NOT EXISTS将多行插入到空表中

Question

如果表肯定是空的，我将使用此代码插入默认行。我试图扩展它以插入多行，但无法弄清楚语法:

INSERT INTO myTable(`myCol`)
SELECT 'myVal'
FROM DUAL
WHERE NOT EXISTS (SELECT * FROM myTable);

<小时/>

我得到了什么(@Uueerdo)

CREATE TEMPORARY TABLE `myDefaults` ( name VARCHAR(100) NULL DEFAULT NULL);# MySQL returned an empty result set (i.e. zero rows).

INSERT INTO myDefaults (name) VALUES ('a'), ('b');# 2 rows affected.


SET @valCount := 0;# MySQL returned an empty result set (i.e. zero rows).

SELECT COUNT(1) INTO @valCount FROM blsf;# 1 row affected.


INSERT INTO blsf(name)
SELECT name 
FROM myDefaults 
WHERE @valCount > 0;# MySQL returned an empty result set (i.e. zero rows).


DROP TEMPORARY TABLE `myDefaults`;# MySQL returned an empty result set (i.e. zero rows).

Answer 1

这样的事情应该有效:

CREATE TEMPORARY TABLE `myDefaults` ( the_value INT|VARCHAR|whatever... )
;
INSERT INTO myDefaults (the_value) VALUES (myVal1), (myVal2), ....
;

SET @valCount := 0; -- Because I am paranoid ;)
SELECT COUNT(1) INTO @valCount FROM myTable;

INSERT INTO myTable(myCol)
SELECT the_value 
FROM myDefaults 
WHERE @valCount = 0
;

DROP TEMPORARY TABLE `myDefaults`;