MySQL 插入数据数据重复从另一个表导入数据

当使用MySQL插入数据时，我们可以根据需求选择合适的插入语句

酸菜鱼土豆大侠

5751人浏览 · 2022-12-08 22:39:33

酸菜鱼土豆大侠 · 2022-12-08 22:39:33 发布

当使用MySQL插入数据时，我们可以根据需求选择合适的插入语句。

一、方法分类

插入语句方法分类

二、具体方法

使用场景	作用	语句	注意
常规插入
	忽略字段名
	忽略字段名	insert into 表名 values (值1, 值2,...,值n)	默认value中的值依次填充所有字，如果出现唯一性冲突，就会抛出异常
	按照字段插入
	按照字段插入	insert into 表名(字段1, 字段2,...,字段n) values (值1, 值2,...,值n)	字段和值一一对应
	一次性插入多条数据
	一次性插入多条数据	insert into 表名(字段1, 字段2,...,字段n) values (值a1, 值a2,...,值an),(值b1, 值b2,...,值bn)	多行之间用逗号隔开，不需要再次写insert into语句
从另一个表导入	导出A表的某些数据插入到B表	insert into 表名B(字段B1, 字段B2,...,字段Bn) select 字段A1, 字段A2,...,字段An from 表名A where [执行条件]	字段A和B可以字段名称不一样，但是数据类型必须一致
插入时数据重复
	如果记录存在报错
	如果记录存在报错	insert into 表名(字段1, 字段2,...,字段n) values (值1, 值2,...,值n)	如果插入的数据记录存在，报错并捕获异常，不存在则直接新增记录
	如果记录存在不插入记录
	如果记录存在不插入记录	insert ignore into 表名(字段1, 字段2,...,字段n) values (值1, 值2,...,值n)	如果插入的数据记录存在就保存旧记录忽略新记录，不存在则直接新增记录
	不论记录是否存在都要插入记录
	不论记录是否存在都要插入记录	replace 表名(字段1, 字段2,...,字段n) values (值1, 值2,...,值n)	如果插入的数据记录存在就先删除再更新，不存在则直接新增记录
	如果记录存在更新指定字段
	如果记录存在更新指定字段	insert into … on duplicate key update	如果插入的数据记录存在就更新指定字段，不存在则直接新增记录

三、实例

students 表 (id表示主键，name是姓名，score是平均成绩)

id	name	score
1	李明	67

（1）常规插入

忽略字段名

insert into 
	students 
values(null, '张三', '74');

执行后结果

id	name	score
1	李明	67
2	张三	74

按照字段插入

insert into 
	students(name) 
values('孙华');

执行后结果

id	name	score
1	李明	67
2	张三	74
3	孙华

一次性插入多条数据

insert into 
	students(name, score) 
values('刘平', '56'),('周雨', '90');

执行后结果

id	name	score
1	李明	67
2	张三	74
3	孙华
4	刘平	56
5	周雨	90

（2）从另一个表导入

students 表 (id表示主键，name是姓名，score是平均成绩)

id	user_name	mobile_phone_number
1	马化腾	13800000000
2	任正非	13800000011
3	马云	13800000022

导出users表的某些数据插入到students表

insert into 
	students(name,score) 
select 
	user_name,
	mobile_phone_number 
from users where id <> 3;

执行结果

id	name	score
1	李明	67
2	张三	74
3	孙华
4	刘平	56
5	周雨	90
6	马化腾	13800000000
7	马云	13800000022

注意：只要对应字段的类型一样，字段不一样也可以导入数据，不会冲突。

（3）插入时数据重复

如果记录存在报错

insert into 
	students 
values(1, '张三', '74');

执行结果: 报错

Duplicate entry '1' for key 'PRIMARY'

如果记录存在不插入记录

insert ignore into 
	students(id,name,score) 
values(1, '张三', '74');

执行结果：不插入不报错

Affected rows:0

id	name	score
1	李明	67
2	张三	74
3	孙华
4	刘平	56
5	周雨	90
6	马化腾	13800000000
7	马云	13800000022

不论记录是否存在都要插入记录

replace 
	students
values(1, '张三', '74');

执行结果

id	name	score
1	张三	74
2	张三	74
3	孙华
4	刘平	56
5	周雨	90
6	马化腾	13800000000
7	马云	13800000022

如果记录存在更新指定字段

insert into 
	students(id)
values(1) on duplicate key 
update 
	name = '李明',
	score = '67';

执行结果

id	name	score
1	李明	67
2	张三	74
3	孙华
4	刘平	56
5	周雨	90
6	马化腾	13800000000
7	马云	13800000022

创建 students 表的代码

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `score` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, '李明', '67');

创建 users 表的代码

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `mobile_phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `users` VALUES (1, '马化腾', '13800000000');
INSERT INTO `users` VALUES (2, '任正非', '13800000011');
INSERT INTO `users` VALUES (3, '马云', '13800000022');

四、注意事项

（1）不写字段名，需要填充自增ID

[使用]：0或者null或者default，自增id默认从1开始。
[使用]：或者没有在自增id中出现的（不重复）数（例如-1，-2），浮点型数据例如3.4，最后显示3，会进行四舍五入。即使定义了int类型，输入‘3’或者浮点型，都会强制转化为int类型，但是输入’a’会报错。具体细节可以看源码。

问题：第一个字段id为什么可以写null？
如果建表的时候写了id为自增id，而写0或者null或者default或者没有在自增id中出现的（不重复）数（例如-1，-2），系统都会自动填充id。如果建表的时候没有写明是自增id，那么主键一定是不能为空的，这个时候写null就会报错。

（2）按字段名填充，可以不录入id

[注意]：字段要与值一一对应。

其余注意事项：

字段名可以省略，默认所有列；
录入值的类型和字段的类型要一致或兼容；
字段和值的个数必须一致。不能出现一行记录5个值，另外一行6个值的情况；
如果写了字段，即使是空值也不能空着，用null代替；

五、应用

（1）简单插入记录

案例来自：SQL110 插入记录（一）

题目描述

牛客后台会记录每个用户的试卷作答记录到exam_record表，现在有两个用户的作答记录详情如下：

用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分；
用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。

试卷作答记录表exam_record中，表已建好，其结构如下，请用一条语句将这两条记录插入表中。

Filed	Type	Null	Key	Extra	Default	Comment
id	int(11)	NO	PRI	auto_increment	(NULL)	自增ID
uid	int(11)	NO			(NULL)	用户ID
exam_id	int(11)	NO			(NULL)	试卷ID
start_time	datetime	NO			(NULL)	开始时间
submit_time	datetime	YES			(NULL)	提交时间
score	tinyint(4)	YES			(NULL)	得分

该题最后会通过执行SELECT uid, exam_id, start_time, submit_time, score FROM exam_record;来对比结果。

【场景】：插入新数据、一次性插入多条

【分类】：插入数据、自增ID

分析思路

难点：

1.如何自动计算时间
insert into table(col1, col2,...,coln) values (value1, value2,...,valuen) 
时间的计算：系统计算，最好不要人工计算。

求解代码

方法一：

写字段名

insert into 
    exam_record(uid, exam_id, start_time, submit_time, score) 
values
(1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(1002, 9002, '2021-09-04 07:01:02', null, null)

方法二：

不写字段名 + 自增id + 人工算时间

insert into 
    exam_record 
values
(null,1001,9001, "2021-09-01 22:11:12","2021-09-01 23:01:12",90),
(null,1002,9002, "2021-09-04 07:01:02",null,null)

方法三：

不写字段名 + 自增id + 系统算时间使用date_add

insert into 
    exam_record
values 
(default,1001,9001,'2021-09-01 22:11:12',date_add('2021-09-01 22:11:12',interval 50 minute) ,90),
(default,1002,9002,'2021-09-04 07:01:02',default ,default)

方法四：

不写字段名 + 自增id + 系统算时间使用 + interval 50 minute

insert into 
    exam_record 
values 
(0,1001,9001,'2021-9-1 22:11:12','2021-9-1 22:11:12' + interval 50 minute,90),
(0,1002,9002,'2021-9-4 7:1:2',null,null)

（2）导出A表的某些数据插入到B表

案例来自：SQL111 插入记录（二）

题目描述

现有一张试卷作答记录表exam_record，结构如下表，其中包含多年来的用户作答试卷记录，由于数据越来越多，维护难度越来越大，需要对数据表内容做精简，历史数据做备份。

表exam_record：

Filed	Type	Null	Key	Extra	Default	Comment
id	int(11)	NO	PRI	auto_increment	(NULL)	自增ID
uid	int(11)	NO			(NULL)	用户ID
exam_id	int(11)	NO			(NULL)	试卷ID
start_time	datetime	NO			(NULL)	开始时间
submit_time	datetime	YES			(NULL)	提交时间
score	tinyint(4)	YES			(NULL)	得分

我们已经创建了一张新表exam_record_before_2021用来备份2021年之前的试题作答记录，结构和exam_record表一致，请将2021年之前的已完成了的试题作答纪录导入到该表。后台会通过执行"SELECT * FROM exam_record_before_2021;"语句来对比结果。

【场景】：导入数据

【分类】：导出A表的某些数据插入到B表、自增ID

分析思路

难点：

1.导出A表的某些数据插入到B表
insert into table1(col1, col2,...,coln) select col1, col2,...,coln from table2

求解代码

方法一：

写字段名

#写字段，不需要插入id
insert into
    exam_record_before_2021(uid,exam_id,start_time,submit_time,score)
select uid,exam_id,start_time,submit_time,score
from exam_record
where year(submit_time) < 2021 
and score is not null

方法二：

不写字段名 + 自增id

#不写字段,自增id，用0或者null代替
insert into 
    exam_record_before_2021
select 0,uid,exam_id,start_time,submit_time,score
from exam_record
where year(submit_time) < 2021 
and score is not null

（3）不论记录是否存在都要插入记录

案例来自：SQL112 插入记录（三）

题目描述

现在有一套ID为9003的高难度SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info（其表结构如下图），不管该ID试卷是否存在，都要插入成功，请尝试插入它。

试题信息表examination_info：

Filed	Type	Null	Key	Extra	Default	Comment
id	int(11)	NO	PRI	auto_increment	(NULL)	自增ID
exam_id	int(11)	NO	UNI		(NULL)	试卷ID
tag	varchar(32)	YES			(NULL)	类别标签
difficulty	varchar(8)	YES			(NULL)	难度
duration	int(11)	NO			(NULL)	时长(分钟数)
release_time	datetime	YES			(NULL)	发布时间

后台会通过执行 SELECT exam_id,tag,difficulty,duration,release_time FROM examination_info 语句来对比结果。

【场景】：不论记录是否存在都要插入记录

【分类】：replace、先delect再insert

分析思路

难点：

1.不论记录是否存在都要插入记录，如果不存在直接新增记录，存在就删除以后重新插入。

方法一、先删除delete再插入insert

[使用]：写2个语句，先执行delete再执行insert

delete from table where ...;insert into table(col1, col2,...,coln) values (value1, value2,...,valuen)

方法二、使用replace或者replace into

replace执行时如果插入的数据记录存在就先删除再更新，不存在则直接新增记录。

replace table(col1, col2,...,coln) values (value1, value2,...,valuen)

求解代码

方法一：

先删除delete再插入insert + 写字段名

delete from 
    examination_info
where exam_id = 9003;
insert into 
    examination_info(exam_id,tag,difficulty,duration,release_time) 
values(9003, 'SQL','hard', 90, '2021-01-01 00:00:00')

方法二：

使用replace + 不写字段名

replace
    examination_info
values(default, 9003, 'SQL','hard', 90, '2021-01-01 00:00:00')

AtomGit 开源协作平台测评赛

瓜分20万奖金获得内推名额丰厚实物奖励易参与易上手

更多推荐

【Spring Boot 】Spring Boot + HikariCP 连接池使用示例

文章目录示例工具版本HikariCP 依赖HikariCP 配置1. connectionTimeout2. minimumIdle3. maximumPoolSize4. idleTimeout5. maxLifetime6. autoCommitSpring Boot Data + HikariCP + MySQL示例测试应用程序1. 使用 Maven 命令2. 使用 Eclipse3. 使用