MySQL inserting data only if table doesn't exist

Question

Using strictly SQL (no PHP or anything else), is it possible to create a table and insert default data into that table only if that table doesn't exist?

Answer 1

Use the CREATE TABLE ... SELECT format:

create table if not exists tablename as
select * from defaultdata;

Answer 2

Here is one way of doing it:

CREATE TABLE IF NOT EXISTS T (
  ID int(10) unsigned NOT NULL primary key,
  NAME varchar(255) NOT NULL
);

REPLACE INTO T SELECT 1, 'John Doe';
REPLACE INTO T SELECT 2, 'Jane Doe';

REPLACE is a MySQL extension to the SQL standard that either inserts, or deletes and inserts.

Answer 3

You might do a select on the one of the meta data tables

if(not exists select * from whatever_meta where table_name = "whatever)
begin
   ...
end

You would have to do some research to figure out how exactly...

Answer 4

Can you store the table status as a variable, then use that variable to determine whether to insert data? Ex:

@status = SHOW TABLES LIKE 'my_table';
INSERT INTO my_table VALUES (1,'hello'),(2,'world') WHERE @status <> false;

The problem with Paul Morgan's answer is that it expects data to already exist in another table. Jonas' answer would be extremely resource exhaustive, especially if there's a lot of REPLACES (which are unnecessary if the table exists).

Answer 5

May be I am missing the point but why can't the default data be a set of insert statements...and what one simply needs to do is create the table if it does not exist followed by insert statements...that ways the default data does not have to exist in a different table.