一、主键冲突
1、在有的表中,使用的是业务主键(字段有业务含义)。但在数据插入的时候,不确定数据表中是否已经存在对应的主键。假设主键对应的值已经存在,插入一定会失败
2、当主键存在冲突的时候,可以选择性的进行处理:更新和替换
3、主键冲突:更新
(1)基本语法
-- 主键冲突:更新
-- 主键冲突时,新值是update后面设置的值,与前面的值列表无关
-- 主键没有冲突时,新值是前面值列表的值,update没有使用到
insert into 表名 [(字段列表,包含主键)] values (值列表) on duplicate key update 字段1=值1, 字段2=值2, ... ;
-- 创建表
create table my_class(
name varchar(20) primary key,
room varchar(20),
number int
)charset utf8;
-- 查看表结构
desc my_class;
-- 插入数据
insert into my_class values ('JAVA001', 'B201', 10);
-- 查看数据
select * from my_class;
-- 再次插入数据,name还是'JAVA001'
-- 因为name是主键,值非空且唯一,所以数据插入失败
insert into my_class values ('JAVA001', 'B321', 20);
-- 主键冲突:更新
-- 插入成功(提示有2行受影响)。新值是update后面设置的值,与前面的值列表无关
insert into my_class values ('JAVA001', 'B321', 20) on duplicate key update room = 'B501', number = 30;
-- 查看数据
-- 更新之后的值为:name='JAVA001', room='B501', number=30
select * from my_class;
注:使用更新解决主键冲突问题,提示有2行受影响
(2)弊端:如果更新的字段较多,update后面要写很多字段,费劲
4、主键冲突:替换
(1)基本语法
-- 主键冲突:替换
-- 如果字段列表中不包含某些字段,则插入后再次查询,不包含的数据为空
-- 相当于删除了主键冲突的那行数据,再插入一行(主键相同)
replace into 表名 [(字段列表,包含主键)] values (值列表);
-- 创建表
create table my_class(
name varchar(20) primary key,
room varchar(20),
number int
)charset utf8;
-- 查看表结构
desc my_class;
-- 插入数据
insert into my_class values ('JAVA001', 'B201', 10);
-- 查看数据
select * from my_class;
-- 再次插入数据,name还是'JAVA001'
-- 因为name是主键,值非空且唯一,所以数据插入失败
insert into my_class values ('JAVA001', 'B321', 20);
-- 主键冲突:替换
-- 提示有2行受影响。先删除影响了一行,再插入又影响了一行
replace into my_class (name, room) values ('JAVA001', 'B405');
-- 查看数据
-- 如果字段列表中不包含某些字段,则插入后再次查询,不包含的数据为空
-- 更新之后的值为:name='JAVA001', room='B405', number=null
select * from my_class;
注:使用替换解决主键冲突问题,提示有2行受影响。先删除影响了一行,再插入又影响了一行
(2)insert的效率比replace的效率高。所以,如果没有主键冲突,插入数据时,要使用insert
注:replace要先判断是否存在,如果存在,还要先删除再插入,效率较低。尤其是数据量大的时候,replace可以解决问题,但使用replace是个非常不好的解决方案
二、蠕虫复制
1、蠕虫复制:从已有的数据中获取数据,然后将数据进行新增操作。结果是数据成倍的增加
注:蠕虫复制是分裂增长(爆发式增长)
2、从表中复制数据不好复制,可以先从已有的表创建一个新表(create table 新表名 like 数据库名.表名),拿到表结构后,用蠕虫复制获得对应的数据。这样就可以实现简单的通过2条sql指令就可以把一个表进行真实的数据复制。一般情况下,可以从生产环境复制到自己的开发环境中,来测试新的代码
3、表创建的高级操作:从已有表创建新表(复制表结构)-- 只复制结构,不会复制数据
-- 表创建的高级操作:从已有表创建新表(复制表结构)
-- 如果在同一个数据库中,可以直接使用表名,省略"数据库名."
create table 新表名 like 数据库名.表名;
-- 创建表
create table my_gbk(
name varchar(32766)
)charset gbk;
-- 查看表结构
desc my_gbk;
-- 插入数据
insert into my_gbk values ('a'), ('A'), ('B'), ('b');
-- 复制创建表(只复制结构,不会复制数据)
create table my_copy like my_gbk;
-- 查看表结构
desc my_copy;
4、蠕虫复制:先查出数据,然后将查出的数据新增一遍