mysql的连表查询之多对一

需求A表的label_ids是B表的主键id，查询A表的时候关联B并查出对应的name

实现方案：

-- 1、json实现（索引在8.X之后才生效）
SELECT
	group_concat( t.label_name ) AS labelName,t.*,date_format( t.create_date, '%Y-%m-%d' ) AS groupDate 
FROM
	(
	SELECT 
		t1.*,t2.label_name 
	FROM
		miniapp_note t1
		LEFT JOIN miniapp_label t2 
		ON json_contains(concat( '[', label_ids, ']' ),concat( t2.id, '' ),'$' ) 
	WHERE
		t1.create_by = 1 
		AND t1.is_enable = 1 
	ORDER BY
		t1.create_date DESC 
	) t 
GROUP BY t.id


-- 2、FIND_IN_SET实现（走索引）-推荐
SELECT
			t1.*,
			(
				SELECT
					GROUP_CONCAT(t.label_name)
				FROM
					note_label t
				WHERE
					t.create_by =1
					and t.is_deleted =1
				and FIND_IN_SET(t.id,t1.label_ids)
			) as labelName,
			date_format(t1.create_date,'%Y-%m-%d') as groupDate
		FROM
			note_calendar t1
		WHERE
			t1.create_by = 1
		and t1.is_deleted =1
		and t1.content like concat ('%','漂亮','%' ) 
	  having labelName like concat ('%','漂亮','%' )

-- 3、INSTR实现（走索引）-推荐
SELECT
	GROUP_CONCAT( t.label_name ),t.id,t.content,t.label_ids 
FROM
	(
	SELECT t1.*,t2.label_name 
	FROM
		note_calendar t1,
		note_label t2 
	WHERE
		INSTR( t1.label_ids, t2.id ) > 0 
	) t 
WHERE
	(t.content LIKE '%test%' OR t.label_name LIKE '%test%' ) 
GROUP BY t.id

-- 4、concat拼接实现
SELECT * FROM A , B where concat(',',ids,',') like concat('%,',id,',%')

语法解释：

1、FIND_IN_SET(str,strlist)

str要查询的字符串

strlist 要以,分割 eg：(1,2,3,4,5)

 in和find_in_set的区别：

如果集合是常量用 in()、find_in_set()，如果集合是变量用 find_in_set()

like和find_in_set的区别：

like是广泛的模糊查询、find_in_set()是精准匹配

2、instr()

mysql的内置函数instr(str,substr)，作用是获取子串第一次出现的位置，如果没找到则返回0

str：源字符串

substr：指定的字符/目标字符串