mysql.help_topic：join后面on居然还可以用 ＜ 连接实现列转行

引言(遇到问题)

今天写代码，看到了如下SQL，直接怀疑他是不是语法错了，on后面不都是等于号吗？

SELECT
    a.bname,
    SUBSTRING_INDEX( SUBSTRING_INDEX( a.bname, ',', b.help_topic_id + 1 ), ',', - 1 ) AS new_book_name 
FROM
    book a
    JOIN mysql.help_topic b ON b.help_topic_id < (
        LENGTH( a.bname ) - LENGTH(
        REPLACE ( a.bname, ',', '' )) + 1 
    )
1
2
3
4
5
6
7
8
9

原来是MySQL的特殊用法!!!

真实作用

为搞懂这种写法的真实目的，首先介绍这个sql的真实作用：

有些时候，为了减少其他字段数据冗余或是一些业务等其他原因，我们可能会将某个特征多条数据合并存为一条数据进行存储，如下图中bname字段这种用法违反第一范式的设计模式。 这种模式下，应用常常需要将这个列依据分隔符进行分割，如用“，”将其分割为多行数据，并得到列转行的结果。

解析

如果只是想将表中的某些行转为多行的操作，那么直接复制上面的sql代码稍加修改就可以实现了，但是，如果想搞懂这个sql的实现原理，还要逐行解析：

1. mysql.help_topic

help_topic是mysql内部属性表，我们这里用这个属性表的目的就是利用这个表中自增id help_topic_id这一字段，由于我们需要将一行转为多行，那么就需要这么一个自增序列字段，help_topic_id共有数百个连续的id，我mysql这个版本共有505个从0开始的连续id，id最大值大于符合分割value值的个数,能满足于大部分需求了。

2. LENGTH( a.bname)

LENGTH函数用于返回字符串的字节长度，长度单位为字节。
使用uft8编码字符集时，一个汉字是3个字节，一个数字或字母是一个字节

3. JOIN

这里JOIN用的是笛卡尔积，由于我们的目的是让一行数据转换为多行数据，那么这里就利用help_topic内部表的行数来实现，在两个表联表查询的时候，让b.help_topic_id小于要转换的行数。

        LENGTH( a.bname ) - LENGTH(
        REPLACE ( a.bname, ',', '' )) + 1 
1
2

这里实现的就是统计当前内容中包含了多少数据，即未来要转换的行数

4. SUBSTRING_INDEX

MySQL中一个很好用的截取字符串的函数：substring_index。

• 用法规则：
  substring_index（“待截取有用部分的字符串”，“截取数据依据的字符”，截取字符的位置N）

• 参数说明

  • string：用于截取目标字符串的字符串。可为字段，表达式等。
  • sep：分隔符，string存在且用于分割的字符，比如“，”、“.”等。
  • num：序号，为非0整数。若为整数则表示从左到右数，若为负数则从右到左数。比如“www.mysql.com”截取字符‘www’，分割符为“.”，从左到右序号为1，即substring_index(“www.mysql.com”,‘.’,1)；若从右开始获取“com”则为序号为-1即substring_index(“www.mysql.com”,‘.’,-1);若从做开始获取“www.mysql”则为序号为2即substring_index(“www.mysql.com”,‘.’,2)

• 详细说明：
  首先，设待处理对象字符串为“15,151,152,16”（虽然这里指的不是iP，可以看作是IP来处理吧）
  这里截取的依据是逗号：“，”具体要截取第N个逗号前部分的字符;

由于这个函数不能指定截取中间的部分索引的单词，索引需要用它来嵌套一下来实现获取当前第n部分单词的效果。

最终效果