If the table exists, then create it; if it does not exist, just create it

Question

I'm at a standstill, I don't know how to do this.

Basically, I just want to create a table, but if it exists, it needs to be deleted and recreated, not truncated, but if it does not exist, just create it.

Can anyone help?

Thanks George

Answer 1

Just put DROP TABLE IF EXISTS `tablename`; before the CREATE TABLE statement.

This statement discards the table if it exists, but will not throw an error if it is not.

Answer 2

Just use DROP TABLE IF EXISTS :

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

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

Answer 3

Well ... For many years no one has mentioned one subtle thing.

Despite the DROP TABLE IF EXISTS 'bla'; CREATE TABLE 'bla' (... ); DROP TABLE IF EXISTS 'bla'; CREATE TABLE 'bla' (... ); DROP TABLE IF EXISTS 'bla'; CREATE TABLE 'bla' (... ); it seems reasonable, this leads to a situation where the old table has already disappeared and the new one has not yet been created: some client may try to access the object table right now.

It is best to create a new table and replace it with the old one (the contents of the table 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 not continue in case of an error, because a failure means that the other thread did not complete the same script: either because it crashed in the middle, or simply has not completed yet - it is a good idea to check things out by yourself.
• Then you should check the result of the first RENAME... and not continue if successful: the whole operation completed successfully; Moreover, starting the next RENAME... may (and will) be unsafe if another thread has already started the same sequence (it is better to cover this case than not to close, see the blocking note below).
• The second RENAME... atomically replaces the table definition; see the MySQL manual for details.
• Finally, DROP... just clears the old table, obviously.

Wrapping all statements with something like SELECT GET_LOCK('__upgrade', -1);... DO RELEASE_LOCK('__upgrade'); SELECT GET_LOCK('__upgrade', -1);... DO RELEASE_LOCK('__upgrade'); allows you to simply call all statements sequentially without error checking, but I don’t think it’s a good idea: complexity is increased, and the lock functions in MySQL are not safe for statement-based replication.

If the table data should survive updating the table definition ... In general, this is a much more complicated story about comparing table definitions to identify differences and creating the correct ALTER... operator, which is not always possible automatically, for example, when renaming columns.

Answer 4

I needed to drop the table and recreate the data from the view. I created a table from a view, and here is what I did:

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

The above worked for me using MySQL MariaDb MySQL.