Check if a table exists, and if not, create it

Question

I'm using a Odbc connection to a mysql server, and was wondering how I would go about checking if a table exists within my database, and if not, create it.

Answer 1

Use CREATE TABLE IF NOT EXISTS.

UPDATE

For the record, not all RDBMSs support CREATE ... IF NOT EXISTS. MySQL does, but for those looking for a more portable solution, know that you have multiple (less efficient but otherwise functional) ways of achieving the same thing:

• unconditionally issue CREATE TABLE; the statement will fail if the table already exists, in which case simply swallow the error and continue
• SELECT from the tables object in the ANSI information_schema to see whether or not a given table already exists (as originally suggested by @Derek -- see @lexu's answer for the actual query); depending on the result, either issue CREATE TABLE -- or do not (when accessing information_schema.tables beware of issues such as table name case sensitivity, the possibility of your table appearing in multiple schemas, etc.)

Cheers, V.

Answer 2

I don't know the syntax for mysql on this, but all the databases I've ever used provide a set of tables containing meta-data about the data. I.e. system tables. You can simply do a select count on the correct system table using the table name in a where clause to find out if it exists. There may be other ways as well.

I'd suggest looking into the doco on mysql for information on the system tables and how to access them. Being mysql, it's probably quite simple.

Answer 3

Besides Vlad Romanscanu's approach to create table if not exists NEW_TABLE_NAME in one sweet direct statement , there is always the option to query the INFORMATION_SCHEMA. (I think that is what Derek CLarkson suggests).

This approach does require some more client-side coding ...

you can use this query:

select count(TABLE_NAME)
  from INFORMATION_SCHEMA 
 where TABLE_SCHEMA = 'your schema'
   and TABLE_NAME = 'your table'
group by TABLE_NAME

NOTE in Mysql you might be tempted to leave out that final 'group by', I tend to be a bit an*l about it and leave it in, so I don't have unpleasant encounters when working on another DBMS.
This case here, that is entirely MySQL specific, is borderline!