这两天在工作中碰到一个需求，开发那边希望在做增量数据的时候，能够得到被更新的字段名以及该字段被更新前后的数据，然后在实现的过程中，用到了两个之前没有看到过的函数：FIND_IN_SET()和SUBSTRING_INDEX()。
一、函数的用法
1、FIND_IN_SET(str,strlist)，其中，str是要查找的字符串（可以是字符串也可以是字段），strlist是用逗号分隔的字符串（可以是字符串也可以是字段）；假设str包含在strlist中，则返回str在strlist中第一次出现的位置（位置从1开始），如果不包含，则返回0。
1.1 str和strlist都是字符串
比如：
SELECT
FIND_IN_SET('a','c,d,a,d,a,v')
FROM db1.`test_instr`
返回3，第一个'a'在字符串'c,d,a,d,a,v'中在第3的位置。
SELECT
FIND_IN_SET('e','c,d,a,d,a,v')
FROM db1.`test_instr`
返回0，'e'在'c,d,a,d,a,v' 中不存在
特殊情况：
-- 当str或者strlist中任意一个为NULL时，返回NULL
SELECT
FIND_IN_SET(NULL,'c,d,a,d,a,v')
FROM db1.`test_instr`
--
SELECT
FIND_IN_SET('e',NULL)
FROM db1.`test_instr`
--
SELECT
FIND_IN_SET(NULL,NULL)
FROM db1.`test_instr`
上面三条查询均返回NULL
-- 当str或者strlist中任意一个为空字符串时，返回0
比如
SELECT *,
FIND_IN_SET('','')
FROM instr_test_left
--
SELECT *,
FIND_IN_SET('','a,b,c')
FROM instr_test_left
--
SELECT *,
FIND_IN_SET('a','')
FROM instr_test_left
上面三条查询均返回0
注：，参数str中不能带逗号，如果带逗号，函数会不起作用，
比如
SELECT
FIND_IN_SET('c,d','c,d,a,d,a,v')
FROM db1.`test_instr`
返回0，函数认为'c,d'在'c,d,a,d,a,v'中不存在，但是看起来又似乎是存在的。
1.2 str和strlist为字段
- 两个都是字段：每条记录的str和strlist进行比较
SELECT *,
FIND_IN_SET(str,strlist)
FROM instr_test_left
返回
image.png
-- str是字段，strlist是字符串：str字段的每个值和strlist作比较
SELECT str,'k,a,d,d,p,b,f',
FIND_IN_SET(str,'k,a,d,d,p,b,f')
FROM instr_test_left
同理，str是字符串，strlist是段：str和strlist的每个值作比较。
2、SUBSTRING_INDEX(str,delim,count)，其中，str为被截取字段，delim为关键字，count为关键字出现的次数（count可以是负数，此时从后往前数）。用来在str中截取第count个关键字之前的所有字符。
比如：
-- count是正数时
SELECT
*,
SUBSTRING_INDEX(strlist,',',2)
FROM instr_test_left
返回
image.png
上面的查询用来截取strlist中第二个逗号之前的所有字符，当字符串中的逗号个数小于2时，返回原数据（比如最后一条记录e，返回的还是e）。
-- count是负数时
SELECT
*,
SUBSTRING_INDEX(strlist,',',-3)
FROM instr_test_left
返回
image.png
上面的查询用来截取strlist倒数第三个逗号之后的所有字符，当字符串中的逗号个数小于3时，返回原数据。
二、实现流程
假设现在的目标表test_instr如下（主键是id）
image.png
要更新到目标表中的数据test_instr_2如下（主键是id）
image.png
大体的思路如下：
1、当主键匹配上时才做更新操作，需要收集更新信息（匹配不上的做新增）；
2、将目标表test_instr除主键id外，进行列转行操作，列名所在列为UPDATE_FIELD，值所在列为OLD_VALUE；
3、将待更新数据test_instr_2中除主键id外，剩余字段值用逗号分隔，合并成一列CONCAT_VALUE，同时将所有列名也用逗号分隔，合并成一列CONCAT_COLUMN；
4、利用函数find_in_set，OLD_VALUE和CONCAT_VALUE分别为参数str和strlist，当返回值为0时，表示发生了更新，提取CONCAT_VALUE中相应的值（根据更新的字段名称）作为NEW_VALUE。
具体实现如下：
MySQL中的列转行
SELECT
a.ID,'cate' UPDATE_FIELD, a.`cate` OLD_VALUE
FROM test_instr a
UNION ALL
SELECT
a.ID,'uuc' UPDATE_FIELD, a.`uuc` OLD_VALUE
FROM test_instr a
UNION ALL
SELECT
a.ID,'duc' UPDATE_FIELD, a.`duc` OLD_VALUE
FROM test_instr a
返回
image.png
test_instr_2表合并字段操作
SELECT
a.`id` ID_B,
-- 将需要比较的字段值用逗号连接
CONCAT(a.`cate`,',',a.`uuc`,',',a.`duc`) CONCAT_VALUE,
-- 将所有列名用逗号连接
(SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 'test_instr_2') CONCAT_COLUMN
FROM test_instr_2 a
返回
image.png
利用find_in_set()函数得到更新的数据，通过
SELECT 'TEST_INSTR' TABLE_NAME,c.ID ,c.UPDATE_FIELD,c.OLD_VALUE,
SUBSTRING_INDEX( SUBSTRING_INDEX( d.CONCAT_VALUE, ',', FIND_IN_SET(c.UPDATE_FIELD,d.CONCAT_COLUMN)-1 ), ',',- 1 ) NEW_VALUE,
SYSDATE() UPDATE_TIME
FROM
-- 列转行
(
SELECT
a.ID,'cate' UPDATE_FIELD, a.`cate` OLD_VALUE
FROM test_instr a
UNION ALL
SELECT
a.ID,'uuc' UPDATE_FIELD, a.`uuc` OLD_VALUE
FROM test_instr a
UNION ALL
SELECT
a.ID,'duc' UPDATE_FIELD, a.`duc` OLD_VALUE
FROM test_instr a
) c
INNER JOIN
(SELECT
a.`id` ID_B,
-- 将需要比较的字段值用逗号连接
CONCAT(a.`cate`,',',a.`uuc`,',',a.`duc`) CONCAT_VALUE,
-- 将所有列名用逗号连接
(SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 'test_instr_2') CONCAT_COLUMN
FROM test_instr_2 a) d
ON c.ID=d.ID_B
AND FIND_IN_SET(c.OLD_VALUE ,d.CONCAT_VALUE )=0
AND
(c.OLD_VALUE <> ''
or d.CONCAT_VALUE <> '');
返回
image.png
这里加了一个条件FIND_IN_SET中的两个参数不同时为空（至少有一个不为空），如果两个参数都为空的话，FIND_IN_SET也会返回0，但实际上空到空不算做更新。
但是，现在有个问题：
列转行和合并字段值，需要把除主键以外的所有字段都列出来，实际业务中有的表字段非常多，全部列出来的话工作量很大，我考虑利用kettle将列转行和合并字段值的数据分别存到新表，用新表操作，但是这样的话每张表就要对应一个流程，有多少张表就要建立多少个流程，很繁琐，如果有好的方法希望大家多多指教！
网友评论