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

引言(遇到问题)

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

SELECTa.bname,SUBSTRING_INDEX( SUBSTRING_INDEX( a.bname, &＃39;,&＃39;, b.help_topic_id &＃43; 1 ), &＃39;,&＃39;, - 1 ) AS new_book_name
FROMbook aJOIN mysql.help_topic b ON b.help_topic_id < (LENGTH( a.bname ) - LENGTH(REPLACE ( a.bname, &＃39;,&＃39;, &＃39;&＃39; )) &＃43; 1 )


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

真实作用

为搞懂这种写法的真实目的&＃xff0c;首先介绍这个sql的真实作用&＃xff1a;

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

解析

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

1. mysql.help_topic

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

2. LENGTH( a.bname)

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

3. JOIN

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

LENGTH( a.bname ) - LENGTH(REPLACE ( a.bname, &＃39;,&＃39;, &＃39;&＃39; )) &＃43; 1


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

4. SUBSTRING_INDEX

MySQL中一个很好用的截取字符串的函数&＃xff1a;substring_index。

• 用法规则&＃xff1a;
  substring_index&＃xff08;“待截取有用部分的字符串”&＃xff0c;“截取数据依据的字符”&＃xff0c;截取字符的位置N&＃xff09;

• 参数说明

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

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

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

最终效果