MySql create few table with the same structure

mysql, sql

Solution

The fastest method for creating tables with duplicate structures would be as follows:

CREATE TABLE tb1
(
    id int not null auto_increment,
    ...,
    primary key (id)
);
create table tb2 like tb1;
create table tb3 like tb1;
create table tb4 like tb1;
...
create table tb8 like tb1;

This copies both the table structure and corresponding indexes.

If you need to make a table without indexes, this may be OK for you

CREATE TABLE tb1
(
    id int not null auto_increment,
    ...,
    primary key (id)
);
CREATE TABLE tb2 AS SELECT * FROM tb1 WHERE 1=2;

Depending on columns definitions, this may or may not work.

CAVEAT

This cannot be do for a table with foreign key constraints. That would be done in stages.

Problem

I have to create 8 tables with the exact same structure. The easiest way is to copy-paste the code and apply different names for the tables. I want to be cool and create them with one cycle. How can I make that cycle and what is best way to apply the different names ? Thanks

Original source