当使用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代替；