一、背景

在做某项目数据迁移适配时，发现MySQL源端部分表结构涉及使用SET特殊类型。‌MySQL中的SET类型是一种特殊的数据类型，用于存储一个固定集合的字符串值。‌每个SET列可以包含零个、一个或多个预定义的值，这些值在存储时是通过逗号分隔的字符串表示的。SET类型的值最多可以有64个不同的成员，且值的顺序不重要。插入记录时，可以使用SET类型中的值，也可以用索引值的方式。记录存入MySQL数据库后，数据库系统会自动按照定义时的顺序显示。而达梦数据库暂不支持SET类型，且无法简单调整为VARCHAR类型和约束实现上述功能，因此调整为VARCHAR类型，通过辅助表、存储过程和触发器实现，作如下适配记录。

二、数据库环境

1、DM8
数据库版本：DM Database 64 V8 05134284336-20250117-257733-20132
参数：case_sensitive=1 blank_pad_mode=1 compatible_mode=4

2、MySQL
数据库版本：Server version: 8.4.3 MySQL Community Server
参数：lower_case_file_system=OFF lower_case_table_names=1

三、适配过程

1、创建存储过程处理插入整数类型数据的情况

CREATE OR REPLACE PROCEDURE PRO_INSERT_SET_NUM( v_user_name IN VARCHAR2,
v_table_name IN VARCHAR2,
v_column_name IN VARCHAR2,
v_input IN NUMBER,
v_result OUT VARCHAR2)
AS
v_str        VARCHAR2(32767) := ''; -- 记录临时字符串
v_pos   NUMBER          := 1;
v_max_binary NUMBER ;
v_sql        VARCHAR2(32767);
BEGIN
--v_input := TO_NUMBER(v_input);
--计算最大二进制数
v_sql := 'SELECT max(id) FROM KEEP_SET WHERE user_name = ''' || v_user_name || ''' and table_name = ''' || v_table_name || ''' and column_name = ''' || v_column_name || ''';';
--DBMS_OUTPUT.PUT_LINE(v_sql);

EXECUTE IMMEDIATE v_sql into v_max_binary;

v_max_binary := POWER(2, v_max_binary) - 1;
--计算转换的字符串
IF v_input < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid input value: must be a non-negative integer');
ELSIF v_input = 0 THEN
v_result := '';
ELSIF v_input <= v_max_binary THEN
WHILE v_pos <= TRUNC(LOG(2, v_input + 1)) + 1
LOOP
IF TRUNC(v_input / POWER(2, v_pos - 1)) MOD 2 = 1 THEN
v_str := v_str || v_pos || ',';
END IF;
v_pos := v_pos + 1;
END LOOP;
v_str := '(' || RTRIM(v_str, ',') || ')';

v_sql := 'SELECT to_char(wm_concat(token)) FROM (SELECT id, token FROM KEEP_SET WHERE user_name = ''' || v_user_name || ''' and table_name = ''' || v_table_name || ''' and column_name = ''' || v_column_name || ''' and id in '||v_str || ' order by id);';
--DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql INTO v_result;
ELSE
RAISE_APPLICATION_ERROR(-20001, 'Invalid input value: out of range');
END IF;
--DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

2、创建存储过程处理插入字符类型数据的情况

CREATE OR REPLACE PROCEDURE PRO_INSERT_SET_VAR( v_user_name IN VARCHAR2,
v_table_name IN VARCHAR2,
v_column_name IN VARCHAR2,
v_input IN VARCHAR2,
v_result OUT VARCHAR2
)
AS
v_str        VARCHAR2(32767) := ''; -- 记录临时字符串
v_token VARCHAR2(32767);
v_pos   NUMBER;
v_start NUMBER := 1;
v_exists NUMBER;
v_sql        VARCHAR2(32767);
BEGIN

--排除插入值前后是逗号的情况
IF SUBSTR(v_input, 1, 1) = ',' OR SUBSTR(v_input, -1, 1) = ',' THEN
RAISE_APPLICATION_ERROR(-20001, 'Insert line cannot start or end with a comma.');
END IF;
--如果插入空串，则直接输出
IF v_input = '' or v_input is null THEN
v_result := v_input;
ELSE
LOOP
v_pos := INSTR(v_input, ',', v_start);
IF v_pos = 0 THEN
v_token := SUBSTR(v_input, v_start);
SELECT COUNT(*) INTO v_exists FROM keep_set WHERE token = v_token;
IF v_exists = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Token ' || v_token || ' does not exist in keep_set');--直接报错不会插入数据
END IF;
EXIT;
ELSE
v_token := SUBSTR(v_input, v_start, v_pos - v_start);
SELECT COUNT(*) INTO v_exists FROM keep_set WHERE token = v_token;
IF v_exists = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Token ' || v_token || ' does not exist in keep_set');--直接报错不会插入数据
END IF;
v_start := v_pos + 1;
END IF;
END LOOP;
v_sql := 'SELECT to_char(wm_concat(token)) FROM(SELECT ID,TOKEN FROM keep_set WHERE user_name = ''' || v_user_name || ''' and table_name = ''' || v_table_name || ''' and column_name = ''' || v_column_name || ''' and token IN (''' || REPLACE(v_input, ',', ''',''') || ''') order by ID);';
--DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql  INTO v_result;
--DBMS_OUTPUT.PUT_LINE(v_result);
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, SQLERRM);
END;
/

3、创建辅助表

DROP TABLE KEEP_SET;
CREATE TABLE KEEP_SET
(
USER_NAME VARCHAR(32767),
TABLE_NAME VARCHAR(32767),
COLUMN_NAME VARCHAR(32767),
ID INT,
TOKEN VARCHAR(32767) ) ;
CREATE INDEX IDX_KEEP_SET ON KEEP_SET(USER_NAME,TABLE_NAME,COLUMN_NAME,ID);
CREATE UNIQUE INDEX IDX_KEEP_SET_UNIQUE ON KEEP_SET(USER_NAME,TABLE_NAME,ID);

4、创建三个公共同义词并授权，使得普通用户也可执行访问

CREATE PUBLIC SYNONYM PRO_INSERT_SET_VAR FOR SYSDBA.PRO_INSERT_SET_VAR;
CREATE PUBLIC SYNONYM PRO_INSERT_SET_NUM FOR SYSDBA.PRO_INSERT_SET_NUM;
CREATE PUBLIC SYNONYM KEEP_SET FOR SYSDBA.KEEP_SET;
--授权给用户PUBLIC
GRANT INSERT,UPDATE,DELETE,SELECT ON KEEP_SET TO PUBLIC;
GRANT EXECUTE ON PRO_INSERT_SET_VAR TO PUBLIC;
GRANT EXECUTE ON PRO_INSERT_SET_NUM TO PUBLIC;

5、创建测试表，测试set类型数据插入
(1)MySQL创建语句：

CREATE TABLE TEST_SET(INSERT_LINE SET('A','b','C','F','Egg','D')) collate utf8_bin;

(2)DM创建语句：

DROP TABLE TEST_SET;
CREATE TABLE TEST_SET(INSERT_LINE VARCHAR2(32767));

--插入set类型列中的数据
INSERT INTO KEEP_SET VALUES('DLJY','TEST_SET','INSERT_LINE',1,'A');
INSERT INTO KEEP_SET VALUES('DLJY','TEST_SET','INSERT_LINE',2,'b');
INSERT INTO KEEP_SET VALUES('DLJY','TEST_SET','INSERT_LINE',3,'C');
INSERT INTO KEEP_SET VALUES('DLJY','TEST_SET','INSERT_LINE',4,'F');
INSERT INTO KEEP_SET VALUES('DLJY','TEST_SET','INSERT_LINE',5,'Egg');
INSERT INTO KEEP_SET VALUES('DLJY','TEST_SET','INSERT_LINE',6,'D');
COMMIT;

--创建触发器
CREATE OR REPLACE TRIGGER TRIGGER_TEST_SET
BEFORE INSERT OR UPDATE ON TEST_SET
FOR EACH ROW
DECLARE
v_result VARCHAR2(32676);
v_user_name VARCHAR2(32676) := 'DLJY';
v_table_name VARCHAR2(32676) := 'TEST_SET';
v_column_name VARCHAR2(32676) := 'INSERT_LINE';
v_num NUMBER;
BEGIN
v_num := TO_NUMBER(:NEW.insert_line);
PRO_INSERT_SET_NUM(v_user_name, v_table_name, v_column_name, :NEW.insert_line, v_result);
EXCEPTION
WHEN OTHERS THEN
PRO_INSERT_SET_VAR(v_user_name, v_table_name, v_column_name, :NEW.insert_line, v_result);
:NEW.insert_line := v_result;
END;
/

6、MySQL和DM均执行以下语句插入测试数据
(1)测试插入字符数据

INSERT INTO TEST_SET VALUES('A,B,C'); --插入不进去
INSERT INTO TEST_SET VALUES('F,A,C'); --'A,C,F'
INSERT INTO TEST_SET VALUES('A,B,C,G'); --插入不进去
INSERT INTO TEST_SET VALUES(''); --空串
INSERT INTO TEST_SET VALUES(' '); --空格串，插入不进去
INSERT INTO TEST_SET VALUES(',A'); --插入不进去
INSERT INTO TEST_SET VALUES('a,B,c'); --插入不进去
INSERT INTO TEST_SET VALUES('Egg'); --'Egg'
INSERT INTO TEST_SET VALUES('g'); --插入不进去
INSERT INTO TEST_SET VALUES('Egg,b,b,F,A'); --'A,b,F,Egg'
COMMIT;
SELECT * FROM TEST_SET;

(2)测试插入整数数据

TRUNCATE TABLE TEST_SET;
INSERT INTO TEST_SET VALUES(-1);--插入不进去
INSERT INTO TEST_SET VALUES(0); --''
INSERT INTO TEST_SET VALUES(1); --'A'
INSERT INTO TEST_SET VALUES(2.5); --'b'
INSERT INTO TEST_SET VALUES(21);--'A,C,Egg'
INSERT INTO TEST_SET VALUES(63);--'A,b,C,F,Egg,D'
INSERT INTO TEST_SET VALUES(64);--插入不进去
COMMIT;
SELECT * FROM TEST_SET;

(3)测试插入混合数据

TRUNCATE TABLE TEST_SET;
INSERT INTO TEST_SET VALUES
(0),
(1),
(63),
('F,A,C'),
(''),
('Egg'),
('A,b,C,A')
;
COMMIT;
SELECT * FROM TEST_SET;

四、测试对比

1、插入字符数据

2、插入数值数据

3、插入混合类型数据