[mysql] If table exists drop table then create it, if it does not exist just create it

I'm stumped, I don't know how to go about doing this.

Basically I just want to create a table, but if it exists it needs to be dropped and re-created, not truncated, but if it doesn't exist just create it.

Would anyone be able to help?

Thanks, George

Just use DROP TABLE IF EXISTS:

DROP TABLE IF EXISTS `foo`;
CREATE TABLE `foo` ( ... );

Try searching the MySQL documentation first if you have any other problems.

I needed to drop a table and re-create with a data from a view. I was creating a table out of a view and this is what I did:

DROP TABLE <table_name>;
CREATE TABLE <table_name> AS SELECT * FROM <view>;

The above worked for me using MySQL MariaDb.

Well... Huh. For years nobody mentioned one subtle thing.

Despite DROP TABLE IF EXISTS `bla`; CREATE TABLE `bla` ( ... ); seems reasonable, it leads to a situation when old table is already gone and new one has not been yet created: some client may try to access subject table right at this moment.

The better way is to create brand new table and swap it with an old one (table contents are lost):

CREATE TABLE `bla__new` (id int); /* if not ok: terminate, report error */
RENAME TABLE `bla__new` to `bla`; /* if ok: terminate, report success */
RENAME TABLE `bla` to `bla__old`, `bla__new` to `bla`;
DROP TABLE IF EXISTS `bla__old`;

• You should check the result of CREATE ... and do not continue in case of error, because failure means that other thread didn't finish the same script: either because it crashed in the middle or just didn't finish yet -- it's a good idea to inspect things by yourself.
• Then, you should check the result of first RENAME ... and do not continue in case of success: whole operation is successfully completed; even more, running next RENAME ... can (and will) be unsafe if another thread has already started same sequence (it's better to cover this case than not to cover, see locking note below).
• Second RENAME ... atomically replaces table definition, refer to MySQL manual for details.
• At last, DROP ... just cleans up the old table, obviously.

Wrapping all statements with something like SELECT GET_LOCK('__upgrade', -1); ... DO RELEASE_LOCK('__upgrade'); allows to just invoke all statements sequentially without error checking, but I don't think it's a good idea: complexity increases and locking functions in MySQL aren't safe for statement-based replication.

If the table data should survive table definition upgrade... For general case it's far more complex story about comparing table definitions to find out differences and produce proper ALTER ... statement, which is not always possible automatically, e.g. when columns are renamed.

Side note 1: You can deal with views using the same approach, in this case CREATE/DROP TABLE merely transforms to CREATE/DROP VIEW while RENAME TABLE remains unchanged. In fact you can even turn table into view and vice versa.

CREATE VIEW `foo__new` as ...; /* if not ok: terminate, report error */
RENAME TABLE `foo__new` to `foo`; /* if ok: terminate, report success */
RENAME TABLE `foo` to `foo__old`, `foo__new` to `foo`;
DROP VIEW IF EXISTS `foo__old`;

Side note 2: MariaDB users should be happy with CREATE OR REPLACE TABLE/VIEW, which already cares about subject problem and it's fine points.