MySQL multi CREATE TABLE syntax help?

Question

I'm trying to write a MySQL script that creates several tables. I have:

CREATE TABLE `DataBase1`.`tbl_this`(
...
);
CREATE TABLE `DataBase1`.`tbl_that`(
...
);
... (14 more) ...

BUT, only the first CREATE TABLE statement is executed. I get no syntax errors. Erm, am I missing the MSSQL equivalent of GO ? What am I doing wrong here; how do I get this baby to run all the tables?

Answer 1

How are you executing this script?

If you are trying to run it programmatically, you should know that the MySQL API only executes one statement at a time by default. You can't string them together with semicolons and expect it to run all the statements.

You can execute each CREATE TABLE statement individually in a loop, or else you can run a script by feeding it as input to the mysql command-line client.

It's not as easy as it would seem to write a general-purpose script runner class in your application, because the full script syntax include many corner cases.

See examples of the corner cases in my answer to Loading .sql files from within PHP.