mysql 字段取出关键字数据_在MySQL中提取字段名称以及更新前后数据

这两天在工作中碰到一个需求，开发那边希望在做增量数据的时候，能够得到被更新的字段名以及该字段被更新前后的数据，然后在实现的过程中，用到了两个之前没有看到过的函数：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将列转行和合并字段值的数据分别存到新表，用新表操作，但是这样的话每张表就要对应一个流程，有多少张表就要建立多少个流程，很繁琐，如果有好的方法希望大家多多指教！