快捷搜索:

mysql快速创建空表

用什么措施可以创建空表?在MYSQL中有两种措施。

1、create table select ...

2、create table like ...

第一种很多人都知道,第二种却很少人用。

第一种有两个毛病

1、第一种会取消掉落原本表的有些定义。

手册上是这么讲的:

Some conversion of data types might occur. For example,

the AUTO_INCREMENT attribute is not preserved,

and VARCHAR columns can become CHAR columns.

不过我测试过,只会取消自增属性!

2、引擎是系统默认引擎。

第二种就不会。

我们来看看例子:

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.01 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=utf8 |

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

1 row in set (0.00 sec)

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

Query OK, 0 rows affected (0.01 sec)

Records: 0Duplicates: 0Warnings: 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=utf8 |

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

1 row in set (0.00 sec)

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=utf8 |

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

1 row in set (0.00 sec)

mysql>

您可能还会对下面的文章感兴趣: