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

时间:2023-03-20 酸菜鱼土豆大侠人气: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

按照字段插入

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代替；

加载全部内容