在MySQL中有两种方法
create table t_name select
create table t_name like
第一种会取消掉原来表的有些定义且引擎是系统默认引擎
手册上是这么讲的Some conversion of data types might occur For example the AUTO_INCREMENT attribute is not preserved and VARCHAR columns can become CHAR columns
第二种就完全复制原表
先建立测试表:
mysql> create database dbtest;
Query OK row affected ( sec)
mysql> use dbtest;
Database changed
mysql> create table t_old
> (
> id serial
> content varchar() not null
> `desc` varchar() not null)
> engine innodb;
Query OK rows affected ( sec)
mysql> show create table t_old;
+++
| Table | Create Table |
+++
| t_old | CREATE TABLE `t_old` (
`id` bigint() unsigned NOT NULL auto_increment
`content` varchar() NOT NULL
`desc` varchar() NOT NULL
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin |
+++
row in set ( sec)
第一种方式
mysql> create table t_select select * from t_old where = ;
Query OK rows affected ( sec)
Records: Duplicates: Warnings:
mysql> show create table t_select;
+++
| Table | Create Table +++
| t_select | CREATE TABLE `t_select` (
`id` bigint() unsigned NOT NULL default
`content` varchar() NOT NULL
`desc` varchar() NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin |
+++
row in set ( sec)
第二种方式
mysql> create table t_like like t_old;
Query OK rows affected ( sec)
mysql> show create table t_like;
+++
| Table | Create Table |
+++
| t_like | CREATE TABLE `t_like` (
`id` bigint() unsigned NOT NULL auto_increment
`content` varchar() NOT NULL
`desc` varchar() NOT NULL
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin |
+++
row in set ( sec)
mysql>
