高级sql查询

高级sql

• 搜集一些业务中不常用，但是偶尔需要的sql，这些sql满足你1次IO请求数据库交互

sql-逻辑判断if-else

有时候我们有一个业务A表（假设内容表content），但是随着业务后期的扩张，允许1:n 一对多的关联，
A可以关联商品good、关联视频video、关联代金劵coupon…。而且在前期的业务发展中，这些实体（good、video、coupon）是无相关性的。即他们的表设计已经是定型的了。
那我现在有需求：我要实现 A表 能关联上面所有的实体怎么做？
初级想法：我们在业务A表上加字段，bind_type 和 bind_id；这样的话1:1是可以的，如果是1:n这样的关系呢？再加字段？bind_good 和 good_id; bind_video 和video_id等等等，那这样的话，每次你都需要去动A表结构，而且还要同步改业务增删改查。
所以我们加中间表：content_bind 表
表设计：

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content_id` int(11) NOT NULL COMMENT '内容id',
  `bind_type` smallint(6) NOT NULL COMMENT '绑定类型',
  `bind_id` int(11) NOT NULL COMMENT '绑定的id',

OK我们的关联表设计完成了；接下来进入正题，如何一条sql查处内容以及他关联的实体

多次IO查询

我们分析下：
首先：中间表有bind_type字段，我们要根据这个字段的value 去查询不同的表, 可能我们会这样做：

左连接查询
SELECT * FROM content c LEFT JOIN content_bind c_b
on c.id = c_b.content_id
WHERE xxxx过滤条件 ORDER BY xx LIMIT M N

然后：我们过滤出一页的数据了，然后业务中循环，根据bind_type 去获取不同表中的数据拼成list返回给前端
你想下复杂程度：如果我这一页数据中，有的关联了 video, 有的关联了good, 有的关联了coupon,怎么办呢？你只能循环去判断然后去不同的表拿数据，最坏情况下：你需要和db进行10次IO请求才能处理完这个业务。再加上上面的分页：11次请求。这还只是一页10条数据，如果是50条呢？最坏51次sql查询。

一次IO查询

	SELECT * 
		CASE b.goods_type
			WHEN 1 THEN
				(SELECT g.good_name from good g WHERE c_b.bind_id = g.id)
			WHEN 2 THEN
				(SELECT v.title from video v WHERE c_b.bind_id = v.id)
			WHEN 3 THEN 
				(SELECT c.name from coupon c WHERE c_b.bind_id = c.id)
			ELSE NULL
		END bind_name
	FROM content ct 
	LEFT JOIN content_bind c_b ON ct.id = c_b.content_id
	WHERE xxx
	ORDER BY xx
	LIMIT M N

这样是不是很舒服，一次IO，就能查询我们想要的

group_contact

链接字符串，配合group查询，返回, 分割好的字符串

	select group_concat(`title`) as tuple from cs_content group by content_type
	// 标题,标题,标题,标题,标题,标题,标题,标题

JSON提取

假如我有个字段叫做：activity_condition, 里边存的json格式，那我们想查询里边的key="xx"的数据怎么做？
{"content_forwarded_count":1,"content_viewed_count":0}

	select * from cs_video_redpack_config where activity_condition -> "$.content_forwarded_count" = 1

查询这张表，只要activity_condition 里边的某个key=content_forwarded_count的值

// 查询
1. select JSON_EXTRACT(activity_condition, "$.content_forwarded_count") as content_forwarded_count from cs_video_redpack_config

2. select activity_condition -> "$.content_forwarded_count" as content_forwarded_count from cs_video_redpack_config

更改json里的data

//删除
UPDATE cs_video_redpack_config set activity_condition = json_remove(activity_condition,'$.content_forwarded_count') WHERE id = 45 

插入json新的key, 如果这个key已经存在了，则不会改变json

//插入
UPDATE cs_video_redpack_config set activity_condition = json_insert(activity_condition,'$.content_forwarded_count','111') WHERE id = 45 

修改,支持一次修改多个

UPDATE cs_video_redpack_config set activity_condition = json_set(activity_condition,'$.content_forwarded_count',456,'$.content_viewed_count',1) WHERE id = 45