Two ways to quickly create an empty table in MySQL _ MySQL

There are two ways to quickly create an empty table in MySQL:

1. create table t_name select...

2. create table t_name like...

First, some definitions of the original table will be removed, and the engine is the system default engine.

This is the case in the manual: Some conversion of data types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns.

The second method is to completely copy the original table.

Create a Test table first:

Mysql> create database dbtest;

Query OK, 1 row affected (0.03 sec)

Mysql> use dbtest;

Database changed

Mysql> create table t_old

-> (

-> Id serial,

-> Content varchar (8000) not null,

-> 'Desc' varchar (100) not null)

-> Engine innodb;

Query OK, 0 rows affected (0.04 sec)

Mysql> show create table t_old;

+ ------- + ------------------------------------------------- +

| Table | Create Table |

+ ------- + -------------------------------------------------- +

| T_old | create table 't_ old '(

'Id' bigint (20) unsigned not null auto_increment,

'Content' varchar (8000) not null,

'Desc' varchar (100) not null,

Unique key 'id' ('id ')

) ENGINE = InnoDB default charset = latin1 |

+ ------- + ---------------------------------------------------- +

1 row in set (0.00 sec)

Method 1:

Mysql> create table t_select select * from t_old where 1 = 0;

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql> show create table t_select;

+ ---------- + -------------------------------------------- +

| Table | Create Table + ---------- + ----------------------------------------------- +

| T_select | create table 't_ Select '(

'Id' bigint (20) unsigned not null default '0 ',

'Content' varchar (8000) not null,

'Desc' varchar (100) NOT NULL

) ENGINE = MyISAM default charset = latin1 |

+ ---------- + ------------------------------------------- +

1 row in set (0.00 sec)

Method 2:

Mysql> create table t_like like t_old;

Query OK, 0 rows affected (0.02 sec)

Mysql> show create table t_like;

+ -------- + --------------------------------------------------- +

| Table | Create Table |

+ -------- + --------------------------------------------------- +

| T_like | create table 't_ like '(

'Id' bigint (20) unsigned not null auto_increment,

'Content' varchar (8000) not null,

'Desc' varchar (100) not null,

Unique key 'id' ('id ')

) ENGINE = InnoDB default charset = latin1 |

+ -------- + --------------------------------------------------- +

1 row in set (0.00 sec)