MySQL到人大金仓：5个致命坑点，如何让应用层代码0行修改？

关注墨瑾轩带你探索编程的奥秘超萌技术攻略轻松晋级编程高手技术宝库已备好就等你来挖掘订阅墨瑾轩智趣学习不孤单即刻启航编程之旅更有趣第一关驱动与方言——换汤不换药的艺术1.1 JDBC驱动别用错了身份证很多人第一步就栽了跟头。人大金仓的JDBC驱动包不是Maven中央仓库里随便搜一个就行的。你得用金仓官方提供的驱动包通常是kingbase8-8.6.0.jar版本号看你用的金仓版本。Maven配置如果官方仓库没有需要手动install到私服!-- 人大金仓JDBC驱动 --!-- 为什么用kingbase8因为金仓V8版本之后统一用这个artifactId --!-- 版本号必须和金仓服务端版本匹配不然协议层会报错 --dependencygroupIdcom.kingbase/groupIdartifactIdkingbase8/artifactIdversion8.6.0/version/dependencyapplication.yml 数据源配置spring:datasource:# 驱动类名金仓V8统一用这个别写成PostgreSQL的# 虽然金仓底层基于PostgreSQL但它的驱动类做了很多兼容性封装driver-class-name:com.kingbase8.Driver# URL是重灾区注意三个关键参数# 1. currentSchema指定默认schema金仓默认是public# 2. compatibleMode开启MySQL兼容模式这是零代码改造的灵魂# 3. stringtype字符串类型处理设为unspecified让驱动自动推断url:jdbc:kingbase8://192.168.1.100:54321/your_db?currentSchemapubliccompatibleModemysqlstringtypeunspecifiedusername:your_userpassword:your_password老墨敲黑板compatibleModemysql这个参数是金仓提供的MySQL兼容模式开关。开启后金仓会在SQL解析层尽量兼容MySQL的语法。但注意它不是100%兼容后面我们会讲到哪些地方还是会翻车。1.2 方言配置告诉ORM框架我换数据库了如果你用的是Hibernate/JPA方言配置是关键spring:jpa:# 金仓官方提供的Hibernate方言类# 如果找不到这个类说明你的金仓驱动包版本不对database-platform:com.kingbase.dialect.KingbaseDialect# 或者用MySQL方言配合compatibleModemysql有时候也能跑# 但生产环境强烈建议用官方方言避免隐式转换坑# database-platform: org.hibernate.dialect.MySQL8Dialecthibernate:# ddl-auto必须设为none信创环境千万别让Hibernate自动建表# 金仓的表结构必须通过KDTS金仓数据迁移工具迁移ddl-auto:none如果你用的是MyBatis国内大多数项目方言配置相对简单因为MyBatis本身不生成SQLSQL是你自己写的。但如果你用了MyBatis-Plus那就必须配置方言mybatis-plus:# 数据库类型金仓官方推荐配置为POSTGRE_SQL# 因为金仓底层是PostgreSQLMP的POSTGRE_SQL方言能兼容大部分语法global-config:db-config:db-type:POSTGRE_SQLconfiguration:# 开启驼峰命名自动映射# 注意金仓默认表名/字段名是小写如果你的MySQL表名是大写这里会出问题后面细说map-underscore-to-camel-case:true第二关大小写敏感——逼疯老码农的隐形杀手这是MySQL迁移到金仓最容易翻车、最隐蔽、最让人崩溃的坑没有之一。2.1 问题根源MySQL和金仓的性格差异MySQL在Linux下表名默认区分大小写lower_case_table_names0但在Windows下不区分lower_case_table_names1。字段名永远不区分大小写。人大金仓底层基于PostgreSQL表名和字段名默认区分大小写且默认存储为小写。这意味着什么如果你在MySQL里建了个表叫User_Info字段叫UserName在金仓里如果没有加双引号它会被自动转成小写user_info和username。然后你的SQL里写的是SELECTUserNameFROMUser_InfoWHEREUserId1在金仓里这条SQL会报错relation “user_info” does not exist或者column “username” does not exist。2.2 解决方案三管齐下方案一金仓数据库层面配置推荐在金仓的kingbase.conf配置文件中设置# 大小写不敏感配置 # 这个参数必须在数据库初始化时设置后期改需要重建数据库 # 设为on后金仓会像MySQL一样表名和字段名不区分大小写 # 注意这个参数对性能有微小影响但在大多数业务场景下可以忽略 enable_ci on方案二MyBatis-Plus层面配置如果数据库层面改不了比如生产环境已经初始化了可以在MyBatis-Plus里配置表名和字段名的策略// 全局配置表名和字段名自动转小写// 这样MyBatis-Plus生成的SQL里表名和字段名都会是小写// 配合金仓默认的小写存储就能匹配上ConfigurationpublicclassMybatisPlusConfig{BeanpublicGlobalConfigglobalConfig(){GlobalConfigconfignewGlobalConfig();GlobalConfig.DbConfigdbConfignewGlobalConfig.DbConfig();// 表名策略转小写// 为什么不用驼峰因为金仓默认存小写驼峰转下划线后还是小写dbConfig.setTableFormat(newLowerCaseFormat());// 字段名策略转小写dbConfig.setColumnFormat(newLowerCaseFormat());config.setDbConfig(dbConfig);returnconfig;}}方案三SQL层面加双引号最不推荐在XML里给每个表名和字段名加双引号!-- 这种写法就是给自己找不痛快几百个Mapper文件改到猴年马月 --!-- 而且加了双引号后金仓会严格区分大小写你必须保证大小写完全一致 --selectidselectUserresultTypeUserSELECT UserName FROM User_Info WHERE UserId #{id}/select老墨建议优先用方案一数据库层面配置其次用方案二ORM层面配置。方案三就是给自己挖坑别碰。第三关自增主键的背叛——从AUTO_INCREMENT到SEQUENCE3.1 问题根源MySQL和金仓的主键哲学MySQL用AUTO_INCREMENT表级别自增简单粗暴。人大金仓底层是PostgreSQL用SEQUENCE序列独立对象更灵活但也更复杂。虽然金仓的MySQL兼容模式支持AUTO_INCREMENT语法底层自动转成序列但在MyBatis-Plus层面如果你不配置好插入数据时会报错null value in column “id” violates not-null constraint。3.2 MyBatis-Plus的ID生成策略配置// 实体类配置DataTableName(user_info)publicclassUserInfo{// 关键配置IdType.AUTO// 为什么用AUTO而不是ASSIGN_ID雪花算法// 1. 如果业务允许用AUTO让数据库自增性能最好// 2. 如果用ASSIGN_IDMyBatis-Plus会在Java层生成ID不依赖数据库序列// 但这样会失去数据库自增的连续性雪花ID是18位长数字// 3. 如果原系统是MySQL自增ID迁移后建议保持AUTO保证ID连续性TableId(typeIdType.AUTO)privateLongid;privateStringusername;privateStringemail;privateLocalDateTimecreateTime;}3.3 金仓数据库层面的序列兼容如果你用IdType.AUTOMyBatis-Plus会生成类似这样的SQLINSERTINTOuser_info(username,email)VALUES(?,?)然后依赖数据库的自增机制。在金仓里你需要确保表的主键列绑定了序列-- 方式一使用SERIAL类型金仓MySQL兼容模式支持-- SERIAL会自动创建一个序列并绑定到该列-- 这是最接近MySQL AUTO_INCREMENT的写法CREATETABLEuser_info(idSERIALPRIMARYKEY,usernameVARCHAR(50),emailVARCHAR(100),create_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMP);-- 方式二手动创建序列并绑定更灵活适合复杂场景-- 如果你的表已经存在没有自增可以用这种方式补上CREATESEQUENCE user_info_id_seq;ALTERTABLEuser_infoALTERCOLUMNidSETDEFAULTnextval(user_info_id_seq);-- 绑定序列到列这样删除表时序列也会自动删除ALTERSEQUENCE user_info_id_seq OWNEDBYuser_info.id;老墨踩坑实录有一次迁移我用KDTS金仓数据迁移工具把MySQL表结构迁过去KDTS自动把AUTO_INCREMENT转成了序列。但插入数据时报错说序列的当前值太小和已有数据冲突。原因KDTS迁移数据后序列的当前值没有更新还是从1开始。而表里已经有1000条数据了插入时序列生成1和已有ID冲突。解决迁移完数据后必须重置序列的当前值-- 重置序列当前值为表中最大ID1-- 这个SQL在每次数据迁移后都必须执行不然插入必报错SELECTsetval(user_info_id_seq,(SELECTMAX(id)FROMuser_info));第四关SQL方言与函数暗坑——那些年我们写死的MySQL专属语法这是最考验零代码改造功力的地方。MySQL有很多专属函数和语法金仓的兼容模式能cover大部分但总有漏网之鱼。4.1 IFNULL vs COALESCEMySQLSELECTIFNULL(username,匿名用户)FROMuser_info金仓兼容模式支持IFNULL但推荐用标准SQL的COALESCE-- COALESCE是SQL标准函数MySQL、PostgreSQL、金仓、Oracle都支持-- 用COALESCE可以保证跨数据库兼容性SELECTCOALESCE(username,匿名用户)FROMuser_info老墨建议如果你的项目里大量使用了IFNULL在金仓兼容模式下一般能跑。但如果有嵌套或者复杂表达式建议全局替换成COALESCE。可以用IDE的全局替换功能一次性搞定。4.2 DATE_FORMAT vs TO_CHARMySQLSELECTDATE_FORMAT(create_time,%Y-%m-%d %H:%i:%s)FROMuser_info金仓兼容模式支持DATE_FORMAT但格式符有差异。金仓兼容模式下%Y、%m、%d等MySQL格式符会被自动转换。但推荐用标准写法-- TO_CHAR是PostgreSQL/金仓的标准日期格式化函数-- 格式符和MySQL不同YYYY-MM-DD HH24:MI:SSSELECTTO_CHAR(create_time,YYYY-MM-DD HH24:MI:SS)FROMuser_info如果不想改代码金仓的compatibleModemysql会自动处理DATE_FORMAT但性能可能略差因为要做语法转换。如果你的SQL里日期格式化很多建议还是改成TO_CHAR。4.3 GROUP_CONCAT vs STRING_AGGMySQLSELECTGROUP_CONCAT(username SEPARATOR,)FROMuser_infoGROUPBYdepartment金仓兼容模式金仓兼容模式支持GROUP_CONCAT但复杂场景如ORDER BY、DISTINCT可能不兼容。推荐用金仓原生的STRING_AGG-- STRING_AGG是PostgreSQL/金仓的标准聚合字符串函数-- 第一个参数是要聚合的列第二个参数是分隔符-- 支持ORDER BY和DISTINCT功能更强大SELECTSTRING_AGG(username,,ORDERBYusername)FROMuser_infoGROUPBYdepartment4.4 LIMIT分页语法MySQLSELECT*FROMuser_infoLIMIT10,20-- 或者SELECT*FROMuser_infoLIMIT20OFFSET10金仓兼容模式完全支持LIMIT ... OFFSET ...语法。但不支持LIMIT offset, count这种简写MySQL专属。如果你用了MyBatis-Plus的分页插件它会自动生成LIMIT count OFFSET offset的标准语法所以不用改代码。但如果你自己在XML里手写了LIMIT 10, 20必须改成LIMIT 20 OFFSET 10。4.5 UPSERTINSERT ON DUPLICATE KEY UPDATEMySQLINSERTINTOuser_info(id,username,email)VALUES(1,张三,zhangsanexample.com)ONDUPLICATEKEYUPDATEusernameVALUES(username),emailVALUES(email)金仓兼容模式金仓兼容模式支持ON DUPLICATE KEY UPDATE语法但底层实现和MySQL不同。金仓底层会转成PostgreSQL的INSERT ... ON CONFLICT ... DO UPDATE。推荐用金仓原生语法更稳定-- INSERT ... ON CONFLICT是PostgreSQL/金仓的标准UPSERT语法-- ON CONFLICT (id) 指定冲突检测的列必须是主键或唯一索引-- DO UPDATE SET 指定冲突时的更新逻辑-- EXCLUDED 是一个特殊表名代表试图插入但冲突的那行数据INSERTINTOuser_info(id,username,email)VALUES(1,张三,zhangsanexample.com)ONCONFLICT(id)DOUPDATESETusernameEXCLUDED.username,emailEXCLUDED.email第五关MyBatis/MyBatis-Plus的终极拦截——不改XML只改配置前面讲了那么多有些SQL语法差异是金仓兼容模式cover不了的而你又不想改XML里的SQL比如几百个Mapper文件改不动。这时候MyBatis拦截器Interceptor就是你的救命稻草。5.1 拦截器原理SQL的中间件MyBatis拦截器可以在SQL执行前拦截SQL语句进行动态改写。比如把MySQL的IFNULL替换成COALESCE把DATE_FORMAT替换成TO_CHAR。5.2 实战写一个SQL改写拦截器// MyBatis拦截器注解// 拦截Executor的query、update、flushStatements、commit、rollback方法// 这些方法涵盖了几乎所有SQL执行场景Intercepts({Signature(typeExecutor.class,methodupdate,args{MappedStatement.class,Object.class}),Signature(typeExecutor.class,methodquery,args{MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class})})ComponentpublicclassKingbaseSqlRewriteInterceptorimplementsInterceptor{privatestaticfinalLoggerlogLoggerFactory.getLogger(KingbaseSqlRewriteInterceptor.class);OverridepublicObjectintercept(Invocationinvocation)throwsThrowable{// 获取方法参数Object[]argsinvocation.getArgs();// 第一个参数是MappedStatement包含SQL的元信息MappedStatementms(MappedStatement)args[0];// 获取原始SQL从BoundSql中获取BoundSqlboundSqlms.getBoundSql(args[1]);StringoriginalSqlboundSql.getSql();// 调用SQL改写方法StringrewrittenSqlrewriteSql(originalSql);// 如果SQL被改写了需要用反射替换BoundSql中的sql字段// 为什么用反射因为BoundSql的sql字段是final的没有setter方法if(!originalSql.equals(rewrittenSql)){log.debug(SQL改写: {} - {},originalSql,rewrittenSql);// 通过反射修改BoundSql的sql字段FieldsqlFieldBoundSql.class.getDeclaredField(sql);sqlField.setAccessible(true);sqlField.set(boundSql,rewrittenSql);}// 继续执行原方法returninvocation.proceed();}/** * SQL改写逻辑 * 这里可以根据你的项目实际情况添加更多的替换规则 */privateStringrewriteSql(Stringsql){// 转大写方便匹配但不改变原SQL的大小写StringupperSqlsql.toUpperCase();// 1. 替换IFNULL为COALESCE// 注意这里用正则替换避免误替换比如字段名包含IFNULL// (?i)表示忽略大小写sqlsql.replaceAll((?i)\\bIFNULL\\s*\$,COALESCE();// 2. 替换DATE_FORMAT为TO_CHAR格式符也需要替换// 这个比较复杂因为格式符不同。这里只做简单的函数名替换// 格式符的替换需要更复杂的正则或者在数据库层面用兼容模式sqlsql.replaceAll((?i)\\bDATE_FORMAT\\s*\$,TO_CHAR();// 3. 替换GROUP_CONCAT为STRING_AGG// 注意GROUP_CONCAT的SEPARATOR语法和STRING_AGG不同// 这里只替换函数名SEPARATOR需要单独处理或者在兼容模式下让金仓处理sqlsql.replaceAll((?i)\\bGROUP_CONCAT\\s*\$,STRING_AGG();sqlsql.replaceAll((?i)\\bSEPARATOR\\s,, );// 4. 替换LIMIT offset, count 为 LIMIT count OFFSET offset// 正则匹配LIMIT 数字, 数字PatternlimitPatternPattern.compile((?i)LIMIT\\s(\\d)\\s*,\\s*(\\d));MatchermatcherlimitPattern.matcher(sql);if(matcher.find()){Stringoffsetmatcher.group(1);Stringcountmatcher.group(2);sqlmatcher.replaceFirst(LIMIT count OFFSET offset);}returnsql;}OverridepublicObjectplugin(Objecttarget){// 只拦截Executor类型的对象if(targetinstanceofExecutor){returnPlugin.wrap(target,this);}returntarget;}OverridepublicvoidsetProperties(Propertiesproperties){// 可以从配置文件中读取自定义属性// 比如在application.yml中配置mybatis.interceptor.rewritetrue}}5.3 MyBatis-Plus的分页插件配置如果你用了MyBatis-Plus的分页插件必须配置正确的数据库类型ConfigurationpublicclassMybatisPlusConfig{BeanpublicMybatisPlusInterceptormybatisPlusInterceptor(){MybatisPlusInterceptorinterceptornewMybatisPlusInterceptor();// 分页插件配置PaginationInnerInterceptorpaginationInterceptornewPaginationInnerInterceptor();// 数据库类型金仓推荐配置为POSTGRE_SQL// 为什么不用KINGBASE_ES因为MyBatis-Plus官方没有内置金仓类型// POSTGRE_SQL的分页语法和金仓完全一致LIMIT ... OFFSET ...paginationInterceptor.setDbType(DbType.POSTGRE_SQL);// 最大单页限制条数防止恶意查询paginationInterceptor.setMaxLimit(500L);interceptor.addInnerInterceptor(paginationInterceptor);// 乐观锁插件如果项目用了乐观锁interceptor.addInnerInterceptor(newOptimisticLockerInnerInterceptor());returninterceptor;}}尾声信创迁移的血泪教训6.1 三个千万不要千万不要相信零代码改造的鬼话产品经理说的零代码是指业务代码不改。但配置文件、SQL方言、拦截器、甚至部分Mapper XML该改还是得改。把期望管理做好别给自己挖坑。千万不要在生产环境直接切库必须经过开发环境验证 → 测试环境全量回归 → 预发环境压测 → 生产环境灰度切流。金仓的KDTS工具支持数据同步可以做双写验证。千万不要忽略性能测试金仓和MySQL的查询优化器不同同样的SQL执行计划可能完全不同。迁移后必须做全量SQL的性能回归特别是复杂查询和分页查询。有些SQL在MySQL里走索引在金仓里可能全表扫描。6.2 老程序员的宿命写到最后我想说信创迁移这事儿技术难度不是最高的最折磨人的是期望管理。老板觉得换个数据库而已很简单。产品经理觉得代码不用改明天上线。测试觉得功能没变不用全量回归。只有老码农知道这背后是无数个凌晨三点的报警是几百个Mapper文件的逐行排查是序列重置、大小写敏感、函数兼容这些隐形地雷的逐个排雷。但这就是我们的宿命——把复杂的事情做简单把不可能的事情变成其实也没那么难。当你看着系统在金仓上稳定运行RT从500ms降到50ms产品经理终于不半夜给你发在吗的时候你会觉得这杯冰美式值了。6.3 彩蛋金仓迁移Checklist最后送你一份我总结的金仓迁移Checklist打印出来贴在工位上□ 金仓数据库初始化时开启compatibleModemysql □ 金仓数据库初始化时配置enable_cion大小写不敏感 □ JDBC URL加上compatibleModemysql和stringtypeunspecified □ MyBatis-Plus配置db-type为POSTGRE_SQL □ 分页插件DbType设为POSTGRE_SQL □ 数据迁移后重置所有序列的当前值 □ 全局检查IFNULL、DATE_FORMAT、GROUP_CONCAT等MySQL专属函数 □ 全局检查LIMIT offset, count语法改为LIMIT count OFFSET offset □ 配置SQL改写拦截器兜底方案 □ 全量SQL性能回归测试 □ 生产环境灰度切流保留MySQL回滚方案