Execute INSERT if table is empty?

Is there a way to do an insert under a count condition, something like:

INSERT INTO my_table (colname) VALUES('foo') IF COUNT(my_table) < 1

Basically I want to insert a single default record if the table is current开发者_开发问答ly empty. I'm using mysql.

Use SELECT instead of VALUES to be able to expand the query with a WHERE clause.

EXISTS is a better & faster test than COUNT

INSERT INTO my_table (colname)
SELECT 'foo'
WHERE NOT EXISTS (SELECT * FROM my_table)

One way would be to place a unique key on a column. Then execute a REPLACE:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

This is easier to read:

INSERT INTO my_table (colname) 
SELECT 'foo' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM my_table);

The lack of a VALUES is mitigated by the SELECT FROM DUAL which will provide the values. the FROM DUAL is not always required, but it doesn't hurt to include it for that weird configurations where it is required (like the installation of Percona I am using).

The NOT EXISTS is faster than doing a count which can be slow on a table with a large number of rows.