mysql拆分字符串做条件查询 - Jame!

2 years ago

source link: https://www.cnblogs.com/sunankang/p/16445918.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

mysql拆分字符串作为查询条件

有个群友问一个问题

这表的ancestors列存放的是所有的祖先节点,以,分隔

例如我查询dept_id为103的所有祖先节点,现在我只有一个dept_id该怎么查

然后我去网上找到这样一个神奇的sql,改改表名就成了下面的这样

sql

	SELECT
		substring_index( substring_index( a.ancestors, ',', b.help_topic_id + 1 ), ',',- 1 ) AS shareholder 
	FROM
		sys_dept a
		JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.ancestors ) - length( REPLACE ( a.ancestors, ',', '' ) ) + 1 ) 
	WHERE
	dept_id = 103

嗯,没错结果出来了,然后我就很好奇,什么原理,一个个来看

mysql.help_topic

这个是一个mysql自带的帮助解释注释表,查询结果如下

id从0开始,我这个版本最大id到584,版本不同应该id最大值也不一样,这个表的作用一会说

REPLACE

这个函数应该都知道吧,替换字符用的

LENGHT

获取字符串的长度

substring_index

查分字符串,三个参数,要拆分的字符串,根据拆分的字符,从第几个开始

如果最后的那个参数为正数则从左开始数,然后获取对应下标左边的所有字符

如果为负数,则从右边开始数,获取对应下标右边的所有字符串,这个就不演示了

先来看第一段

sql

( length( a.ancestors ) - length( REPLACE ( a.ancestors, ',', '' ) ) + 1 )

我们假设当前数据的ancestors值为 0,100,101 那么第一个length(a.ancestors)的值就是9 减去后面的一段

length( REPLACE ( a.ancestors, ',', '' ) ) 因为我们假设的值里面有两个 , 所以length为7 最后在加1 那么这段值为 3

和前面的 join on条件能查出的数据也就是mysql.help_topic这个表中所有id小于3的数据,也就是id为0,1,2的三条数据

那么现在先来看看这样查询的结果是啥

那么我们假设现在是第一行,mysql.help_topic表中的help_topic_id为0

sql

substring_index( substring_index( a.ancestors, ',', b.help_topic_id + 1 ), ',',- 1 )

最里面的substring_index 拆分后为0,因为没有能在拆分的了所以外面的substring_index返回的也是0

第二行help_topic_id为1的时候获取结果为0,100

然后执行外层的substring_index 根据,拆分,值为-1 所以从右边找一位,获取的值就是100

第三行结果为0,100,101,外层substring_index 执行后结果为101

....只能心中大喊牛逼

那么现在知道mysql.help_topic这个表的作用了吗?就是用来对拆分出的数据分行,专业点叫笛卡尔积 (真的不懂..)

这种方法也有缺点:就是拆出的行数不能大于mysql.help_topic这个表的数据条数

__EOF__

本文作者： Jame 本文链接： https://www.cnblogs.com/sunankang/p/16445918.html 关于博主： 评论和私信会在第一时间回复。或者直接私信我。 版权声明： 本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！ 声援博主： 如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。

Recommend