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?

The only mechanism sql has for inserting a record is with an insert statement so I'm not clear what your problem is? — P.Salmon
– P.Salmon, Commented Jan 5, 2019 at 14:39
I think you can use trigger on create table, search for that — Abhay Sehgal
– Abhay Sehgal, Commented Jan 5, 2019 at 14:43
@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 — raven_977
– raven_977, Commented Jan 5, 2019 at 14:45
Would it be enough to check whether the table has any records? — Barmar
– Barmar, Commented Jan 5, 2019 at 14:54
Define "just created." What exactly does that mean here, and how can we know whether or not the table were just created? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 5, 2019 at 14:55

Barmar · Accepted Answer · 2019-01-05 15:11:05Z

9

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.

edited Jan 5, 2019 at 15:11

answered Jan 5, 2019 at 14:59

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

raven_977 Over a year ago

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 Over a year ago

Adding aliases to the SELECT clause fixes it.

GMB · Accepted Answer · 2019-01-05 15:22:22Z

4

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.

answered Jan 5, 2019 at 15:22

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related