抽丝剥茧探穷境！一次数据库JSON字段的深度使用实践
背景
在我们系统中，承接多种行业，多种商家的，多个业务条线，不同场景的的不同业务诉求，在实现上将个性和通用字段隔离，通用字段是所有条线通用逻辑所共用的标准字段，对于个别条线的个性化诉求，则通过个性化扩展字段来实现。
通用字段，作用于通用逻辑，所有条线走到相应功能时，会对通用字段读写。而对于个性扩展字段而言，只有用到个性化功能时，才会对个性化扩展字段进行赋值。在数据库持久化存储上，用不到该个性化扩展字段时，该字段无需存储，不占用额外存储空间。
在表结构上，使用JSON类型字段进行存储。在日常扩展字段的开发过程中，不可避免的用到JSON函数进行字段处理。
常用JSON函数一览
| Name | Description |
|---|---|
-> | Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT(). |
->> | Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()). |
JSON_APPEND() | Append data to JSON document |
JSON_ARRAY() | Create JSON array |
JSON_ARRAY_APPEND() | Append data to JSON document |
JSON_ARRAY_INSERT() | Insert into JSON array |
JSON_CONTAINS() | Whether JSON document contains specific object at path |
JSON_CONTAINS_PATH() | Whether JSON document contains any data at path |
JSON_DEPTH() | Maximum depth of JSON document |
JSON_EXTRACT() | Return data from JSON document |
JSON_INSERT() | Insert data into JSON document |
JSON_KEYS() | Array of keys from JSON document |
JSON_LENGTH() | Number of elements in JSON document |
JSON_MERGE() | Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE() |
JSON_MERGE_PATCH() | Merge JSON documents, replacing values of duplicate keys |
JSON_MERGE_PRESERVE() | Merge JSON documents, preserving duplicate keys |
JSON_OBJECT() | Create JSON object |
JSON_PRETTY() | Print a JSON document in human-readable format |
JSON_QUOTE() | Quote JSON document |
JSON_REMOVE() | Remove data from JSON document |
JSON_REPLACE() | Replace values in JSON document |
JSON_SEARCH() | Path to value within JSON document |
JSON_SET() | Insert data into JSON document |
JSON_STORAGE_SIZE() | Space used for storage of binary representation of a JSON document |
JSON_TYPE() | Type of JSON value |
JSON_UNQUOTE() | Unquote JSON value |
JSON_VALID() | Whether JSON value is valid |
JSON字段实现项目场景诉求典型案例分析
JSON字段对我们系统来说并不陌生，有不少场景使用，下面有一些典型的使用场景案例先放出来作为JSON字段项目使用背景铺垫一下。
扩展字段的存储和查询
动态扩展查询案例
在二手库或逆向条件，有比较多的扩展属性，其他条线缺没有这些属性值，这些属性以JSON扩展的方式存储。
下面是一个JSON扩展字段的JSON数据示例:
{ "ppCode": "PPDA4302865239B10F", "zoneNo": "STAGE-OUTBOUND", "zoneName": "出库暂存区", "zoneType": "t", "extendMap": { "cid1": 13765, "cid2": 14192, "cid3": 14533, "deptId": "1", "jdFlag": 1, "symbol": 300, "brandId": 52368, "cbjPrice": 2034, "salesPin": "xumingchen", "commonType": 0, "wareSource": 100, "orderSource": "it100", "supplierCode": "lgde", "outTransferId": "1284009718", "inboundSourceType": 100, "purchaseChannelId": "1001" }, "storeCode": "", "sourceModule": "wms-pick", "isolationZone": false, "stageZoneType": "OUTBOUND" }这些扩展字段支持作为查询条件过滤库存，用于出库建单。
在服务层面为了支持动态增减的属性条件，条件字段定义为Map类型，与 JSON 字段中的 extendMap 所对应。
list查询条件的特殊预处理
SQL层面的动态查询效果：
元素包含条件使用案例
设备组条件
批属性条件
序列号条件
数值管理深度剖析案例
案例背景
在现有的场景中，目前仅华冠场景支持重量库存，其他条线暂无库存管理。在本案例中，重量库存通过个性化扩展字段实现，JSON类型。
JSON类型的字段名是 extend_content，其中重量字段 stockWeight 的 JSON path是 $.stockInfo.stockWeight 。
stockInfo 的内容示例如下：
{ "businessNo": "OT2008735812539129856", "stockWeight": 630, "businessType": "WMS_PICK", "businessTypeName": "出库拣货" }增减重量库存
单行更新
通过CASE WHEN THEN 方式批量更新JSON字段中的重量库存
剥茧第一层
在调试中，发现JSON字段，如果想要通过 json_set 赋值，必须依赖于该字段不能为null，如果json_set 该JSON字段值为null，则更新不上，不会产生SQL语法错误。
对于存量的历史null值字段统计，并做一次初始化。
初始化历史数据
统计JSON字段extend_content值为null 的数据
将 extend_content 为 null 的进行初始化
统计JSON字段extend_content值不为null，但其中的stockInfo为null的数据
将 extend_content.stockInfo 为 null 的数据进行初始化
对存量的历史数据初始化完毕，考虑到某些场景可能还会持续产生 JSON 字段 null值情况，考虑在代码中进行兼容，如果想在 JSON 字段中进行 json_set，SQL进行检查并自动进行前置初始化。
以上SQL，通过JSON_OBJECT函数将null值的JSON字段，初始化为一个空JSON对象，肉眼看上去是 {}。
剥茧第二层
除了上面历史null值的JSON字段外，还发现一个更为复杂的场景，JSON字段更新前不为null，但是本次赋值更新操作却导致JSON字段整体变成了null，匪夷所思！
单行更新方式因为是一次是更新一行，没有问题。
批量更新，如果所有的明细stockWeight字段都有值，更新也没问题。如果一次批量更新的明细中，有的stockWeight字段有值，有的没有值，则更新会有问题，不会出现SQL语法错误，但是整个extend_content JSON字段会被错误地更新为null。
在测试环境中，调试的一个入参如下：
{ "requestHeader": { "sourceModule": "inventory", "requestIp": "11.50.45.137", "warehouseNo": "6_6_618", "businessType": "INV-CHANGE-PROFIT-LOSS", "businessTypeName": "盘盈亏", "businessNo": "CP2009231067168407553", "uuid": "wms.inv.change.profit.6_6_618.CP2009231067168407553.2009231067554283520.12", "operator": "guozhongqiang5" }, "increaseStockDetailList": [ { "detailBusinessNo": "DPPT20092299369586442242", "stockLocationIndicator": { "locationNo": "01", "containerLevel1": "", "containerLevel2": "" }, "stockSkuIndicator": { "sku": "EMG172002001", "lotNo": "-1", "skuLevel": "100", "packCode": "8c59689e8972a14e4883b0ea755b3702", "ownerNo": "EBU4398046536982" }, "increaseOperateType": "normalIncrease", "stockQty": 1.0000, "recommendLocationNo": "", "externalNo": "CP2009231067168407553", "uniqueStockList": [], "reason": {} }, { "detailBusinessNo": "DPPT2009229936958644224", "stockLocationIndicator": { "locationNo": "01", "containerLevel1": "", "containerLevel2": "" }, "stockSkuIndicator": { "sku": "EMG172002003", "lotNo": "-1", "skuLevel": "100", "packCode": "8c59689e8972a14e4883b0ea755b3702", "ownerNo": "EBU4398046536982" }, "increaseOperateType": "normalIncrease", "stockQty": 0.0000, "stockWeight": 3000, "recommendLocationNo": "", "externalNo": "CP2009231067168407553", "uniqueStockList": [], "reason": {} } ] }我们可以看到，上面这个入参有两个明细，对应的SKU分别是EMG172002001 和 EMG172002003。第一个明细只有stockQty字段无stockWeight字段，第二个同时有stockQty字段，和stockWeight字段，stockWeight值为 3000。
此时，批量更新形成的SQL如下：
UPDATE st_stock set stock_qty = case WHEN deleted = 0 AND warehouse_no = '6_6_618' AND id = 2008872347100020736 AND status = 0 THEN stock_qty + 1.0000 WHEN deleted = 0 AND warehouse_no = '6_6_618' AND id = 2008872388812374016 AND status = 0 THEN stock_qty + 0.0000 end, extend_content = case WHEN deleted = 0 AND warehouse_no = '6_6_618' AND id = 2008872388812374016 AND status = 0 THEN JSON_SET(COALESCE(extend_content, '{}'), '$.stockInfo', COALESCE(JSON_EXTRACT(extend_content, '$.stockInfo'), JSON_OBJECT())) ELSE extend_content end, extend_content = case WHEN deleted = 0 AND warehouse_no = '6_6_618' AND id = 2008872388812374016 AND status = 0 THEN json_set(extend_content, '$.stockInfo.stockWeight', IFNULL(extend_content ->> '$.stockInfo.stockWeight', 0) + IFNULL(3000.0, 0)) end, update_time = now(), version = version + 1, update_user = 'guozhongqiang5', extend_content = json_set(extend_content, '$.stockInfo.businessType', 'INV-CHANGE-PROFIT-LOSS', '$.stockInfo.businessTypeName', '盘盈亏', '$.stockInfo.businessNo', 'CP2009231067168407553333') WHERE ( deleted = 0 AND warehouse_no = '6_6_618' AND id = 2008872347100020736 AND status = 0 ) or ( deleted = 0 AND warehouse_no = '6_6_618' AND id = 2008872388812374016 AND status = 0 )问题出现下图圈红的部分：
在上面的场景中，一次批量更新有两个明细，其中一个因为stockWeight为null，不会被动态拼接到SQL中，这个无对应的 WHEN THEN 则 extend_content 被置为null，另一个明细因为有 WHEN THEN 则赋值正常。
此时直观的想法是，既然因为stockWeight为null的明细走不上 WHEN THEN ，何不通过 ELSE 赋值为extend_content 本身来解决呢。按此思路进行代码调整如下：
调试明细stockWeigh不为空，验证通过。
剥茧第三层
但是，当所有明细stockWeight为null的时候，因为 ELSE extend_content 在 if条件满足的时候才会动态拼接，当，此时 WHEN THEN 和 ELSE都不会拼接进去，语法不通过。
那么，把 ELSE extend_content 从 if 条件判断拿出来，放在 end的前面总可以了吧。
这里借助 foreach 的 close 来拼接 ELSE extend_content 语句。
在测试环境验证时，当更新入参至少一个明细中的 stockWeight 不为null时，确实没问题。当所有明细的stockWeight都为null时，新的问题来了，报错信息及分析过程如下：
### The error occurred while setting parameters ### SQL: UPDATE st_stock set stock_qty=case WHEN deleted = 0 AND warehouse_no = ? AND id = ? and stock_qty >= ? * -1 THEN stock_qty + ? WHEN deleted = 0 AND warehouse_no = ? AND id = ? and stock_qty >= ? * -1 THEN stock_qty + ? end, diff_qty=case WHEN deleted = 0 AND warehouse_no = ? AND id = ? and diff_qty >= ? * -1 THEN diff_qty + ? WHEN deleted = 0 AND warehouse_no = ? AND id = ? and diff_qty >= ? * -1 THEN diff_qty + ? end, extend_content=case ELSE extend_content end, extend_content=case ELSE extend_content end, update_time = now() , version = version + 1 , update_user = ? , extend_content = json_set(extend_content, ?, ?, ?, ?, ?, ?) WHERE ( deleted = 0 AND warehouse_no = ? AND id = ? and diff_qty >= ? * -1 AND status = 0 ) or ( deleted = 0 AND warehouse_no = ? AND id = ? and diff_qty >= ? * -1 AND status = 0 ) /* [SQLMarking] statementId: com.jdwl.wms.stock.infrastructure.jdbc.main.dao.StockOperationDao.simpleDecreaseDiffQty */ ### Cause: com.jdbc.exceptions.jdbc4.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near 'ELSE extend_content end, extend_content=case ELSE extend_content' at line 36 ; bad SQL grammar []; nested exception is com.sql.jdbc.exceptions.jdbc4.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near 'ELSE extend_content end,入参
{ "requestHeader": { "sourceModule": "inventory", "requestIp": "11.158.12.73", "warehouseNo": "6_6_618", "businessType": "INV-PROFIT-LOSS-TRANSFER-STOCK-MATCH", "businessTypeName": "盘盈、盘亏单预占转移", "businessNo": "CL2009892868717576192", "uuid": "wms.inv.change.lock.transfer.loss.6_6_618.2009892868977623040.0", "operator": "guozhongqiang5" }, "stockLockTransformType": "difference2Change", "stockLockTransformDetailList": [ { "srcBusinessNo": "DPPT2009892261415911424", "destBusinessNo": "CL2009892868717576192", "stockSkuIndicator": { "sku": "EMG172002002", "lotNo": "-1", "skuLevel": "100", "packCode": "8c59689e8972a14e4883b0ea755b3702", "ownerNo": "EBU4398046536982" }, "stockLocationIndicator": { "locationNo": "01", "containerLevel1": "", "containerLevel2": "" }, "qty": 2 }, { "srcBusinessNo": "DPPT2009892261415911424", "destBusinessNo": "CL2009892868717576192", "stockSkuIndicator": { "sku": "EMG172002004", "lotNo": "-1", "skuLevel": "100", "packCode": "8c59689e8972a14e4883b0ea755b3702", "ownerNo": "EBU4398046536982" }, "stockLocationIndicator": { "locationNo": "01", "containerLevel1": "", "containerLevel2": "" }, "qty": 4 } ], "serialLockTransformDetailList": [ ] }入参本身没什么问题，是符合要求的入参，从本次需求的特征上来看只是没有重量字段，重量字段非必填，也不是必须的，在很多场景下也不涉及重量库存的变化。
数据
数据库中的待更新数据本身也没有问题。
到这里，已经确认入参是正常场景，待更新数据也没问题，是SQL本身存在问题。
<trim prefix="extend_content=case" suffix="end,"> <foreach collection="list" item="item" index="index" close="ELSE extend_content"> <if test="item.stockWeight != null and item.stockWeight != 0"> WHEN <include refid="simpleCommonIncreaseClauseOfItem"/> THEN json_set(extend_content, #{item.stockWeightJsonPath, jdbcType=VARCHAR}, IFNULL(extend_content ->> #{item.stockWeightJsonPath, jdbcType=VARCHAR}, 0) + IFNULL(#{item.stockWeight, jdbcType=DECIMAL}, 0)) </if> </foreach> </trim>结合代码来看，入参stockWeight为null，则 if 条件里的 WHEN THEN 拼不进去，但 close 部分的 ELSE extend_content 可以拼入，结合前面prefix的 extend_content=case 和 suffix 的 end, 连在一起就是:
extend_content=case ELSE extend_content end,显然，这个语法不通过的。
解决方法也比较简单，就是把 ELSE 语句改成 WHEN THEN 语句，具体如下：
这样即使if语句不拼接进去，整体拼出来的语句如下，也是符合预期的：
extend_content=case WHEN 1=1 THEN extend_content end,至此，批量更新所有明细全有stockWeight，全无stockWeight，部分有部分无stockWeight的场景，均已支持完毕。
再来一层
回过头看，还有另外一个思路处理更为简单，不用 CASE WHEN THEN 方式新写一个批量更新方法：
这种批量更新方式，依赖于开启 allowMultiQueries ：
不禁感慨，条条大路通罗马，并非所有的路都一帆风顺，但我们终究抵达终点。