【MySQL\Oracle\PostgreSQL】遷移到openGauss數據出現的問題解決方案

問題1：序列值不自動刷新問題
下面SQL只針對單庫操作以及每個序列只綁定一張表的情況

-- 自動生成的序列，設置序列值
with sequences as (select *from (select table_schema,table_name,column_name,pg_get_serial_sequence(format('%I.%I', table_schema,table_name), column_name) as auto_seq, --<<自動(string_to_array(column_default, E'\''))[2]             as col_seq   --<<手動from information_schema.columnswhere table_schema not in ('pg_catalog', 'information_schema')and column_default ILIKE 'nextval(%'and table_schema = 'usc') t
--                    where auto_seq is not null),maxvals as (select table_schema,table_name,column_name,auto_seq,col_seq,(xpath('/row/max/text()',query_to_xml(format('select max(%I) as max from %I.%I', column_name, table_schema,table_name),true, true, '')))[1]::text::bigint as max_val,(xpath('/row/cur/text()',query_to_xml(format('select last_value as cur from %I.%I',table_schema, col_seq),true, true, '')))[1]::text::bigint as cur_valfrom sequences)
select table_schema,table_name,column_name,auto_seq,col_seq,cur_val,coalesce(max_val, 0) as max_val,setval(col_seq, coalesce(max_val, 1)) --<<設置序列值
from maxvals;

問題2：
字符類型為空字符串遷移后會變成null排查，需要考慮大表問題，防止慢SQL

-- 數據庫遷移出現null/空字符串 排除
with t as (select table_schema,table_name,column_name,(xpath('/row/cnt/text()',query_to_xml(format(E'select count(*) as cnt from %I.%I where %I = \'\'', table_schema,table_name, column_name),true, true, '')))[1]::text::bigint as null_cnt
from information_schema.columns
where table_schema not in ('pg_catalog', 'information_schema')and table_schema = 'usc'and is_nullable = 'NO'and udt_name in ('varchar', 'text', 'bpchar'))
select * from t where null_cnt > 0;

問題3：
時間精度問題，導致時間范圍查詢失敗。批量調整時間字段精度

DO
$$DECLAREr RECORD;BEGINFOR r INselect table_name, column_name, udt_namefrom information_schema.columnswhere table_schema not in ('pg_catalog', 'information_schema')and table_schema = 'usc'and udt_name in ('timestamp','timestamptz')LOOPEXECUTE 'ALTER TABLE ' || r.table_name || ' ALTER COLUMN ' || r.column_name || ' TYPE ' || r.udt_name ||'(0)';END LOOP;END
$$;