insert into select用法

本篇文章主要讲解insert into select 的用法，以及insert into select的坑或者注意事项。本篇文章中的sql基于mysql8.0进行讲解

一、insert into select

该语法常用于从另一张表查询数据插入到某表中。如备份表的时候，将表中的数据迁移到备份表中。

以下测试insert into select用于备份表

create table if not exists user
(
	id int not null primary key,
	sex char(3) null,
	NAME char(20) null
);
INSERT INTO user VALUES 
(1,'nan','陈一'),
(2,'nv','珠二'),
(3,'nv','张三'),
(4,'nan','李四'),
(5,'nv','王五'),
(6,'nan','赵六');
1
2
3
4
5
6
7
8
9
10
11
12
13

备份表sql

-- 创建bak备份表
create table user_bak_20230731 like user;
-- 将user表的数据插入到备份表
insert into user_bak_20230731 select * from user;
1
2
3
4

备份表数据如下，可以看到与user原表内容一致。

注意：

1. 若insert into select一次性插入数据量过多，建议分批插入，避免io异常或缓存不足。

二、insert into select插入失败

失败场景： hive数据库中，针对分区表直接执行insert into select时报错。（其他类型数据库针对分区表直接执行insert into select没发现这个问题，mysql数据库亲测是ok的）
错误日志：

FAILED: SemanticException 1:23 Need to specify partition columns because the destination table is partitioned. Error encountered near token 'user_tmp'
1

解决方法
需要在插入的数据中指定分区字段的数值是多少。

比如：
建表语句

create table test1 (
starttime string,
endtime string,
title string
)
PARTITIONED BY (username string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '| '
STORED AS TEXTFILE;

-- 创建另一张表
create table test2 like test1;
1
2
3
4
5
6
7
8
9
10
11
12

插入语句：

insert into table test2 PARTITION(username='admin') select starttime, endtime, title from test1 where username = 'admin';
1