這兩天在工作中碰到一個需求，開發(fā)那邊希望在做增量數(shù)據(jù)的時候，能夠得到被更新的字段名以及該字段被更新前后的數(shù)據(jù)，然后在實現(xiàn)的過程中，用到了兩個之前沒有看到過的函數(shù)：FIND_IN_SET()和SUBSTRING_INDEX()。
一、函數(shù)的用法
1、FIND_IN_SET(str,strlist)，其中，str是要查找的字符串（可以是字符串也可以是字段），strlist是用逗號分隔的字符串（可以是字符串也可以是字段）；假設(shè)str包含在strlist中，則返回str在strlist中第一次出現(xiàn)的位置（位置從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
注：，參數(shù)str中不能帶逗號，如果帶逗號，函數(shù)會不起作用，
比如
SELECT
FIND_IN_SET('c,d','c,d,a,d,a,v')
FROM db1.`test_instr`
返回0，函數(shù)認為'c,d'在'c,d,a,d,a,v'中不存在，但是看起來又似乎是存在的。
1.2 str和strlist為字段
- 兩個都是字段：每條記錄的str和strlist進行比較
SELECT *,
FIND_IN_SET(str,strlist)
FROM instr_test_left
返回
-- 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為關(guān)鍵字，count為關(guān)鍵字出現(xiàn)的次數(shù)（count可以是負數(shù)，此時從后往前數(shù)）。用來在str中截取第count個關(guān)鍵字之前的所有字符。
比如：
-- count是正數(shù)時
SELECT
*,
SUBSTRING_INDEX(strlist,',',2)
FROM instr_test_left
返回
上面的查詢用來截取strlist中第二個逗號之前的所有字符，當字符串中的逗號個數(shù)小于2時，返回原數(shù)據(jù)（比如最后一條記錄e，返回的還是e）。
-- count是負數(shù)時
SELECT
*,
SUBSTRING_INDEX(strlist,',',-3)
FROM instr_test_left
返回
上面的查詢用來截取strlist倒數(shù)第三個逗號之后的所有字符，當字符串中的逗號個數(shù)小于3時，返回原數(shù)據(jù)。
二、實現(xiàn)流程
假設(shè)現(xiàn)在的目標表test_instr如下（主鍵是id）
要更新到目標表中的數(shù)據(jù)test_instr_2如下（主鍵是id）
大體的思路如下：
1、當主鍵匹配上時才做更新操作，需要收集更新信息（匹配不上的做新增）；
2、將目標表test_instr除主鍵id外，進行列轉(zhuǎn)行操作，列名所在列為UPDATE_FIELD，值所在列為OLD_VALUE；
3、將待更新數(shù)據(jù)test_instr_2中除主鍵id外，剩余字段值用逗號分隔，合并成一列CONCAT_VALUE，同時將所有列名也用逗號分隔，合并成一列CONCAT_COLUMN；
4、利用函數(shù)find_in_set，OLD_VALUE和CONCAT_VALUE分別為參數(shù)str和strlist，當返回值為0時，表示發(fā)生了更新，提取CONCAT_VALUE中相應(yīng)的值（根據(jù)更新的字段名稱）作為NEW_VALUE。
具體實現(xiàn)如下：
MySQL中的列轉(zhuǎn)行
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
返回
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
返回
利用find_in_set()函數(shù)得到更新的數(shù)據(jù)，通過
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
-- 列轉(zhuǎn)行
(
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 <> '');
返回
這里加了一個條件FIND_IN_SET中的兩個參數(shù)不同時為空（至少有一個不為空），如果兩個參數(shù)都為空的話，F(xiàn)IND_IN_SET也會返回0，但實際上空到空不算做更新。
但是，現(xiàn)在有個問題：
列轉(zhuǎn)行和合并字段值，需要把除主鍵以外的所有字段都列出來，實際業(yè)務(wù)中有的表字段非常多，全部列出來的話工作量很大，我考慮利用kettle將列轉(zhuǎn)行和合并字段值的數(shù)據(jù)分別存到新表，用新表操作，但是這樣的話每張表就要對應(yīng)一個流程，有多少張表就要建立多少個流程，很繁瑣，如果有好的方法希望大家多多指教！
