MySQL create table if not exists and insert record only if table was created

Question

I need to create a table and insert a first record only if the table was just newly created.

I do create the table with this statement:

CREATE TABLE IF NOT EXISTS tableName (
    id int(9) NOT NULL, 
    col1 int(9) DEFAULT NULL, 
    col2 int(3) unsigned zerofill DEFAULT NULL,
    PRIMARY KEY(id)
)  ENGINE = InnoDB DEFAULT CHARSET = latin1;

How do I insert an first record only if the table was just created?

Solution 1

Combine the creation and insert into a single statement:

CREATE TABLE IF NOT EXISTS tableName (
    id int(9) NOT NULL, 
    col1 int(9) DEFAULT NULL, 
    col2 int(3) unsigned zerofill DEFAULT NULL,
    PRIMARY KEY(id)
)  ENGINE = InnoDB DEFAULT CHARSET = latin1
AS SELECT 1 AS id, 10 AS col1, 5 AS col2;

If it doesn't create the table, AS SELECT ... clause is ignored.

Solution 2

That’s a good spot to use the INSERT IGNORE command rather than the INSERT command.

INSERT IGNORE INTO mytable (id, field1, field2) VALUES(1, 'foo', 'bar');

From the mysql documentation :

Errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors generate warnings instead.

Comments

[P.Salmon]

The only mechanism sql has for inserting a record is with an insert statement so I'm not clear what your problem is?

[Abhay Sehgal]

I think you can use trigger on create table, search for that

[raven_977]

@P.Salmon If the table exists and was not just created I do not want to insert an record, if the table was just created I want to insert a first record

[Barmar]

Would it be enough to check whether the table has any records?

[Tim Biegeleisen]

Define "just created." What exactly does that mean here, and how can we know whether or not the table were just created?

[raven_977]

@Barmar yes, this would work also for me.

[raven_977]

@Tim Biegeleisen with "just created" I mean created with the query I quoted in my question.

[Tim Biegeleisen]

Then see Barmar's answer, or you could also just do an insert after the create table statement, but put both of them into a single transaction.

[P.Salmon]

Either you manually or some code must be deciding to create the table. So what's the problem with adding an insert?

[Barmar]

@P.Salmon The database decides whether to create the table because of the IF NOT EXISTS clause.

[P.Salmon]

@barmar even mysql will not create a table out of the blue.

[Barmar]

It's not out of the blue, he's executing the above CREATE TABLE statement. But he only wants to add the insert depending if the statement actually needed to create the table.

[raven_977]

This is creating new columns for 1, 10 and 5. If these values would be in the existing columns for id, col1 and col1 it would be perfect

[Barmar]

Adding aliases to the SELECT clause fixes it.