目录
0. 示例表数据
root@localhost[demo]> select * from demo.tcustmer;
+-----------+--------------------+-------------+-------+
| CUST_CODE | NAME | CITY | STATE |
+-----------+--------------------+-------------+-------+
| ANN | ANN'S BOATS | NEW YORK | NY |
| BILL | BILL'S USED CARS | DENVER | CO |
| DAVE | DAVE'S PLANES INC. | TALLAHASSEE | FL |
| JANE | ROCKY FLYER INC. | DENVER | CO |
| WILL | BG SOFTWARE CO. | SEATTLE | WA |
+-----------+--------------------+-------------+-------+
1. MySQL端数据校验
1.1 MySQL列转行
-- 设置字段拼接最大长度,防止字段过多显示不全
SET @@GLOBAL.GROUP_CONCAT_MAX_LEN=10000,@@GROUP_CONCAT_MAX_LEN=10000;
SELECT @@GLOBAL.GROUP_CONCAT_MAX_LEN,@@GROUP_CONCAT_MAX_LEN;
-- 查询COLUMNS表元数据做字段拼接
SELECT TABLE_NAME,
CONCAT(GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION SEPARATOR ',')) AS ALL_COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'demo'
AND TABLE_NAME = 'tcustmer'
GROUP BY TABLE_NAME;
-- 输出结果如下
+------------+---------------------------+
| TABLE_NAME | ALL_COLUMNS |
+------------+---------------------------+
| tcustmer | CUST_CODE,NAME,CITY,STATE |
+------------+---------------------------+
1.2 MySQL数据校验
-- 使用MD5对拼接的字段做校验,生成16进制校验码
SELECT upper(MD5(CONCAT_WS('',CUST_CODE,NAME,CITY,STATE))) as MD5 FROM tcustmer;
+----------------------------------+
| MD5 |
+----------------------------------+
| 9B1A3721FB80C9E75C136C5CFBB7FFC1 |
| E0ACAC3ADA8E3AC75CE08CC32DDB4139 |
| 4ECA8A64D252C69709344EE78BCB5923 |
| D264438E50624428039DC6254851DB52 |
| 1828B98ADF2E5866FA895DCB5B3A3833 |
+----------------------------------+
-- 由于后续需要使用到bit_xor做数值异或,将校验码截取前6位做比对
SELECT left(upper(MD5(CONCAT_WS('',CUST_CODE,NAME,CITY,STATE))),6) as MD5 FROM tcustmer;
+--------+
| MD5 |
+--------+
| 9B1A37 |
| E0ACAC |
| 4ECA8A |
| D26443 |
| 1828B9 |
+--------+
-- 将16进制的MD5前6位字符串转换为10进制
SELECT conv(left(upper(MD5(CONCAT_WS('',CUST_CODE,NAME,CITY,STATE))),6),16,10) as MD5 FROM tcustmer;
+----------+
| MD5 |
+----------+
| 10164791 |
| 14724268 |
| 5163658 |
| 13788227 |
| 1583289 |
+----------+
-- 最后对10进制的MD5值做异或算法生成唯一校验值
SELECT bit_xor(conv(left(upper(MD5(CONCAT_WS('',CUST_CODE,NAME,CITY,STATE))),6),16,10)) as MD5 FROM tcustmer;
+----------+
| MD5 |
+----------+
| 16724203 |
+----------+
2. Oracle端数据校验
由于Oracle端默认没有bit_xor函数,需要自行创建,且Oracle端MD5校验值是由
DBMS_OBFUSCATION_TOOLKIT.MD5包生成,为了方便也重新创建函数进行封装
2.1 Oracle列转行
-- 查询DBA_TAB_COLUMNS生成拼接字段
col table_name for a32
col column_concat for a40
-- 使用listagg聚合函数
select table_name, listagg(COLUMN_NAME, '||') within group(order by column_id) column_concat
from DBA_TAB_COLUMNS
where OWNER = upper('scott')
and table_name=upper('tcustmer')
group by table_name;
-- 输出结果示例
TABLE_NAME COLUMN_CONCAT
--------- ----------------------------------------
TCUSTMER CUST_CODE||NAME||CITY||STATE
2.2 创建Oracle端MD5函数
-- 该函数只是为了使MD5的用法与MySQL一致
CREATE OR REPLACE FUNCTION MD5(PASSWD IN VARCHAR2) RETURN VARCHAR2 IS
RETVAL VARCHAR2(32);
BEGIN
RETVAL := UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => PASSWD));
RETURN RETVAL;
END;
/
-- 调用测试
col MD5 for a32
select md5(CUST_CODE||NAME||CITY||STATE) as MD5 from TCUSTMER;
-- 生成的结果与MySQL完全一致,符合预期
MD5
--------------------------------
1828B98ADF2E5866FA895DCB5B3A3833
D264438E50624428039DC6254851DB52
4ECA8A64D252C69709344EE78BCB5923
E0ACAC3ADA8E3AC75CE08CC32DDB4139
9B1A3721FB80C9E75C136C5CFBB7FFC1
2.3 创建Oracle端bit_xor函数
create or replace type xor_type as object (
cat_val number,
static function ODCIAggregateInitialize(cs_ctx In Out xor_type) return number,
member function ODCIAggregateIterate(self In Out xor_type,value in number) return number,
member function ODCIAggregateMerge(self In Out xor_type,ctx2 In Out xor_type) return number,
member function ODCIAggregateTerminate(self In Out xor_type,returnValue Out number,flags in number) return number
)
/
create or replace type body xor_type is
static function ODCIAggregateInitialize(cs_ctx IN OUT xor_type) return number
is
begin
cs_ctx := xor_type(0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT xor_type,
value IN number )
return number
is
begin
self.cat_val := value + self.cat_val - BITAND(value, self.cat_val) * 2;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN Out xor_type,
returnValue OUT number,
flags IN number)
return number
is
begin
returnValue := self.cat_val;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT xor_type,
ctx2 IN Out xor_type)
return number
is
begin
self.cat_val := self.cat_val + ctx2.cat_val - BITAND(self.cat_val, ctx2.cat_val) * 2;
return ODCIConst.Success;
end;
end;
/
CREATE OR REPLACE
FUNCTION bit_xor(input number)
RETURN number
PARALLEL_ENABLE AGGREGATE USING xor_type;
/
-- 调用验证
SCOTT@zhenxing> select bit_xor(PRODUCT_PRICE) from TCUSTORD;
BIT_XOR(PRODUCT_PRICE)
----------------------
167872
-- 在MySQL端执行默认的bit_xor函数,对比结果一致
root@localhost[demo]> select bit_xor(PRODUCT_PRICE) from TCUSTORD;
+------------------------+
| bit_xor(PRODUCT_PRICE) |
+------------------------+
| 167872 |
+------------------------+
2.4 Oracle数据校验
-- 生成MD5值
SCOTT@zhenxing> col MD5 for a32
SCOTT@zhenxing> select md5(CUST_CODE||NAME||CITY||STATE) as MD5 from TCUSTMER order by CUST_CODE;
MD5
--------------------------------
9B1A3721FB80C9E75C136C5CFBB7FFC1
E0ACAC3ADA8E3AC75CE08CC32DDB4139
4ECA8A64D252C69709344EE78BCB5923
D264438E50624428039DC6254851DB52
1828B98ADF2E5866FA895DCB5B3A3833
-- 后续需要使用到bit_xor做数值异或,将校验码截取前6位做比对
SCOTT@zhenxing> select substr(md5(CUST_CODE||NAME||CITY||STATE),0,6) as MD5 from TCUSTMER order by CUST_CODE;
MD5
--------------------------------
9B1A37
E0ACAC
4ECA8A
D26443
1828B9
-- 将16进制的MD5前6位字符串转换为10进制
SCOTT@zhenxing> col MD5 for 999999999999999999
SCOTT@zhenxing> select to_number(substr(md5(CUST_CODE||NAME||CITY||STATE),0,6),'xxxxxxxxxx') as MD5 from TCUSTMER order by CUST_CODE;
MD5
-------------------
10164791
14724268
5163658
13788227
1583289
-- 最后对10进制的MD5值做异或算法生成唯一校验值
-- 对比MySQL端校验值,完全一致
SCOTT@zhenxing> select bit_xor(to_number(substr(md5(CUST_CODE||NAME||CITY||STATE),0,6),'xxxxxxxxxx')) as MD5 from TCUSTMER order by CUST_CODE;
MD5
-------------------
16724203
