MySQL实现数据插入操作的示例详解

吾爱主题阅读：173 2023-02-22 15:07:00 评论：0

使用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）常规插入

忽略字段名

执行后结果

id	name	score
1	李明	67
2	张三	74

按照字段插入

1 2 3 insert into students( name ) values ( '孙华' );

执行后结果

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

一次性插入多条数据

1 2 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表

1 2 3 4 5 6 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）插入时数据重复

如果记录存在报错

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

执行结果: 报错

Duplicate entry '1' for key 'PRIMARY'

如果记录存在不插入记录

1 2 3 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

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

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

执行结果

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

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

1 2 3 4 5 6 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 表的代码

1 2 3 4 5 6 7 8 9 10 11 12 13 14 -- ---------------------------- -- 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 表的代码

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 -- ---------------------------- -- 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' );