01-union,不存在则插入,批量更新快捷方式
1.union
定义:union操作符用于连接两个以上的select语句的结果组合到一个结果集中。默认:多个select语句会删除重复的数据
1. 同join on 区别:
- join on:用行关联,合并结果列数(在两个表或多个表中查询数据,用来关联表)
- union:用列关联,合并结果集的条数
2. 案例
-- 表结构
CREATE TABLE `user_0` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`user_sex` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`user_name`,`user_sex`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;
-- union案例
SELECT
user_name ,
user_sex
FROM
`user_1`
WHERE
user_name = "aaa" UNION
SELECT
user_name,
user_sex
FROM
user_0
WHERE
user_sex =0
场景举例:可用于页面展示时需要用到不同表数据的内容的情况
注意
1. UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家,每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值!
2. union展示的列是按照第一个select语句的查询列展示
3. union关联的几个select语句的查询字段名可以不同,但是对应要聚合的字段类型必须一样
4. union关联的几个select语句的查询列必须一样多,且查询的字段的顺序均一致。
2. 不存在则插入
2.1 使用union和insert实现不存在则插入
2.2 脚本案例:
-- 不存在则插入
INSERT into user_0 (user_name, user_sex)
(
SELECT "aaa", 1 from user_0
where not EXISTS (SELECT id from user_0 where user_name = "aaa" limit 1)
)
union
(
SELECT "测试0", 0 from user_0
where not exists (SELECT id from user_0 where user_name="测试0" LIMIT 1)
)
2.3 脚本解析:
- 条件:
SELECT "测试0", 0 from user_0 where not exists (SELECT id from user_0 where user_name="测试0" LIMIT 1)
的查询字段是常量,已知的要进行不存在则插入操作的数据值。 SELECT id from user_0 where user_name="测试0" LIMIT 1
:用于筛选要插入的数据是否存在的查询子句,通过where 和limit进行数据限制,如唯一限制;本案例中使用的是唯一条件并限制仅展示一条存在即可limit 1,实际也可以使用其他查询条件,只要该select子句的结果有数据结合exists或者not exists即可得到待判断数据是否在结果集中,从而判断该数据是否需要插入。EXISTS
关键字后跟着的语句不会返回具体的数据,而是返回true或false,如果外层sql的字段在子查询中存在则返回true,该行数据就可以作为外部条件的查询结果,否则返回false;not exists
与exists
相反,如果not exists
后子查询的查询结果不存在则整个not exists
子句返回true
所以上述如果子查询SELECT id from user_0 where user_name = "aaa" limit 1
没有找到目标数据,则not exists (SELECT id from user_0 where user_name = "aaa" limit 1)
的结果即为trueSELECT "测试0", 0 from user_0
:查询到的所有行的值都是给定数据
PS:从效率上来说,select 常量 from table > select xxx > select * ,因为使用常量不用查字典表SELECT "测试0", 0 from user_0 where not exists (SELECT id from user_0 where user_name="测试0" LIMIT 1)
综上,该子句等价于SELECT "测试0", 0 from user_0 where true
结果即为查询目标常量值:"测试0", 0
;注意这一子句的查询结果的记录数等于表的行数,即:表user_0
有几行记录就查询到几条"测试0", 0
数据union
合并多个select子句的查询结果集,并过滤重复数据,得到所有需要插入的数据集- 最后执行
INSERT into user_0 (user_name, user_sex) .....
做插入,等价于insert into table_name(xx,xxx....) select xx,xxx.... from ...
优势:- 性能好,实现简单
缺点:
1.要插入数据的表不能是空表,表中必须有数据才行(这个有点致命);如果待插入的表中没有数据则该方案数据插不进去,并且相当耗时。
2.性能不稳定,波动较大:在测试1000条记录的耗时中:分别有耗时11367,12300 ,1139 ,1218的耗时体现,差距较大;
- 性能好,实现简单
2.4. 其他方案
2.4.1 方案一
先查询目标数据是否存在,再判断是否需要插入。
default void save(List<T> elements) {
if (!CollectionUtils.isEmpty(elements)) {
List<List<T>> group = ListUtil.group(elements, 500);
group.forEach(list->{
});
ListUtil.group(elements, 500)
.forEach(v -> {
int rows = this.batchUpdate(v);
if (rows < 1) {
this.