MySQL数据新增

高级SQL操作：利用SQL指令的变化实现一些复杂业务的数据操作。

一、数据新增

1、批量插入

目标：了解批量插入的语法，掌握批量插入的应用
批量插入：是一种优化数据逐条插入的方式
批量插入数据的语法与简单数据插入的语法差不多
批量插入分为两种

1. 全字段批量插入
   insert into 表名 values(值列表1),(值列表2),...(值列表N);
2. 部分字段批量插入（注意字段默认值）
   insert into 表名 (字段列表) values (值列表1),(值列表2),...(值列表N);
   小结
   1、批量插入可以针对性解决数据的批量导入之类的业务
   2、批量插入可以一次性解决多条数据插入，能够有效降低客户端占用问题，提升数据操作效率

• MySQL8以后默认有事务安全，即批量要么都成功要么都失败，不会出现部分问题
  eg：

--全字段批量插入
insert into table_A values('Tom','Computer',90),
('Lily','Computer',92);

--部分字段批量插入
insert into table_A (stu_name,course) values('Tony','English'),('Ray','Math');

2、蠕虫复制

目标：了解蠕虫复制的语法和原理，能够利用蠕虫复制实现数据的快速增长。
蠕虫复制：从已有表中复制数据直接插入到另外一张表（同一张表）
蠕虫复制的目标是快速增加表中的数据

• 实现表中数据复制（用于数据备份或者迁移）
• 实现数据的指数级递增（多用于测试）

蠕虫复制语法

insert into 表名 [(字段列表)] select 字段列表 from 表名;

注意事项

• 字段列表必须对应上
• 字段类型必须匹配上
• 数据冲突需要事先考虑

--蠕虫复制A表数据到B表
insert into table_B select * from table_A;  

 ---选择字段时，注意匹配
insert into stu_B (stu_name,course,score) select stu_name,course,score from stu_A; 

3、主键冲突

目标：了解主键冲突的原理，掌握主键冲突的解决方案

概念
主键冲突：在数据进行插入时包含主键指定，而主键在数据表已经存在

• 主键冲突的业务通常是发生在业务主键上（业务主键本身有业务意义）
• 主键冲突的解决方案

1、忽略冲突：保留原始记录

 insert ignore into 表名 [(字段列表)] values(值列表);

eg：

-- username为  primary key,先插入数据
insert into t_36 values('username','password',12345678);

-- 当发生冲突时，冲突忽略，不能覆盖
insert ignore into t_36 values('username','12345678',12345678);

2、冲突更新：冲突后部分字段变成更新

insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值[,字段=新值...];

eg：

-- username为  primary key,先插入数据
insert into t_37 values('username',12345678);	# 当前时间戳

# 冲突更新（替换部分字段数据）
insert into t_37 values('username',12345678) on duplicate key update logintime = unix_timestamp();	# 当前时间戳.

如果主键不冲突：新增
如果主键冲突：更新指定字段
上述方式适用于字段较多，但是可能冲突时数据变化的字段较少

3、冲突替换：先删除原有记录，后新增记录

--效率没有insert高（需要检查是否冲突）
replace into 表名 [(字段列表)] values(值列表); 

eg：

-- username为  primary key,先插入数据
insert into t_38 values('username',unix_timestamp）;
# 替换插入
replace into t_38 values('username',unix_timestamp());

replace遇到主键重复就会先删除、后新增
如果有较多字段需要更新：建议使用替换

4、小结

1、主键冲突的解决方案有三种，但是需要根据具体的业务来选择合适的方式

• 忽略新数据：insert ignore
• 更新部分数据：insert ... on duplicate key update
• 全部替换：replace into
  2、从效率上来讲，insert into不考虑冲突的效率最高，三种解决冲突的方式都会有效率下降（需要检索），其中三种本身的效率依次是：忽略新数据 > 更新部分数据 > 替换全部