I'm using MySQL 5.+ and i'm trying to carry out a large listing of instructions if your table doesn't exist. So I must have:
if not exist table then 10000 line query that creates and populates the table with a lot of entries. end if
The only issue is the fact that I've been searching and to date I discovered that MySQL doesn't support this type of feature.
In the current moment I've:
IF NOT EXISTS `profiles` THEN A LOT OF QUERIES; END IF;
For whatever reason it continues giving me error saying syntax is wrong online 1.
So I'm wondering if anybody would have a much better idea regarding how start approaching this issue, or how you can repair it.
Addding onto code from bfavaretto, if you have information_schema.tables, try something similar to this:
IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_schema = 'databasename' AND table_name = 'tablename') do your big long create table stuff
You need 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';
You can test something similar to
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;