islandguy islandguy - 4 months ago 23
SQL Question

MySQL conditionally create a table

Is it possible to do a conditional create table in MySql based on the result of a select statement? Essentially, if I have a schema used by multiple clients but only some of them need a table can I create it based on another value...for example...if I have a user John then Create this table...because John's app needs it.

Something along the lines of:

IF EXISTS (SELECT * FROM USERS WHERE name='JOHN')

CREATE TABLE CREATE_THIS
(
ID VARCHAR(255) COLLATE utf8_bin NOT NULL,
NAME VARCHAR(255) COLLATE utf8_bin NOT NULL
)
ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer

This sort of business logic does not really belong within the database layer of your application. It would normally reside at a higher level, within whatever code you use to connect to the database.

However, for completeness, there is a (very hackish) way to accomplish what you ask by preparing a statement from a string that contains the SQL to be executed:

SET @sql := CASE WHEN EXISTS (SELECT * FROM USERS WHERE name='JOHN') THEN
 'CREATE TABLE CREATE_THIS (
    ID VARCHAR(255) COLLATE utf8_bin NOT NULL,
    NAME VARCHAR(255) COLLATE utf8_bin NOT NULL   
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8'
END;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Comments