mysql把一张表的数据复制到其他表, insert into select from 和 create table table_name_new as select。mysql不支持select * into new_table.

创建数据

数据库版本

1
2
3
4
5
6
7
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.27 |
+-----------+
1 row in set (0.03 sec)

两张表结构一样,t_rolet_role_copy1

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `t_role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色Id',
`created` datetime NOT NULL COMMENT '创建时间',
`updated` datetime NOT NULL COMMENT '最后更新时间',
`cn_name` varchar(255) DEFAULT NULL COMMENT '角色中文名称',
`modules` varchar(255) DEFAULT NULL COMMENT 'Java客户端权限id列表',
`name` varchar(255) NOT NULL COMMENT '角色英文名称',
`res_ids` varchar(255) DEFAULT NULL COMMENT '业务功能id列表',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='账号类型表';

数据

1
2
3
INSERT INTO `test`.`t_role`(`id`, `created`, `updated`, `cn_name`, `modules`, `name`, `res_ids`) VALUES (1, '2021-01-19 19:25:11', '2021-01-19 19:25:14', 'y', '1', '1', '1');
INSERT INTO `test`.`t_role`(`id`, `created`, `updated`, `cn_name`, `modules`, `name`, `res_ids`) VALUES (2, '2021-01-19 19:25:11', '2021-01-19 19:25:14', 'y', '1', '1', '1');

插入表结构相同的已存在的表 insert into select from

语法
1
2
INSERT INTO table2
SELECT * FROM table1;

可指定字段

1
2
3
4
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

可指定数据过滤条件

1
2
3
4
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1 where condition1, condition2 ...;
示例

1.把t_role的数据复制到t_role_copy1

1
insert into t_role_copy1 select * from t_role

2.只复制id为1 的数据,假如过滤的条件查询不到数据不会报错,相当于不复制数据。

1
insert into t_role_copy1 select * from t_role where id = 1

3.指定字段,未指定的字段需要有默认值,否则会报错

1
insert into t_role_copy1(cn_name, modules,name, res_ids) select cn_name, modules,name, res_ids from t_role

created 和 updated没有默认值,id是主键,默认自增的

1
2
3
insert into t_role_copy1(cn_name, modules,name, res_ids) select cn_name, modules,name, res_ids from t_role
> 1364 - Field 'created' doesn't have a default value
> 时间: 0s

4.通过函数指定默认值或插入常量

1
2
3
4
5
6
7
8
9
INSERT INTO t_role_copy1 ( created, updated, cn_name, modules, NAME, res_ids ) SELECT
'2020-01-01 00:00:00',
now( ),
cn_name,
modules,
NAME,
res_ids
FROM
t_role

5.实际上,插入数据时,插入表的字段和查询表的字段是根据顺序来匹配的,所以和字段名无关。只要数据类型和个数能对上就能插入。所以可以从不同结构的表查询数据插入。

以下语句会报错,类型不匹配

1
2
3
4
5
6
7
8
9
INSERT INTO t_role_copy1 ( created, updated, cn_name, modules, NAME, res_ids ) SELECT
cn_name,
'2020-01-01 00:00:00' as created,
now( ) as updated,
modules,
NAME,
res_ids
FROM
t_role
1
2
3
4
5
6
7
8
9
10
11
INSERT INTO t_role_copy1 ( created, updated, cn_name, modules, NAME, res_ids ) SELECT
cn_name,
'2020-01-01 00:00:00' as created,
now( ) as updated,
modules,
NAME,
res_ids
FROM
t_role
> 1292 - Incorrect datetime value: 'y' for column 'created' at row 1
> 时间: 0s

6.name和cn_name 数据类型相同,调换顺序可以执行成功,并且把原表的值插入到新表时交换了位置。

1
2
3
4
5
6
7
8
9
INSERT INTO t_role_copy1 ( created, updated, cn_name, modules, NAME, res_ids ) SELECT
'2020-01-01 00:00:00' as created,
now( ) as updated,
NAME,
modules,
cn_name,
res_ids
FROM
t_role

查询数据插入新表自动创建表

语法

1.复制表结构及其数据

1
create table table_name_new as select * from table_name_old

2.只复制表结构,原理是复制表结构和数据,但是增加查询条件,满足条件1=2的数据不存在,则未插入数据。

1
create table table_name_new as select * from table_name_old where 1=2;

3.只复制指定字段

1
create table table_name_new as select column1,column2... from table_name_old
示例

1.复制t_role表为t_role_new

1
create table t_role_new as select * from t_role

2.根据t_role的id和created字段创建一张新表

1
create table t_role_new1 as select id,created from t_role

结果

1
2
3
4
5
6
7
8
mysql> select * from t_role_new1;
+----+---------------------+
| id | created |
+----+---------------------+
| 1 | 2021-01-19 19:25:11 |
| 2 | 2021-01-19 19:25:11 |
+----+---------------------+
2 rows in set (0.03 sec)

3.新建表时更改字段名, as 设置字段别名。

1
create table t_role_new2 as select id,created as date from t_role

结果,新表created对应的是date字段

1
2
3
4
5
6
7
8
mysql> select * from t_role_new2;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2021-01-19 19:25:11 |
| 2 | 2021-01-19 19:25:11 |
+----+---------------------+
2 rows in set (0.03 sec)

参考链接

[菜鸟教程]https://www.runoob.com/sql/sql-insert-into-select.html