MySQL multi CREATE TABLE syntax help?

Question

Hi guys,

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

try this:

use database_name;

create table a..; create table b..; create table c..;

Answer 2

Are the tables referencing (e.g. primary keys and the like) one another? Tables are created serially, so if your second table is referencing a table that is not yet created, it will fail.

Answer 3

The create table syntax looks fine. Probably the tool you use to execute your SQL just executes the first statement.

Answer 4

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.

Answer 5

How do you execute your script ? If you do it from command line it should be something like this:

mysql -u[username] -p[password] --database DataBase1 < scriptname.sql