由PostgreSQL数据库（版本号：PostgreSQL 15.4, compiled by Visual C++ build 1914, 64-bit）迁移到OceanBase 的 MySQL模式（下直接称mysql，版本号：5.7.25-OceanBase-v3.2.3.3）过程产生的问题及解决方案

类别 问题 解决方案
DDL mysql没有schema，只能通过 “逻辑数据库” 对应pg的schema，但新的数据库名和旧的schema名称多了数据库前缀，因为在之前不同的pg数据库中存在相同名称的schema，在mysql中是不允许同名数据库的 将 schema. 替换为新的 数据库名称. 如 base. 替换为 new_base.
pg自增是通过bigserial类型的序列来实现的，mysql为自身的特性 将 id bigserial NOT NULL 中的 bigserial 改为 BIGINT AUTO_INCREMENT
mysql没有jsonb字段 将 jsonb 改为 json
pg的表备注dll为 COMMENT ON TABLE bms_base.base_code_detail IS '字典详情表'; mysql不支持 改为 ALTER TABLE bms_base.base_code_detail COMMENT '字典详情表';（可将 COMMENT ON 替换为 ALTER TABLE 将 IS ' 替换为 COMMENT ' ）
或直接将COMMENT = '字典详情表' 加到ddl最后
pg的字段备注dll为 COMMENT ON COLUMN bms_base.base_code_detail .id IS '自增主键'; mysql不支持 改为 ALTER TABLE bms_base.base_code_detail MODIFY COLUMN id bigint(20) COMMENT '自增主键'; 或直接将 COMMENT '自增主键' 加到每一列的ddl最后
`id` bigint(20) NOT NULL COMMENT '自增主键',
关键字 如 PG 的 "method" ddl有引号到mysql报错 如该关键字不是mysql的关键字 则去掉引号
如："method" 去掉引号 method
如该关键字是mysql的关键字 则引号 变为反引号 如："password" 改为 `password`
或将所有的列都加上反引号 例如：
CREATE TABLE `sys_user` ( `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID', `dept_id` bigint(20) DEFAULT NULL COMMENT '部门ID', `user_name` varchar(30) NOT NULL COMMENT '用户账号', ......
pg的varchar可以不指定长度mysql不支持 varchar 改为例： varchar(1000)
建立索引报错
CREATE UNIQUE INDEX base_code_detail_code_type_idx ON bms_base.base_code_detail USING btree (code_type, code_detail);		 去掉 USING btree
CREATE UNIQUE INDEX base_code_detail_code_type_idx ON bms_base.base_code_detail (code_type, code_detail);
pg的 bool 类型 可使用 bool 类型 但建表之后会自动转换为 tinyint(1)
pg的 int2 类型 mysql 不支持 改为 int
pg的 int4 类型 mysql 不支持 改为 int
pg的 int8 类型 mysql 不支持 改为 bigint
pg的 serial4 类型 mysql 不支持 改为 int
pg的 bpchar(2) 类型 mysql 不支持 改为 char(2)
pg的 numeric 类型 mysql 不支持 无需改动 建表后会自动转换为 decimal
pg的默认值 '0'::character varying 语法 mysql 不支持 去掉 ::character varying
在建表之后，给id加自增、更改某些表结构（如json改为text等）报错 貌似OceanBase for MySQL无法支持某些结构在建表后再修改，目前的方法只能 重新建表
在mysql中，若在字段A上建立了唯一索引，插入两行字段A只有大小写不同的数据时，会报唯一索引冲突的错误 更改表的排序规则为 utf8mb4_bin
数据迁移
(主要采用dbever和Navicat的库对库导入导出方式)		 有时pg的 jsonb 类型字段在导入mysql的 json 字段后，会变成base64的编码 如使用dbever，更改数据导出导入方式如：csv文件、sql文件
如使用Navicat，在输入传输选项中取消勾选 “为 BLOB 使用十六进制格式” 选项
有些pg的 jsonb 类型字段在导入mysql的 json 字段时，会报错
package for query is too large，you can change this value on the server by setting the 'max_allowed_package' variable		 mysql对单次接收的 单个值的大小有限制 默认为4m，如确实有正常业务下产生的大于4m的单个值的数据，需要更改mysql数据库配置，此报错为测试环境极端情况产生的数据，故直接丢弃
dbever在做大数据量表迁移时速度过慢 使用Navicat做数据迁移，为避免不必要的报错，迁移时 表选项均不勾选，记录选项只勾选 “使用完整插入语句”、“使用扩展插入语句”，其它选项均不勾选
使用dbever迁移部分表时，插入同步Date类型字段时，结果会少一天 连接信息里，服务器时区不要选择默认的 自动检测，要手动选择 Asia/Shanghai
多个迁移任务同时进行有时mysql服务器会报错 Navicat与dbever在进行库对库导入导出时，单个迁移任务就是多线程运行的，如果多个迁移任务同时进行，就会有更多线程同时对数据库进行写入，可能会导致 数据库压力过大而报错
迁移测试环境时，尽量不要有太多任务同时进行，迁移生产环境时，尽量不要有任务同时进行，用时间换取稳定性
最好在准生产环境全量迁移几次后估算生产环境全量迁移的时间，以提前规划好迁移生产环境的时间
配置文件 普通insert语句报错：
insert into sys_job_log( job_name, job_group, invoke_target, job_message, status, create_time )values( ?, ?, ?, ?, ?, current_timestamp )
Cause: java.sql.SQLException: Index 0 out of bounds for length 0; Index 0 out of bounds for length 0		 降低mysql连接驱动版本
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
或降低mysql连接驱动版本至5.x，OceanBase-v3.2.3.3对应的mysql版本为5.7
注意此时driver-class-name 应配置为：com.mysql.jdbc.Driver，mysql 8.x为com.mysql.cj.jdbc.Driver
使用 batch 报错 在数据库连接URL中加入 &rewriteBatchedStatements=true&allowMultiQueries=true 参数
插入Date类型字段时，有时插入结果会少一天 在数据库连接URL中加入 &serverTimezone=Asia/Shanghai 参数
SQL mysql没有schema，只能通过 “逻辑数据库” 对应pg的schema，但新的数据库名和旧的schema名称多了数据库前缀，因为在之前不同的pg数据库中存在相同名称的schema，在mysql中是不允许同名数据库的 将 schema. 替换为新的 数据库名称. 如 base. 替换为 new_base.
mysql没有to_char方法，在时间类型转换为字符串的时候报错 将 to_char(status_update_time, 'yyyy-MM-dd') 改为 date_format(status_update_time,'%Y-%m-%d')
如果带时间则格式化字符串为 '%Y-%m-%d %H:%i:%s' 如果带毫秒为 '%Y-%m-%d %H:%i:%s.%f'
cast(#{value} as INTEGER) 报错 去掉 cast 方法 直接使用 #{value} 能够转换为数字的字符串数据库会自动处理
update bms_public.sys_menu set query = #{query} 报错 query与mysql关键字冲突 query = #{query} 改为 `query` = #{query}
any(string_to_array(column,',')) 报错 改为 find_in_set(#{query.deptId}, column)
to_timestamp 报错 如果是 date 与 timestamp 字段类型比较 则可去掉 to_timestamp mysql可自动处理
to_date(pre_date,'YYYYMMDD') 报错 改为 STR_TO_DATE(pre_date,'%Y%m%d')
select
icitig."CODE",
...
报错：You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '"CODE"		 去掉字段的 双引号
icbsc.security_name <![CDATA[~]]> '^[^0-9]+$' 报错 pg中使用~匹配正则表达式（<![CDATA[~]]>是转义符）mysql换为REGEXP 方法：REGEXP '^[^0-9]+$'
mysql不支持 cast(cal_percent as text) 改为 cast(cal_percent as CHAR)
mysql不支持 cast(#{modifyRowBefore,jdbcType=VARCHAR} as jsonb) 改为 cast(#{modifyRowBefore,jdbcType=VARCHAR} as json)
mysql不支持 distinct on 使用 group by子句实现distinct on的效果 但需要注意排序order by必须在group by之后
如下sql：
select
distinct on (t.name)
t.id,
t.name,
t.value
from table t
改为
select
t.id,
t.name,
t.value
from table t
group by
t.name
需注意在MySQL从5.7版本开始，要求GROUP BY子句中的列必须在SELECT列表中出现，或者以聚合函数的形式出现
pg 的 timestamp 字段支持毫秒，转换为 mysql 的 timestamp 字段后默认没有毫秒 需要测试是否对功能有影响，如需要存储毫秒级的时间戳，则需要使用 timestamp(3) 或者 DATETIME 类型
mysql 不支持 :: 类型转换语法 非必要时去掉类型转换，必要时可使用 cast 方法
mysql 不支持convert_to(org_name,'gbk') 改为 convert(org_name using gbk)
mysql 不支持json检索语法 t.event_info #>> '{symbol,value}' symbol 改为 t.event_info ->> '{symbol,value}' symbol
或
改为 JSON_EXTRACT(t.event_info,'$.symbol.value') symbol
但需要注意 string 类型的 json 的 value，使用pg的 #>>取出后是不带引号的，但使用mysql的 JSON_EXTRACT 方法取出后有引号 需要再处理引号
如需去引号可使用以下方式
replace(JSON_EXTRACT(t.event_info, '$.symbol.value'), '"', '') symbol
还需注意 如果 json 中某 vaule 的值是 null ，使用此方法 pg 取出的是 null，mysql 取出的是 字符串 null，如果还有其它条件，一般需要增加一句 !='null' 判断
还有一个奇怪的现象 有些非字符串类型的 json 的 value ，使用 JSON_EXTRACT(t.event_info, '$.symbol.value') 提取出后是 不带"的字符串，这时直接 和 相同值的 字符串进行 = 比较的时候还会返回 false 替换掉 不存在的"后
replace(JSON_EXTRACT(t.event_info, '$.symbol.value'), '"', '') 才能返回true
mysql 不支持 date_trunc('MONTH', CURRENT_DATE)-INTERVAL '1 day' （获取上个月的最后一天） 改为 last_day(date_sub(curdate(),interval 1 MONTH))
mysql 不支持
jsonb_set(event_info, cast('{' || 'level' || '}' as text[]), cast('{"modBy": "esg_editing.a", "value": "3", "cleaned": true, "modTime": "2023-10-26 13:50:32"}' as jsonb), true)
更新json 中某个 key 的 value		 改为
json_set(event_info, '$.level', cast('{"modBy": "esg_editing.a1111", "value": "3", "cleaned": true, "modTime": "2023-10-26 13:50:32"}' as json ))
但其比 pg 的 jsonb_set 方法少了最后一个参数 ：【create_if_missing】即key不存在时是否自动创建，pg是可选的，mysql不可选，会自动创建，若【create_if_missing】为false，暂未找到mysql中的替换方法
mysql 不支持 NULLS Last 即不支持排序时的nulls Last将空值排序到最后 直接去掉 NULLS Last mysql 会自动将空值排序到最后
mysql 不支持 using
delete
from
table1 t1
using table2 t2
where
t1.column1 = t2.column1		 改为
delete
from
table1 t1
where
t1.column1 in (
select
column1
from
t2
mysql 不支持 like any column like any (array[ '3670%', '1234%']) 改为 column like '3670%' or column like '1234%'
mysql 不支持 split_part(str, delim, count) 改为 substring_index(str, delim, count)
mysql 不支持 jsonb_array_elements 未找到 mysql 的替换方法，jsonArray拆分的功能放到内存中
cast('[' || #{key, jdbcType=VARCHAR} || ']' as json) 报错 SQL 错误 [1292] [01000]: Truncated incorrect DOUBLE value 改为 cast(concat('[' , #{key, jdbcType=VARCHAR} , ']') as json)
mysql 不支持
update table
set column =
column || cast(#{key, jdbcType=VARCHAR} as json) 的语法向jsonArray中添加jsonObject		 改为
update table
set column =
JSON_ARRAY_APPEND(key, '$', cast(#{key,jdbcType=VARCHAR} as json))
substr('1_x_y', 0, position('_' in '1_x_y')) 在pg返回 1 在mysql返回空 改为 substr('1_x_y', 1, position('_' in '1_x_y') -1)
mysql 不支持 (date_column + interval '1 day') 加一天 改为 date_add(date_column, interval 1 day)
pg和mysql相同的查询sql，特别是没有指定order by字段的sql，查询结果的默认排序可能会不一致 需评估是否会对给功能及业务产生影响，如有影响可通过 指定order by字段 解决

其它注意事项

  1. 在DDL改造的过程中，并非 PG 某些字段一定要对应到 MySQL 的某些字段，但最好使用 “最佳兼容原则” 进行修改，尽量不要改变程序中映射类属性的数据类型；
  2. 从PG到MySQL，并非100%的SQL都能通过改造达到相同的效果，如果少量SQL难以改造或改造后性能较差且难以优化，可以视情况考虑将SQL逻辑拆分放到内存中进行处理。
  3. 在进行功能性改造之后，还需要关注性能问题。由于受不同数据库本身性能不同、对查询的优化（如索引的利用）不同、相同或相似方法的性能不同等影响，某些复杂的查询SQL或大数据量表的查询SQL可能会运行效率较低或直接超时。
    可以对改造后的系统进行慢SQL记录，测试记录一段时间后，收集慢SQL，经过分析后根据问题原因针对性解决：如在 MySQL 数据库中，RANK()窗口函数 的性能较pg数据库低，可尝试使用 聚合函数+表自连接 的方式进行替换；某些无索引的查询sql在pg数据库中能以尚可接受的效率查询出结果，但在mysql数据库中效率低下，可尝试在mysql数据库的表中增加更多的索引解决；
  4. 本文是 PostgreSQL(PG) 数据库到 OceanBase 的 MySQL 模式的经验教训，大部分问题和原生 MySQL 是一致的，但不保证完全一致。
# 数据库 # postgresql # oceanbase # mysql
Logo
中德AI开发者社区

2万人民币佣金等你来拿，中德社区发起者X.Lab，联合德国优秀企业对接开发项目，领取项目得佣金！！！

更多推荐

cover

模糊神经网络的基础原理剖析(附DeepSeek行业解决方案100+)

avatar 中德AI开发者社区
cover

X.Lab的AI报告丨巴黎上演AI巅峰对话！GOSIM大会首日狂掀技术风暴！

avatar 中德AI开发者社区
cover

国际AI工程师齐聚！携手X.Lab，共筑中德AI合作新桥梁！

avatar 中德AI开发者社区