1. MySQL到openGauss迁移的核心挑战最近在帮客户做数据库迁移时踩了不少坑。从MySQL切换到openGauss看似只是换个数据库实际暗藏两大杀手级差异大小写敏感问题和二进制类型处理。这两个问题如果没处理好轻则查询报错重则数据丢失。先说说大小写敏感这个坑。MySQL默认是大小写不敏感的SELECT * FROM users WHERE nameTom和SELECT * FROM users WHERE nametom返回结果相同。但openGauss默认是大小写敏感的同样的查询可能返回空结果。这个问题在应用代码里埋得最深——很多开发者根本意识不到自己依赖了这个特性。二进制类型的问题更隐蔽。MySQL的BLOB类型在openGauss里对应bytea类型但JDBC驱动处理方式完全不同。我见过最坑的情况是数据能正常写入但读取时却报错原因竟是MyBatis映射配置多了个jdbcType属性。2. 大小写敏感问题的终极解决方案2.1 兼容性模式的选择openGauss支持四种兼容模式A模式兼容OracleB模式兼容MySQL推荐C模式兼容TeradataPG模式兼容PostgreSQL关键操作创建数据库时指定B模式CREATE DATABASE mydb DBCOMPATIBILITY B;2.2 字符序(Collation)的魔法在B模式下通过字符序控制大小写敏感utf8mb4_general_ci不区分大小写utf8mb4_bin区分大小写建表示例CREATE TABLE users ( id VARCHAR(32) COLLATE pg_catalog.utf8mb4_general_ci PRIMARY KEY, username VARCHAR(100) COLLATE pg_catalog.utf8mb4_general_ci, password VARCHAR(100) COLLATE pg_catalog.utf8mb4_bin );2.3 实际踩坑案例我们迁移的一个用户表有50万条数据原MySQL查询SELECT * FROM users WHERE emailUSEREXAMPLE.COM;在openGauss中必须改为-- 方案1修改查询条件不推荐 SELECT * FROM users WHERE LOWER(email)LOWER(USEREXAMPLE.COM); -- 方案2建表时指定字符序推荐 CREATE TABLE users ( email VARCHAR(255) COLLATE pg_catalog.utf8mb4_general_ci );3. 二进制数据类型迁移指南3.1 类型映射关系MySQL类型openGauss类型注意事项BLOBbytea首选方案BLOBblob不推荐使用3.2 JDBC操作对比MySQL写法// 插入BLOB数据 preparedStatement.setBlob(1, inputStream); // MyBatis映射 Result(columnfile_data, propertyfileData, jdbcTypeJdbcType.BLOB)openGauss正确写法// 插入bytea数据 preparedStatement.setBinaryStream(1, inputStream); // 或 preparedStatement.setBytes(1, bytes); // MyBatis映射去掉jdbcType Result(columnfile_data, propertyfileData)3.3 性能优化技巧处理大二进制数据时增大JDBC连接参数defaultRowFetchSize100使用流式读取try (ResultSet rs statement.executeQuery()) { while (rs.next()) { try (InputStream is rs.getBinaryStream(data)) { // 处理流数据 } } }4. 完整迁移流程4.1 准备工作安装openGauss Docker镜像docker run --name opengauss \ -e GS_PASSWORDMyComplexPassword123 \ -p 5432:5432 \ -d enmotech/opengauss:5.0.1创建目标模式CREATE SCHEMA myapp; SET search_path TO myapp;4.2 SQL脚本转换使用sed命令批量处理# 1. 移除所有双引号 sed s///g mysql_dump.sql temp.sql # 2. 转换BLOB类型 sed -i s/BLOB/bytea/g temp.sql # 3. 处理保留字字段如order改为order sed -i s/order/order/g temp.sql4.3 迁移验证清单检查所有表数量是否一致SELECT count(*) FROM information_schema.tables WHERE table_schemamyapp;验证数据量-- MySQL SELECT table_name, table_rows FROM information_schema.tables WHERE table_schemamydb; -- openGauss SELECT relname AS table_name, n_live_tup AS row_count FROM pg_stat_user_tables WHERE schemanamemyapp;5. 应用层适配要点5.1 MyBatis配置调整错误示例resultMap iduserResult typeUser result propertyavatar columnavatar jdbcTypeBLOB/ /resultMap正确写法resultMap iduserResult typeUser result propertyavatar columnavatar/ /resultMap5.2 Spring Boot配置spring: datasource: driver-class-name: org.postgresql.Driver url: jdbc:postgresql://localhost:5432/mydb?currentSchemamyapp username: myuser password: mypassword hikari: connection-init-sql: SET search_path TO myapp5.3 事务处理差异MySQL默认RR隔离级别openGauss默认RC。需要显式设置Transactional(isolation Isolation.REPEATABLE_READ) public void transferMoney() { // 业务逻辑 }6. 性能调优建议连接池配置spring: datasource: hikari: maximum-pool-size: 20 idle-timeout: 30000 max-lifetime: 1800000关键参数调整-- 增加工作内存 ALTER SYSTEM SET work_mem 16MB; -- 优化并行查询 ALTER SYSTEM SET max_parallel_workers 8; ALTER SYSTEM SET max_parallel_workers_per_gather 4;监控指标-- 查看慢查询 SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; -- 锁等待监控 SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid;7. 常见问题排查问题1迁移后查询报错column does not exist检查字段名大小写是否一致解决方案统一使用小写字段名或严格使用双引号包裹问题2二进制数据读取报错检查MyBatis是否误加了jdbcType属性验证直接使用JDBC读取测试问题3性能下降严重检查执行计划是否走错索引工具使用EXPLAIN ANALYZE分析查询问题4事务隔离问题现象出现不可重复读解决显式设置事务隔离级别为REPEATABLE READ8. 迁移后的验证策略数据一致性检查# 使用pandas进行抽样比对 import pandas as pd from sqlalchemy import create_engine mysql_engine create_engine(mysql://user:passmysql-host:3306/db) gauss_engine create_engine(postgresql://user:passgauss-host:5432/db) df_mysql pd.read_sql(SELECT * FROM users SAMPLE 1000, mysql_engine) df_gauss pd.read_sql(SELECT * FROM users TABLESAMPLE SYSTEM(1), gauss_engine) pd.testing.assert_frame_equal(df_mysql, df_gauss)性能基准测试使用JMeter模拟生产流量对比TPS、响应时间等关键指标应用兼容性测试全量回归测试重点测试包含二进制操作的场景9. 进阶技巧自动化迁移工具对于大型系统建议使用迁移工具链Schema转换# 使用pgloader工具 pgloader mysql://user:passhost:3306/db \ postgresql://user:passhost:5432/db数据校验# 使用data-diff工具>
1. MySQL到openGauss迁移的核心挑战最近在帮客户做数据库迁移时踩了不少坑。从MySQL切换到openGauss看似只是换个数据库实际暗藏两大杀手级差异大小写敏感问题和二进制类型处理。这两个问题如果没处理好轻则查询报错重则数据丢失。先说说大小写敏感这个坑。MySQL默认是大小写不敏感的SELECT * FROM users WHERE nameTom和SELECT * FROM users WHERE nametom返回结果相同。但openGauss默认是大小写敏感的同样的查询可能返回空结果。这个问题在应用代码里埋得最深——很多开发者根本意识不到自己依赖了这个特性。二进制类型的问题更隐蔽。MySQL的BLOB类型在openGauss里对应bytea类型但JDBC驱动处理方式完全不同。我见过最坑的情况是数据能正常写入但读取时却报错原因竟是MyBatis映射配置多了个jdbcType属性。2. 大小写敏感问题的终极解决方案2.1 兼容性模式的选择openGauss支持四种兼容模式A模式兼容OracleB模式兼容MySQL推荐C模式兼容TeradataPG模式兼容PostgreSQL关键操作创建数据库时指定B模式CREATE DATABASE mydb DBCOMPATIBILITY B;2.2 字符序(Collation)的魔法在B模式下通过字符序控制大小写敏感utf8mb4_general_ci不区分大小写utf8mb4_bin区分大小写建表示例CREATE TABLE users ( id VARCHAR(32) COLLATE pg_catalog.utf8mb4_general_ci PRIMARY KEY, username VARCHAR(100) COLLATE pg_catalog.utf8mb4_general_ci, password VARCHAR(100) COLLATE pg_catalog.utf8mb4_bin );2.3 实际踩坑案例我们迁移的一个用户表有50万条数据原MySQL查询SELECT * FROM users WHERE emailUSEREXAMPLE.COM;在openGauss中必须改为-- 方案1修改查询条件不推荐 SELECT * FROM users WHERE LOWER(email)LOWER(USEREXAMPLE.COM); -- 方案2建表时指定字符序推荐 CREATE TABLE users ( email VARCHAR(255) COLLATE pg_catalog.utf8mb4_general_ci );3. 二进制数据类型迁移指南3.1 类型映射关系MySQL类型openGauss类型注意事项BLOBbytea首选方案BLOBblob不推荐使用3.2 JDBC操作对比MySQL写法// 插入BLOB数据 preparedStatement.setBlob(1, inputStream); // MyBatis映射 Result(columnfile_data, propertyfileData, jdbcTypeJdbcType.BLOB)openGauss正确写法// 插入bytea数据 preparedStatement.setBinaryStream(1, inputStream); // 或 preparedStatement.setBytes(1, bytes); // MyBatis映射去掉jdbcType Result(columnfile_data, propertyfileData)3.3 性能优化技巧处理大二进制数据时增大JDBC连接参数defaultRowFetchSize100使用流式读取try (ResultSet rs statement.executeQuery()) { while (rs.next()) { try (InputStream is rs.getBinaryStream(data)) { // 处理流数据 } } }4. 完整迁移流程4.1 准备工作安装openGauss Docker镜像docker run --name opengauss \ -e GS_PASSWORDMyComplexPassword123 \ -p 5432:5432 \ -d enmotech/opengauss:5.0.1创建目标模式CREATE SCHEMA myapp; SET search_path TO myapp;4.2 SQL脚本转换使用sed命令批量处理# 1. 移除所有双引号 sed s///g mysql_dump.sql temp.sql # 2. 转换BLOB类型 sed -i s/BLOB/bytea/g temp.sql # 3. 处理保留字字段如order改为order sed -i s/order/order/g temp.sql4.3 迁移验证清单检查所有表数量是否一致SELECT count(*) FROM information_schema.tables WHERE table_schemamyapp;验证数据量-- MySQL SELECT table_name, table_rows FROM information_schema.tables WHERE table_schemamydb; -- openGauss SELECT relname AS table_name, n_live_tup AS row_count FROM pg_stat_user_tables WHERE schemanamemyapp;5. 应用层适配要点5.1 MyBatis配置调整错误示例resultMap iduserResult typeUser result propertyavatar columnavatar jdbcTypeBLOB/ /resultMap正确写法resultMap iduserResult typeUser result propertyavatar columnavatar/ /resultMap5.2 Spring Boot配置spring: datasource: driver-class-name: org.postgresql.Driver url: jdbc:postgresql://localhost:5432/mydb?currentSchemamyapp username: myuser password: mypassword hikari: connection-init-sql: SET search_path TO myapp5.3 事务处理差异MySQL默认RR隔离级别openGauss默认RC。需要显式设置Transactional(isolation Isolation.REPEATABLE_READ) public void transferMoney() { // 业务逻辑 }6. 性能调优建议连接池配置spring: datasource: hikari: maximum-pool-size: 20 idle-timeout: 30000 max-lifetime: 1800000关键参数调整-- 增加工作内存 ALTER SYSTEM SET work_mem 16MB; -- 优化并行查询 ALTER SYSTEM SET max_parallel_workers 8; ALTER SYSTEM SET max_parallel_workers_per_gather 4;监控指标-- 查看慢查询 SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; -- 锁等待监控 SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid;7. 常见问题排查问题1迁移后查询报错column does not exist检查字段名大小写是否一致解决方案统一使用小写字段名或严格使用双引号包裹问题2二进制数据读取报错检查MyBatis是否误加了jdbcType属性验证直接使用JDBC读取测试问题3性能下降严重检查执行计划是否走错索引工具使用EXPLAIN ANALYZE分析查询问题4事务隔离问题现象出现不可重复读解决显式设置事务隔离级别为REPEATABLE READ8. 迁移后的验证策略数据一致性检查# 使用pandas进行抽样比对 import pandas as pd from sqlalchemy import create_engine mysql_engine create_engine(mysql://user:passmysql-host:3306/db) gauss_engine create_engine(postgresql://user:passgauss-host:5432/db) df_mysql pd.read_sql(SELECT * FROM users SAMPLE 1000, mysql_engine) df_gauss pd.read_sql(SELECT * FROM users TABLESAMPLE SYSTEM(1), gauss_engine) pd.testing.assert_frame_equal(df_mysql, df_gauss)性能基准测试使用JMeter模拟生产流量对比TPS、响应时间等关键指标应用兼容性测试全量回归测试重点测试包含二进制操作的场景9. 进阶技巧自动化迁移工具对于大型系统建议使用迁移工具链Schema转换# 使用pgloader工具 pgloader mysql://user:passhost:3306/db \ postgresql://user:passhost:5432/db数据校验# 使用data-diff工具>
)
异常传递机制与实战处理策略)
)
