這兩天在工作中碰到一個需求纳令，開發(fā)那邊希望在做增量數(shù)據(jù)的時候饼齿，能夠得到被更新的字段名以及該字段被更新前后的數(shù)據(jù)屡萤，然后在實(shí)現(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' 中不存在
特殊情況：
-- 當(dāng)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
-- 當(dāng)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ù)認(rèn)為'c,d'在'c,d,a,d,a,v'中不存在泵琳，但是看起來又似乎是存在的摄职。
1.2 str和strlist為字段
- 兩個都是字段：每條記錄的str和strlist進(jìn)行比較
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可以是負(fù)數(shù)，此時從后往前數(shù)）且改。用來在str中截取第count個關(guān)鍵字之前的所有字符验烧。
比如：
-- count是正數(shù)時
SELECT
*,
SUBSTRING_INDEX(strlist,',',2)
FROM instr_test_left
返回
上面的查詢用來截取strlist中第二個逗號之前的所有字符，當(dāng)字符串中的逗號個數(shù)小于2時又跛，返回原數(shù)據(jù)（比如最后一條記錄e碍拆，返回的還是e）。
-- count是負(fù)數(shù)時
SELECT
*,
SUBSTRING_INDEX(strlist,',',-3)
FROM instr_test_left
返回
上面的查詢用來截取strlist倒數(shù)第三個逗號之后的所有字符慨蓝，當(dāng)字符串中的逗號個數(shù)小于3時感混，返回原數(shù)據(jù)。
二礼烈、實(shí)現(xiàn)流程
假設(shè)現(xiàn)在的目標(biāo)表test_instr如下（主鍵是id）
要更新到目標(biāo)表中的數(shù)據(jù)test_instr_2如下（主鍵是id）
大體的思路如下：
1弧满、當(dāng)主鍵匹配上時才做更新操作，需要收集更新信息（匹配不上的做新增）此熬；
2庭呜、將目標(biāo)表test_instr除主鍵id外，進(jìn)行列轉(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，當(dāng)返回值為0時未斑，表示發(fā)生了更新咕宿，提取CONCAT_VALUE中相應(yīng)的值（根據(jù)更新的字段名稱）作為NEW_VALUE。
具體實(shí)現(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芽突，但實(shí)際上空到空不算做更新试浙。
但是，現(xiàn)在有個問題：
列轉(zhuǎn)行和合并字段值寞蚌，需要把除主鍵以外的所有字段都列出來田巴，實(shí)際業(yè)務(wù)中有的表字段非常多，全部列出來的話工作量很大挟秤，我考慮利用kettle將列轉(zhuǎn)行和合并字段值的數(shù)據(jù)分別存到新表壹哺，用新表操作，但是這樣的話每張表就要對應(yīng)一個流程艘刚，有多少張表就要建立多少個流程管宵，很繁瑣，如果有好的方法希望大家多多指教攀甚！
