MySQL JSON 数据操作

注意事项EF Core 版本EF.Functions.JsonContains 方法需要 EF Core 5.0 或更高版本并且数据库需要支持 JSON 函数。MySQL 提供程序确保安装了 Pomelo.EntityFrameworkCore.MySql 或 Oracle.MySql.EntityFrameworkCore 提供程序。*、CASTSELECT CAST({name: John, age: 30} AS JSON)*、JSON_CONTAINS-- 示例1 SELECT * FROM SaleOrdersDetails WHERE 11 AND Risk AND JSON_CONTAINS(CAST(Risk AS JSON), {itemCode: applyno, itemValue: BH20260206111111}) ; -- 示例2 WHERE 11 AND JSON_CONTAINS(jsonObject1, JSON_OBJECT(itemValue, BH20260206111111)); AND JSON_CONTAINS(jsonObject2, JSON_OBJECT(name, John));******、表结构SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0; -- ---------------------------- -- Table structure for SaleOrdersDetails -- ---------------------------- DROP TABLE IF EXISTS SaleOrdersDetails; CREATE TABLE SaleOrdersDetails ( Id char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL, TenantId char(36) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT NULL, OrderId char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 订单编号, Risk longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT 标的信息相关内容, Refund longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT 退保相关内容, Other longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT 其他信息, PRIMARY KEY (Id) USING BTREE, INDEX IX_SaleOrdersDetails_OrderId(OrderId ASC) USING BTREE ) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic; SET FOREIGN_KEY_CHECKS 1;*、risk字段类型是 longtext 字符串类型存储的字符串内容是json数组格式的字符串[ { itemText: , itemCode: applyno, itemValue: BH20260206111111, itemValueDesc: }, { itemText: , itemCode: enter_institution_type, itemValue: 6, itemValueDesc: }, { itemText: , itemCode: bank_id, itemValue: 100, itemValueDesc: } ]*、SQL查询语句-- 注意 注意 注意 -- Risk 一定要写在前面要不然会报错 SELECT Id,OrderId,Risk,Refund,Other,PayUrl,PolicyUrl,InvoiceUrl,GuaranteeLetterUrl ,CAST(Risk AS JSON) AS js11onject FROM SaleOrdersDetails WHERE 11 AND Risk AND JSON_CONTAINS(CAST(Risk AS JSON), JSON_OBJECT(itemValue, BH20260206111111)); LIMIT 0,1000 -- SELECT * FROM SaleOrdersDetails WHERE 11 AND Risk AND JSON_CONTAINS(CAST(Risk AS JSON), {itemCode: applyno, itemValue: BH20260206111111}) ;***