业务表对应主键为varChar类型且无自增策略的新增与修改问题

上周五领到一个需求，在开发过程中遇到一点小插曲，在此记录下，话不多说，直接看表结构：

这里机构id为主键且无自增策略，也就是说机构id这个字段是通过手动输入保存的，还有这个机构DES密钥的问题（在此处花费了较长时间）。下面直接看代码（使用SpringBoot+Vue+Mybatis / Mybatis plus）:

这段代码可以看到修改或新增提交from表单时是通过branchId（主键）来判断具体走新增还是更新，所以此套逻辑必定不行。
于是做了修改，下面为修改后的代码：

/** 新增按钮操作 */handleAdd() {this.reset();this.open = true;this.title = "添加机构信息管理";this.flag=true;       //新增绑定true标识},/** 修改按钮操作 */handleUpdate(row) {this.flag=false;     //新增绑定true标识this.reset();const branchId = row.branchId || this.idsgetWalletInfo(branchId).then(response => {this.form = response.data;this.open = true;this.title = "修改机构信息管理";});},

修改提交form表单逻辑（此时通过绑定的flag标识进行逻辑判断）：

/** 提交按钮 */
submitForm() {this.$refs["form"].validate(valid => {if (valid) {if (!this.flag) {updateWalletInfo(this.form).then(response => {this.$modal.msgSuccess("修改成功");this.open = false;this.getList();});} else {addWalletInfo(this.form).then(response => {this.$modal.msgSuccess("新增成功");this.open = false;this.getList();});}}});
},

解决了功能问题还有一些细节需要处理。修改时需要将branchId（主键）置为不可编辑状态：

效果：

做完以上操作后，尝试对数据进行修改时，结果又产生报错，以下为报错信息：

	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:106)at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)at com.sun.proxy.$Proxy164.update(Unknown Source)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)at com.sun.proxy.$Proxy164.update(Unknown Source)at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)... 146 common frames omitted
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "3" <S_LONG>at line 9, column 13.Was expecting one of:"ACTION""ANY""BYTE""CASCADE""CAST""CHANGE""CHAR""CHARACTER""COLUMN""COLUMNS""COMMENT""COMMIT""CREATE""CYCLE""DESC""DESCRIBE""DISABLE""DIV""DO""DOUBLE""DUPLICATE""ENABLE""END""EXCLUDE""EXTRACT""FALSE""FIRST""FN""FOLLOWING""FORMAT""GROUP""IF""INDEX""INSERT""INTERVAL""ISNULL""KEY""LAST""LEFT""LIMIT""MATERIALIZED""NEXTVAL""NO""NOLOCK""NULLS""OF""OFFSET""ON""OPEN""OPTIMIZE""ORDER""OVER""PARTITION""PATH""PERCENT""PRECISION""PRIMARY""PRIOR""PROCEDURE""PUBLIC""RANGE""READ""REPLACE""RIGHT""ROW""ROWS""SCHEMA""SEPARATOR""SEQUENCE""SESSION""SET""SIBLINGS""SIZE""START""TABLE""TABLES""TEMP""TEMPORARY""TO""TOP""TRUE""TRUNCATE""TYPE""UNSIGNED""VALIDATE""VALUE""VALUES""VIEW""XML""ZONE"<K_DATETIMELITERAL><K_DATE_LITERAL><S_IDENTIFIER><S_QUOTED_IDENTIFIER>at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:26538)at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:26377)at net.sf.jsqlparser.parser.CCJSqlParser.RelObjectNameExt(CCJSqlParser.java:3882)at net.sf.jsqlparser.parser.CCJSqlParser.RelObjectNameList(CCJSqlParser.java:3135)at net.sf.jsqlparser.parser.CCJSqlParser.Column(CCJSqlParser.java:3173)at net.sf.jsqlparser.parser.CCJSqlParser.Update(CCJSqlParser.java:1138)at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:138)at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:502)at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:186)at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:176)at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserMulti(JsqlParserSupport.java:63)... 168 common frames omitted
14:42:32.410 [http-nio-8899-exec-6] WARN  o.s.w.s.m.m.a.ExceptionHandlerExceptionResolver - [logException,208] - Resolved [org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: <EOL><EOL>### Error updating database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: update wallet_branch_info<EOL>         SET CUST_NO = ?,<EOL>            BRANCH_NAME = ?,<EOL>            PARENT_BRANCH_ID = ?,<EOL>            STATUS = ?,<EOL>            OPEN_DATE = ?,<EOL>            OPEN_TIME = ?,<EOL>            MD5_KEY = ?,<EOL>            3DES_KEY = ?,<EOL>            LINK_NAME = ?,<EOL>            MOBILE_PHONE = ?,<EOL>            E_MAIL = ? <EOL>        where BRANCH_ID = ?<EOL><EOL>### The error may exist in file [D:\hzsr\platform-business\target\classes\mapper\business\WalletBranchInfoMapper.xml]<EOL><EOL>### The error may involve com.horzits.business.mapper.WalletBranchInfoMapper.updateWalletBranchInfo<EOL><EOL>### The error occurred while executing an update<EOL><EOL>### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: update wallet_branch_info<EOL>         SET CUST_NO = ?,<EOL>            BRANCH_NAME = ?,<EOL>            PARENT_BRANCH_ID = ?,<EOL>            STATUS = ?,<EOL>            OPEN_DATE = ?,<EOL>            OPEN_TIME = ?,<EOL>            MD5_KEY = ?,<EOL>            3DES_KEY = ?,<EOL>            LINK_NAME = ?,<EOL>            MOBILE_PHONE = ?,<EOL>            E_MAIL = ? <EOL>        where BRANCH_ID = ?]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3bf20f8d] was not registered for synchronization because synchronization is not active
JDBC Connection [ConnectionProxyImpl{connectedTime=2024-06-17 14:39:22.55, closeCount=0, lastValidateTimeMillis=2024-06-17 14:42:21.375}] will not be managed by Spring
==>  Preparing: insert into sys_oper_log(title, business_type, method, request_method, operator_type, oper_name, dept_name, oper_url, oper_ip, oper_location, oper_param, json_result, status, error_msg, cost_time, oper_time) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate())
==> Parameters: 机构信息管理(String), 2(Integer), com.horzits.business.controller.WalletBranchInfoController.edit()(String), PUT(String), 1(Integer), admin(String), 研发部门(String), /business/walletInfo(String), 127.0.0.1(String), 内网IP(String), {"_3desKey":"2","branchId":"2","branchName":"2","custNo":"21111","email":"2","linkName":"2","md5Key":"2","mobilePhone":"2","openDate":"2个人","openTime":"2","parentBranchId":"2","status":"0"}(String), null, 1(Integer), nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: update wallet_branch_infoSET CUST_NO = ?,BRANCH_NAME = ?,PARENT_BRANCH_ID = ?,STATUS = ?,OPEN_DATE = ?,OPEN_TIME = ?,MD5_KEY = ?,3DES_KEY = ?,LINK_NAME = ?,MOBILE_PHONE = ?,E_MAIL = ? where BRANCH_ID = ?
### The error may exist in file [D:\hzsr\platform-business\target\classes\mapper\business\WalletBranchInfoMapper.xml]
### The error may involve com.horzits.business.mapper.WalletBranchInfoMapper.updateWalletBranchInfo
### The error occurred while executing an update
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: update wallet_branch_infoSET CUST_NO = ?,BRANCH_NAME = ?,PARENT_BRANCH_ID = ?,STATUS = ?,OPEN_DATE = ?,OPEN_TIME = ?,MD5_KEY = ?,3DES_KEY = ?,LINK_NAME = ?,MOBILE_PHONE = ?,E_MAIL = ? where BRANCH_ID = ?(String), 12(Long)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3bf20f8d]

以下为xml映射：

<update id="updateWalletBranchInfo" parameterType="WalletBranchInfo">update wallet_branch_info<trim prefix="SET" suffixOverrides=","><if test="custNo != null and custNo != ''">CUST_NO = #{custNo},</if><if test="branchName != null and branchName != ''">BRANCH_NAME = #{branchName},</if><if test="parentBranchId != null and parentBranchId != ''">PARENT_BRANCH_ID = #{parentBranchId},</if><if test="status != null and status != ''">STATUS = #{status},</if><if test="openDate != null and openDate != ''">OPEN_DATE = #{openDate},</if><if test="openTime != null and openTime != ''">OPEN_TIME = #{openTime},</if><if test="md5Key != null and md5Key != ''">MD5_KEY = #{md5Key},</if><if test="_3desKey != null and _3desKey != ''">3DES_KEY = #{_3desKey},</if><if test="linkName != null and linkName != ''">LINK_NAME = #{linkName},</if><if test="mobilePhone != null and mobilePhone != ''">MOBILE_PHONE = #{mobilePhone},</if><if test="email != null and email != ''">E_MAIL = #{email},</if></trim>where BRANCH_ID = #{branchId}
</update>

根据报错检查映射以及参数占位等等问题均无果。以下为xml映射，尝试将这一行删掉：

 <if test="_3desKey != null and _3desKey != ''">3DES_KEY = #{_3desKey},</if>

结果便可以修改成功。至此原因找到，得出以下总结：使用Mybatis / Mybatis plus 在编译解析xml中sql语句时，无法编译解析数字开头的字段。

解决方法：（使用``将特殊字段包裹起来）