一. 概述
- 总结
SQL语句 | 含义 | 数据库中已存在 | 数据库中不存在 | 结果 | 举例 |
---|
insert into | 插入新数据 | 报错 | 插入 | 主键自增 | insert into names(name, age) values(“小明”, 23); |
insert ignore into | 忽略老数据 | 忽略 | 插入 | 主键自增 | insert ignore into names(name, age) values(“小明”, 24); |
replace into | 替换老数据 | 替换 | 插入 | 主键自增 | replace into names(name, age) values(“小明”, 25); |
- 插入语句总结
SQL语句 | 场景 | 执行结果 | 影响行数 | 主键是否自增 | 示例 |
---|
insert into | 唯一索引重复 | ERROR | - | 主键自增 | insert into names(name,age) values(‘dadadda’,12); |
insert into | 主键重复 | ERROR | - | 主键不自增 | insert into names(id,name,age) values(15,‘hihdadasdadih’,12); |
insert ignore into | 唯一索引重复 | WARNING | 0行受影响 | 主键自增 | insert ignore into names(name,age) values(‘dadadda’,12); |
insert ignore into | 主键重复 | WARNING | 0行受影响 | 主键不自增 | insert ignore into names(id,name,age) values(14,‘dajsldjal’,12); |
replace into | 唯一索引重复 | 执行成功 | 2行受影响 | 删除旧记录,新记录 | replace into names(name, age) values(“xiaoxue”, 24); |
replace into | 主键重复 | 执行成功 | 2行受影响 | 直接替换该行记录,id不会改变 | replace into names(id,name, age) values(22,“dasdasdsa”, 28); |
replace into | 主键和唯一索引都不重复 | 执行成功 | 1行受影响 | 与新增记录一样 | replace into names(name, age) values(“好嗲速递哦啊睡觉的”, 29); |
- 更新语句总结
SQL语句 | 场景 | 执行结果 | 影响行数 | 主键是否自增 | 示例 |
---|
update | 旧数据和要更新的值完全一样 | 执行成功 | 0行受影响 | - | update names set name=‘dasd’ where id=12; |
update | 没有找到该条数据 | 执行成功 | 0行受影响 | - | update names set name=‘dasd’ where id=313421; |
update | 修改的唯一索引字段已有值 | ERROR | - | - | update names set name=‘qaa’ where id=4; |
- 删除语句总结
SQL语句 | 场景 | 执行结果 | 影响行数 | 主键是否自增 | 示例 |
---|
delete | 正常情况 | 执行成功 | 1行受影响 | - | delete from names where name=‘333’; |
delete | 没有该条记录 | 执行成功 | 0行受影响 | - | delete from names where name=‘3333131’; |
- 创建测试表并插入初始数据
CREATE TABLE names(
id INT(10) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) UNIQUE,
age INT(10)
);
select * from names;
insert into names(name, age) values("xixi", 24);
insert into names(name, age) values("houhou", 25);
insert into names(name, age) values("maomi", 26);
insert into names(name, age) values("liuxing", 27);
- 查询结果
二. 详解
1. insert into
- 概述
- 表示插入新数据。
- 如果出现重复唯一索引值,id会自增,但插入不成功,会报错。
- 如果出现重复主键,id不会自增,则插入不成功,报错。
- 示例
insert into names(name, age) values("xixi", 24);
- 结果:
Error Code: 1062. Duplicate entry 'xixi' for key 'name'
2. insert ignore into
- 概述
- 表示忽略老数据。
- 如果新数据与数据库已有数据的唯一索引重复,则忽略当前新数据;否则,效果和insert into一样。
- 如果新数据与数据库已有数据的主键重复,则忽略当前新数据。
- 要求表中必须有主键或唯一索引;否则,则会插入新数据。
- 示例
# 已存在:忽略新记录,id自增,不会报错
insert ignore into names(name, age) values("xixi", 24); # id+1
# 不存在:添加新记录
insert into names(name, age) values("xiaoxue", 24);
- 结果
3. replace into
- 概述
- 表示替换老数据。REPLACE = DELETE和INSERT,即删除旧记录,新增新记录
- 如果新数据与数据库已有数据的主键或唯一索引重复,则用新数据替换;否则,效果和insert into一样。
- 要求表中必须有主键或唯一索引;否则,则会插入新数据。
- 执行REPLACE后,系统返回了所影响的行数:
- 如果返回值等于1,说明表中没有重复记录;
- 如果返回值等于2,说明有一条重复记录;
- 如果返回值大于2,说明有多个唯一索引,有多条记录被删除和插入。
- 示例
replace into names(name, age) values("xiaoxue", 24);
replace into names(name, age) values("xiaoyu", 29);
- 结果
- ⚠️多个唯一索引的情况
-
表结构
-
初始查询
select * from test.tx;
replace into test.tx(c1,c2) values(13,25);
- 结果:删除了id=37和id=38的两条记录,插入id=41的一条记录。可以看出,有多个唯一索引的情况,可能会用一条记录替换多条记录。
参考资料