json数据库(抽丝剥茧探穷境！一次数据库JSON字段的深度使用实践)

json数据库(抽丝剥茧探穷境！一次数据库JSON字段的深度使用实践)
抽丝剥茧探穷境！一次数据库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数据库(抽丝剥茧探穷境！一次数据库JSON字段的深度使用实践)

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_stockset 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本身存在问题。


                        WHEN                        THEN json_set(extend_content, #{item.stockWeightJsonPath, jdbcType=VARCHAR}, IFNULL(extend_content ->> #{item.stockWeightJsonPath, jdbcType=VARCHAR}, 0) + IFNULL(#{item.stockWeight, jdbcType=DECIMAL}, 0))


结合代码来看，入参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 ：





不禁感慨，条条大路通罗马，并非所有的路都一帆风顺，但我们终究抵达终点。


文章版权声明：除非注明，否则均为边学边练网络文章，版权归原作者所有

相关阅读

最新文章

热门文章

前端开发顺序(零基础怎么学习前端？前端学习路线分享)

149 阅读 ，

sql数据库工具(7款主流sql工具大盘点！)

146 阅读 ，

web前端开发流程(web前端开发培训怎么入门学习)

128 阅读 ，

本栏目文章

rds数据库(亚马逊云渠道商：RDS 6 大引擎解析 + 成本优化实战)
数据库技术2026-02-23
彭博数据库(抢占现货市场定价权“华南铝价”在大沥发布)
数据库技术2026-03-12
DNA数据库(33年没找到的弟弟，AI人脸识别找到了！上万名筛查，DNA一锤定音)
数据库技术2026-04-04
数据库保护问题包括(Oracle 数据库保护解决方案 确保备份资料零遗失)
数据库技术2026-01-20
mongodb数据库(关于MongoDB数据库存在严重安全隐患预警的通报)
数据库技术2026-02-17
uci数据库(不平衡数据加权集成学习算法)
数据库技术2026-02-26