if table does not exist execute a long query

Question

I am using MySQL 5.0+ and I am trying to execute a big list of commands if a table does not exist. So I would like to have:

if not exist table
then
10000 line query that creates and populates the table with a lot of entries.
end if

The only problem is that I have been searching and so far I found out that MySQL does not support such a feature.

At the current moment I have:

IF NOT EXISTS `profiles`
THEN
    A LOT OF QUERIES;
END IF;

For some reason it keeps on giving me error saying syntax is wrong on line 1.

So I was wondering if anyone would happen to have a better idea as to how go about approaching this problem, or how to fix it.

Answer 1

You can try something like

CREATE PROCEDURE test()
BEGIN
  DECLARE tmp INT;
  DECLARE CONTINUE HANDLER FOR  1146  -- 1146 - table does not exist
  BEGIN
     -- A lot of queries
  END;
  SELECT 1 INTO tmp FROM profiles LIMIT 1; -- INTO just to prevent any output
END;

Answer 2

You can try something like:

select * from table1 where exists (select table_name from information_schema.tables where table_schema=database() and table_name = 'table1');

The 'select * from table1' will only occur if table1 exists in the current database. This is a good way to get around querying for information in a non-existent table, which results in an error. You can run this outside of a stored procedure, and would need to append the 'where exists ...' to each of your queries. See http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html for more information.

Answer 3

You have to query the information_schema database. Found this answer on MySQL Forums:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'tablename';

Answer 4

Addding on to code from bfavaretto, if you do have information_schema.tables, try something like this:

IF NOT EXISTS (SELECT * FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'tablename')
do your big long create table stuff