24

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 currently empty. I'm using mysql.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
user291701
  • 38,411
  • 72
  • 187
  • 285

3 Answers3

52

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)
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
2

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

Jeff Swensen
  • 3,513
  • 28
  • 52
  • 2
    +1: I was just going to write something like this when your answer popped up. :) An alternative to REPLACE is to use INSERT IGNORE, which will produce a warning (rather than an error) when the statement fails to execute. – Ted Hopp Mar 15 '11 at 03:30
  • See also `INSERT INTO ... ON DUPLICATE KEY UPDATE ...` – Rick James Apr 15 '23 at 05:20
0

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.

joehep
  • 136
  • 1
  • 5
  • What kind of performance hit is there doing that sub query? Would it be better to just do an insert and catch the error if it exists? – pwaterz Nov 19 '18 at 20:07
  • The performance hit would depend on if any indexes were used. From a readability and maintenance POV, doing a SELECT and then doing the insert if the row doesn't exist is probably better, just because this kind of query is just weird. I also wouldn't want to do the INSERT IGNORE just because it seems like you're asking for triage headaches down the road. – joehep Jun 17 '19 at 17:26