creating multiple tables with single sql command

Question

I searched for this here and on google and surprisingly couldn't find an answer. I tried to create syntax to submit to mysql that would create multiple tables with the same columns, but it returned an error. Can you point out what is wrong with my syntax, or if this is even possible?

 CREATE TABLE news, life
 (
 id int PRIMARY KEY AUTO_INCREMENT ,
 name varchar( 30 ) ,
 email varchar( 50 ) ,
 COMMENT text,
 datetime datetime,
 ip varchar( 20 )
 ) 

Answer 1

It's not possible like that.

Think about your table design. This sounds like you should consider creating a single table and adding another column type that will be news or life (or a reference to another table defining types).

---

If you really need two tables, create your first table:

 CREATE TABLE news
 (
 id int PRIMARY KEY AUTO_INCREMENT ,
 name varchar( 30 ) ,
 email varchar( 50 ) ,
 COMMENT text,
 datetime datetime,
 ip varchar( 20 )
 )

and then

CREATE TABLE life AS ( SELECT * FROM news where 1=2 );

Indexes and constraints (UNIQUE, PRIMARY KEY, FOREIGN KEY) will not be copied though. You will have to handle them yourself.

Answer 2

you are in mysql so you can use the LIKE clause of the CREATE TABLE command; that way you will also get column attributes and indexes copied over e.g.

CREATE TABLE new_tbl LIKE orig_tbl;

see http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table: CREATE TABLE new_tbl LIKE orig_tbl; The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table. LIKE works only for base tables, not for views. CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions.