环境:Win10 64位,MySQL 5.5
下面说下练习时发现的问题及测试过程,如有不对或不全面的地方,欢迎指正,谢谢。
现有一张表user,结构及数据如下,特殊说明下,表中主键id为自增长,且表中有个字段updated_time自动记录数据更新时间,并且表中已有3个数据,id自增长已到4
mysql> desc user;
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| updated_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.01 sec)
mysql> select * from user;
+----+------+--------+-----+---------------------+
| id | name | gender | age | updated_time |
+----+------+--------+-----+---------------------+
| 2 | 张三 | 男 | 22 | 2018-11-01 14:15:43 |
| 3 | 小红 | 女 | 18 | 2018-11-01 14:21:12 |
| 4 | 小明 | M | 16 | 2018-11-02 16:48:41 |
+----+------+--------+-----+---------------------+
3 rows in set (0.00 sec)
第一种 "show create table"方式
mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`gender` char(1) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `user_copy` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(255) NOT NULL,
-> `gender` char(1) NOT NULL,
-> `age` tinyint(3) unsigned NOT NULL,
-> `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)
mysql> show columns from user_copy;
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| updated_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.02 sec)
mysql> select * from user_copy;
Empty set (0.00 sec)
这样复制的表user_copy只有结构,没有数据,需要数据的话,需要再行导入,导入方法见文章最后,这里我们不导入,直接插入一条数据可见id直接从5开始
mysql> select * from user_copy;
Empty set (0.00 sec)
mysql> insert into user_copy(name,gender,age) values('小白','女',14);
Query OK, 1 row affected (0.02 sec)
mysql> select * from user_copy;
+----+------+--------+-----+---------------------+
| id | name | gender | age | updated_time |
+----+------+--------+-----+---------------------+
| 5 | 小白 | 女 | 14 | 2018-11-02 17:39:34 |
+----+------+--------+-----+---------------------+
1 row in set (0.00 sec)
第二种 "create table user_copy2 like user;"
mysql> create table user_copy2 like user;
Query OK, 0 rows affected (0.18 sec)
mysql> desc user_copy2;
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| updated_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.02 sec)
mysql> show create table user_copy2;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_copy2 | CREATE TABLE `user_copy2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`gender` char(1) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
这种方式复制的表同样只有结构没有数据,不过,要注意的是,看表创建语句与user表有个不同的地方是:user_copy2中没有“AUTO_INCREMENT=5” 这句,这会导致user_copy中新添加的数据id会从1开始,添加一条试下
mysql> select * from user_copy2;
Empty set (0.00 sec)
mysql> insert into user_copy2(name,gender,age) values('小白','女',14);
Query OK, 1 row affected (0.03 sec)
mysql> select * from user_copy2;
+----+------+--------+-----+---------------------+
| id | name | gender | age | updated_time |
+----+------+--------+-----+---------------------+
| 1 | 小白 | 女 | 14 | 2018-11-02 17:42:29 |
+----+------+--------+-----+---------------------+
1 row in set (0.00 sec)
第三种 “create table user_copy3 select * from user;”
mysql> create table user_copy3 select * from user;
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc user_copy3;
+--------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------------------+-------+
| id | int(10) unsigned | NO | | 0 | |
| name | varchar(255) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| updated_time | timestamp | NO | | 0000-00-00 00:00:00 | |
+--------------+---------------------+------+-----+---------------------+-------+
5 rows in set (0.02 sec)
mysql> show create table user_copy3;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_copy3 | CREATE TABLE `user_copy3` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL,
`gender` char(1) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`updated_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from user_copy3;
+----+------+--------+-----+---------------------+
| id | name | gender | age | updated_time |
+----+------+--------+-----+---------------------+
| 2 | 张三 | 男 | 22 | 2018-11-01 14:15:43 |
| 3 | 小红 | 女 | 18 | 2018-11-01 14:21:12 |
| 4 | 小明 | M | 16 | 2018-11-02 16:48:41 |
+----+------+--------+-----+---------------------+
3 rows in set (0.00 sec)
这种复制方法,会把表结构及数据都复制到user_copy3中,但不同的地方可以从user_copy3中的创建语句中可以看出来,与user的区别:
- id的主键设定没了,默认自增长也没了,变成了默认0
- 没有“AUTO_INCREMENT=5”
- updated_time的默认值由“CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”变成了“0000-00-00 00:00:00”
mysql> select * from user_copy3;
+----+------+--------+-----+---------------------+
| id | name | gender | age | updated_time |
+----+------+--------+-----+---------------------+
| 2 | 张三 | 男 | 22 | 2018-11-01 14:15:43 |
| 3 | 小红 | 女 | 18 | 2018-11-01 14:21:12 |
| 4 | 小明 | M | 16 | 2018-11-02 16:48:41 |
+----+------+--------+-----+---------------------+
3 rows in set (0.00 sec)
mysql> insert into user_copy3(name,gender,age) values('小白','女',14);
Query OK, 1 row affected (0.02 sec)
mysql> select * from user_copy3;
+----+------+--------+-----+---------------------+
| id | name | gender | age | updated_time |
+----+------+--------+-----+---------------------+
| 2 | 张三 | 男 | 22 | 2018-11-01 14:15:43 |
| 3 | 小红 | 女 | 18 | 2018-11-01 14:21:12 |
| 4 | 小明 | M | 16 | 2018-11-02 16:48:41 |
| 0 | 小白 | 女 | 14 | 0000-00-00 00:00:00 |
+----+------+--------+-----+---------------------+
4 rows in set (0.00 sec)
mysql> insert into user_copy3(name,gender,age) values('小白','女',14);
Query OK, 1 row affected (0.03 sec)
mysql> insert into user_copy3(name,gender,age) values('小白','女',14);
Query OK, 1 row affected (0.03 sec)
mysql> select * from user_copy3;
+----+------+--------+-----+---------------------+
| id | name | gender | age | updated_time |
+----+------+--------+-----+---------------------+
| 2 | 张三 | 男 | 22 | 2018-11-01 14:15:43 |
| 3 | 小红 | 女 | 18 | 2018-11-01 14:21:12 |
| 4 | 小明 | M | 16 | 2018-11-02 16:48:41 |
| 0 | 小白 | 女 | 14 | 0000-00-00 00:00:00 |
| 0 | 小白 | 女 | 14 | 0000-00-00 00:00:00 |
| 0 | 小白 | 女 | 14 | 0000-00-00 00:00:00 |
+----+------+--------+-----+---------------------+
6 rows in set (0.00 sec)
所以,经过测试发现,完美的复制表方法是第一种方法,这样复制出来的表不会给你日后制造意想不到的bug
最后附上数据导入方法,从user导入到user_copy中如下所示
mysql> insert into user_copy(id,name,gender,age,updated_time) select id,name,gender,age,updated_time from user;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from user_copy;
+----+------+--------+-----+---------------------+
| id | name | gender | age | updated_time |
+----+------+--------+-----+---------------------+
| 2 | 张三 | 男 | 22 | 2018-11-01 14:15:43 |
| 3 | 小红 | 女 | 18 | 2018-11-01 14:21:12 |
| 4 | 小明 | M | 16 | 2018-11-02 16:48:41 |
+----+------+--------+-----+---------------------+
3 rows in set (0.00 sec)