MySQL使用之三_GROUP_CONCAT、INSTR、JOIN

想要实现的需求：
1.blog、blog_with_tag、tag三张表，blog与tag为多对多关系，blog_with_tag为中间表。
2.blog主键为mid，tag主键为tid，blog_with_tag中进行mid、tid映射。
3.tag中tag字段为具体内容。
需求：在blog中添加tags字段，用于存储blog对应的所有tag具体内容；

一 GROUP_CONCAT

多行合并GROUP_CONCAT：https://www.cnblogs.com/zelzzz/p/7496884.html

GROUP_CONCAT可将多行数据拼接，找到blog对应的所有blog_with_tag中的记录后，可将这些记录值合并拼接。

以将blog对应的blog_with_tag中记录的tid合并为例：

SET group_concat_max_len=102400; 

UPDATE blog
SET blog.tags = (
	SELECT t.tags		// 多余，可精简；详见后文
	FROM (
		SELECT blog_with_tag.mid, GROUP_CONCAT(blog_with_tag.tid SEPARATOR '|') AS tags
		FROM blog_with_tag
		GROUP BY blog_with_tag.mid
	) t // 查询得到单个值（目标值）的table
	WHERE blog.mid = t.mid
); // set的赋值必须是一个值，而不是一个table；当select查询返回的是只有一个值的table，如果用括号框起来就是一个值了，也即：将一个值的table转为单个值

注意： 当GROUP_CONCAT拼接后字段数据超过默认最大长度会报错，可如上手动设置最大长度。

二 基于INSTR实现

注意： 实际上在此使用INSTR在逻辑上就是错误的，以下是为了记录解决过程和熟悉语法。

上述代码只在blog和blog_with_tag两张表之间的操作，只得到了blog对应的所有tid拼接成的tids，需要进一步结合tag表由tids得到具体tag内容。

INSTR使用教程：https://blog.csdn.net/qq_35548288/article/details/81771978

一个可能的思路是，判断tag中记录的tid是否在tids中，筛选出记录的tag值。问题在于：tag.tid是int类型，而tids是string类型，没法用in判断。发现上述教程，说是可以通过INSTR实现。

2.1 INSTR介绍

INSTR(str,substr)：用于判断是否为子串

语法：
SELECT XXX
FROM ()t
WHERE INSTR((...),t.XXX)

2.2 INSTR实现

注意： 这里只是为了学习SQL语法；这里使用INSTR本身是错误的且执行效率极低，执行20h未出结果；

 版本一：
 
 UPDATE blog
 SET blog.tags = (
  SELECT t.tags		// 多余，可精简；
  FROM (
    SELECT GROUP_CONCAT(tag.tag SEPARATOR '|') as tags
  	FROM tag
    WHERE INSTR((
    	SELECT t.tags // 多余，可精简；
 	  	FROM (
 	     	SELECT blog_with_tag.mid, GROUP_CONCAT(blog_with_tag.tid) AS tags
 	    	FROM blog_with_tag
 	    	GROUP BY blog_with_tag.mid
	 	)t1
	  	WHERE blog.mid = t.mid),
	  tag.tid)
  )t
);

如注释所说，两步SELECT多余，可以直接获取GROUP_CONCAT的值，不需要先得到表后再获取字段值，即同时包含WHERE、GROUP BY子句。

版本二：

 UPDATE blog
 SET blog.tags = (
    SELECT GROUP_CONCAT(tag.tag SEPARATOR '|') as tags
  	FROM tag
    WHERE INSTR((
 	    SELECT blog_with_tag.mid, GROUP_CONCAT(blog_with_tag.tid) AS tags
 	    FROM blog_with_tag
 	    GROUP BY blog_with_tag.mid
	  	WHERE blog.mid = t.mid),
	  tag.tid)
  )t
);

进一步地，由以下教程可知WHERE在GROUP BY前面执行，在这里使用WHERE筛选后不需要再使用GROUP BY，WHERE的结果作为一整组即可，如下代码所示：

执行顺序： https://blog.csdn.net/menghuanzhiming/article/details/79886083

版本三：执行20h未出结果

UPDATE blog
SET blog.tags = (
 SELECT GROUP_CONCAT(tag.tag SEPARATOR '|')
 FROM tag
 WHERE INSTR((
 	SELECT GROUP_CONCAT(blog_with_tag.tid)
 	FROM blog_with_tag
 	WHERE blog.mid = blog_with_tag.mid
 ), tag.tid)
);

二 INSTR导致的错误

按照上面INSTR的使用方式，存在如下错误：

INSTR(str,substr)

SELECT COUNT(root.mid) FROM root  WHERE INSTR('123,456',123); // 98173

SELECT COUNT(root.mid) FROM root  WHERE INSTR('123,456',12);  // 98173

可见，INSTR是先将Int转为str后再判断，所以认为12在'123,456'内

其实需求为典型的联表查询，用JOIN即可实现。

三 基于JOIN实现

在一个表中找出字段值等于另一个表中某字段值的记录，该需求是典型的联表查询，使用JOIN可实现。

UPDATE blog
SET blog.tags = (
 SELECT t.tags 
 FROM (
 	SELECT GROUP_CONCAT(t1.tag) AS tags,t1.mid 
 	FROM (
 		SELECT blog_with_tag.mid,blog_with_tag.tid,tag.tag
 		FROM blog_with_tag
 		RIGHT JOIN tag
 		ON blog_with_tag.tid = tag.tid
 	)t1 
 	GROUP BY t1.mid
 )t 
 WHERE blog.mid = t.mid
);

以上代码又是先得到GROUP_CONCAT中间表后再SELECT字段，可优化如下：

blog表70w条记录，更新tags值9w条记录，耗时42s

UPDATE blog
SET blog.tags=(
	SELECT GROUP_CONCAT(t.tag SEPARATOR '|') 
	FROM (
		SELECT blog_with_tag.mid,tag.tag
		FROM blog_with_tag
		RIGHT JOIN tag
		ON blog_with_tag.tid = tag.tid
	)t 
	WHERE blog.mid = t.mid 
	GROUP BY t.mid
);

补充：
JOIN联表查询应用场景：以其他表多行的值为条件
A表、B表中均有name字段，要从A表选出在B表中存在的name值。也即选出A、B表中同时存在的name值，然而MySQL中没有判断存在的函数。可通过联表查询实现。

SELECT A.name AS A_name,B.name AS B_name
FROM A
RIGHT JOIN B
ON A.name = B.name;

若B表为中间表

SELECT A.name AS A_name,B.name AS B_name
FROM A
RIGHT JOIN (SELECT ...)B
ON A.name = B.name;

典型情况是：A表为多对多中间表，B表为含主键的表。此时，将A、B表拼成一个表。

四 收获

解决上述问题虽然绕了不少弯路，但对SQL语法有了更深理解，总结如下：

1. SQL语句的写法：先按照如下模板开头，然后再详细写中间表的内容。另外，()和别名t之间不需要空格；
2. SELECT嵌套时，分析清楚后，应从外层往里层写，而且从外往里依次将中间表命名为t、t1…；
3. SELECT嵌套时，外层表的字段可在内层中直接使用；

模板一：
SELECT XXX
FROM  ()t
WHERE XXX

模板二：
UPDATE XXX
SET XXX.XXX=(

);