sql语句(mysql中json_contains、json_array的使用)

本文介绍了一种利用科大讯飞语音识别技术获取中文分词后的文字,并演示了如何通过SQL查询从数据库中筛选出含有指定关键词的数据记录。特别展示了使用JSON_CONTAINS及LIKE操作符的不同方式。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1、用科大讯飞语音识别到的已经中文分词的文字,例:“我”“想”“销”“存单”
2、根据上面语音识别出来的多个关键字,在数据表中查找出包含上述四个关键字中任意若干个数据。

表中数据大概这样:
这里写图片描述

要从此表中匹配到包含上述四个关键字中的任意若干个,可这样:

SELECT * FROM nlu_define_table WHERE JSON_CONTAINS(JSON_ARRAY("我","想","销","存单"),keywords->'$.keywords') ORDER BY weights DESC;

即可搜索到数据
这里写图片描述


另附上部分sql,以防之后忘记

SELECT * FROM nlu_define_table;
SELECT * FROM nlu_define_table WHERE keywords LIKE "%销%";
SELECT * FROM nlu_define_table WHERE JSON_EXTRACT(keywords,'$.keywords') LIKE "%销%";
SELECT * FROM nlu_define_table WHERE keywords -> '$.keywords' LIKE "%销%";

其中第三条sql和第四条sql是等效的。。。

<think>我们正在处理一个MySQL查询条件,该条件用于检查当前用户是否有权访问某些数据。条件如下:(create_user_id=#{currentUserId}ORJSON_CONTAINS(duty_user,CAST(#{currentUserId}ASJSON))解释:1.`create_user_id=#{currentUserId}`:表示当前用户是数据的创建者。2.`JSON_CONTAINS(duty_user,CAST(#{currentUserId}ASJSON)`:表示当前用户ID在`duty_user`这个JSON字段所包含的数组中。注意:`duty_user`字段应该是一个JSON数组,例如`[1,2,3]`,存储的是负责该数据的用户ID列表。但是,这里有一个潜在的问题:`CAST(#{currentUserId}ASJSON)`可能会将整数类型的用户ID转换为JSON数字,但有时我们可能需要将其转换为JSON字符串(如果duty_user中存储的是字符串形式的ID)。因此,我们需要确保存储的ID类型与转换后的类型一致。另外,`JSON_CONTAINS`函数的第二个参数必须是JSON类型,因此我们使用CAST将当前用户ID(可能是整数或字符串)转换为JSON类型。示例:假设`currentUserId`为123,那么转换后的表达式为:(create_user_id=123ORJSON_CONTAINS(duty_user,CAST(123ASJSON))如果`duty_user`字段存储的是`[123,456]`,那么`JSON_CONTAINS`将返回真。但是,如果`duty_user`存储的是`["123","456"]`(字符串数组),那么`JSON_CONTAINS`将不会匹配,因为数字123和字符串"123"在JSON中是不同的类型。因此,我们需要确保在存储时,`duty_user`数组中的元素类型与传入的`currentUserId`转换后的类型一致。如果`duty_user`中存储的是字符串,那么我们应该将`currentUserId`转换为字符串再转为JSON字符串,例如:JSON_CONTAINS(duty_user,CAST('123'ASJSON))或者,在应用层确保传入的`currentUserId`转换为字符串形式。在MyBatis或类似框架中,我们可以这样处理:<iftest="currentUserId!=null">(create_user_id=#{currentUserId}ORJSON_CONTAINS(duty_user,CAST(CONCAT('"',#{currentUserId},'"')ASJSON))</if>这样,我们将整数ID转换为带双引号的字符串(即JSON字符串)。例如,如果`currentUserId`是123,那么`CONCAT('"',#{currentUserId},'"')`会生成`"123"`,然后将其转换为JSON字符串。但是,这种方法可能不够优雅,并且如果ID本身是字符串,那么直接使用字符串形式即可。另一种方法是,在应用层将当前用户ID转换为字符串,并确保`duty_user`中存储的是字符串形式的ID,然后使用:ORJSON_CONTAINS(duty_user,CAST(#{currentUserId}ASCHAR))但是,这样转换可能并不正确,因为`CAST(#{currentUserId}ASCHAR)`会生成一个字符串,但`JSON_CONTAINS`要求第二个参数是JSON类型。所以,我们可以使用JSON_QUOTE函数来生成一个JSON字符串:ORJSON_CONTAINS(duty_user,JSON_QUOTE(#{currentUserId}))然而,`JSON_QUOTE`函数要求MySQL版本5.7.13或更高。该函数将字符串转换为JSON字符串(即添加双引号)。如果`currentUserId`是整数,我们需要先将其转换为字符串,再使用`JSON_QUOTE`。在MyBatis中,我们可以这样写:ORJSON_CONTAINS(duty_user,JSON_QUOTE(CAST(#{currentUserId}ASCHAR)))这样,无论传入的是整数还是字符串,都会先转为字符串,然后加上双引号变成JSON字符串。但是,如果`duty_user`中存储的是数字,而传入的是字符串,同样会出现类型不匹配。因此,最好的做法是统一存储为同一种类型(推荐字符串)。假设我们统一使用字符串,那么查询条件可以写成:(create_user_id=#{currentUserId}ORJSON_CONTAINS(duty_user,JSON_QUOTE(#{currentUserId})))但注意:`create_user_id`字段可能是整数类型,所以这里需要将`currentUserId`作为整数比较,而`duty_user`中存储的是字符串,所以使用JSON_QUOTE将其转换为JSON字符串。然而,如果`duty_user`中存储的是数字,那么我们应该将用户ID作为数字存储,并且在查询时使用数字的JSON表示(即不加引号)。那么条件可以写成:ORJSON_CONTAINS(duty_user,CAST(#{currentUserId}ASJSON))因为当`currentUserId`是整数时,`CAST(#{currentUserId}ASJSON)`会生成一个JSON数字(例如123)。总结:关键在于确保`duty_user`数组中的元素类型与传入的CAST后的类型一致。因此,在数据库设计时,我们应该确定`duty_user`数组中的ID是数字还是字符串,并在整个应用中保持一致。如果无法确定,或者需要同时支持两种类型,我们可以尝试两种转换(但这会影响性能):(create_user_id=#{currentUserId}ORJSON_CONTAINS(duty_user,CAST(#{currentUserId}ASJSON))ORJSON_CONTAINS(duty_user,CAST(#{currentUserId}ASCHAR)FORMATJSON)--MySQL8.0.12及以上支持FORMATJSON)但是,在MySQL5.7中,可能不支持`FORMATJSON`,所以更简单的方式是使用两个条件:ORJSON_CONTAINS(duty_user,CAST(#{currentUserId}ASJSON))ORJSON_CONTAINS(duty_user,CONCAT('"',#{currentUserId},'"'))但这样写可能会很慢,因为需要两次JSON解析。因此,最佳实践是统一类型。现在,我们假设`duty_user`中存储的是数字(整数),那么原始条件就是正确的。示例查询:SELECT*FROMtable_nameWHERE(create_user_id=123ORJSON_CONTAINS(duty_user,CAST(123ASJSON));如果`duty_user`字段存储的是`[123,456]`,那么该行将被选中。如果存储的是`[123]`,同样也会被选中。注意事项:1.确保`duty_user`字段是JSON类型(MySQL5.7及以上版本支持)。2.确保传入的`currentUserId`是整数(或者根据存储类型调整)。如果`duty_user`字段可能为NULL,那么我们需要考虑NULL的情况,因为`JSON_CONTAINS(NULL,...)`会返回NULL,条件不成立。所以如果存在NULL,我们可以使用`COALESCE`或确保非空。例如:(create_user_id=#{currentUserId}OR(duty_userISNOTNULLANDJSON_CONTAINS(duty_user,CAST(#{currentUserId}ASJSON))))但是,如果`duty_user`是NULL,那么第二个条件就会跳过,只检查第一个条件。最后,给出一个完整的示例:假设表结构为:CREATETABLEtasks(idINTPRIMARYKEY,create_user_idINT,duty_userJSON--存储负责人的ID数组,如[1,2,3]);查询语句使用MyBatis动态参数):SELECT*FROMtasksWHERE(create_user_id=#{currentUserId}OR(duty_userISNOTNULLANDJSON_CONTAINS(duty_user,CAST(#{currentUserId}ASJSON)));如果确定duty_user字段不会为NULL,则可以省略NULL检查。性能注意:对JSON字段使用函数(如JSON_CONTAINS)可能会使索引失效(除非使用MySQL的函数索引)。如果该查询频繁使用,可以考虑在duty_user上创建函数索引(MySQL8.0支持)或者使用生成列加索引。例如,在MySQL8.0中,我们可以创建一个生成列来存储用户ID数组,并在该列上创建多值索引(Multi-ValuedIndex):ALTERTABLEtasksADDCOLUMNduty_user_idsBIGINTUNSIGNEDGENERATEDALWAYSAS(JSON_EXTRACT(duty_user,'$[*]'))VIRTUAL,ADDINDEXidx_duty_user_ids(duty_user_ids);但是,注意:多值索引是MySQL8.0.17引入的,用于索引JSON数组中的多个值。创建多值索引后,我们可以使用:WHERE#{currentUserId}MEMBEROF(duty_user_ids)这样查询会更高效。因此,条件可以改写为:(create_user_id=#{currentUserId}OR#{currentUserId}MEMBEROF(duty_user_ids))但这需要提前创建多值索引。总结:原始条件在类型一致的情况下是有效的。如果类型不一致,需要调整。同时,考虑NULL值和性能优化。根据用户的需求,我们给出以下建议:1.确保duty_user数组中的ID类型与传入的currentUserId类型一致(推荐都使用数字)。2.如果duty_user可能为NULL,则添加NULL检查。3.对于性能要求高的场景,考虑使用多值索引(MySQL8.0.17及以上)。因此,最终的查询条件可以写为:(create_user_id=#{currentUserId}OR(duty_userISNOTNULLANDJSON_CONTAINS(duty_user,CAST(#{currentUserId}ASJSON)))如果使用多值索引,则条件为:(create_user_id=#{currentUserId}OR#{currentUserId}MEMBEROF(duty_user_ids))但注意,多值索引需要提前创建生成列和索引。现在,我们回到用户的问题,他提供的条件在类型一致的情况下是正确的。</think>在 MySQL 中实现包含当前用户 ID 的条件查询,您的表达式基本正确但需要根据数据类型进行调整。以下是完整解决方案: ### 完整查询条件 ```sql ( create_user_id = #{currentUserId} OR JSON_CONTAINS(duty_user, CAST(#{currentUserId} AS JSON)) ) ``` ### 关键说明 1. **数据类型匹配**(最易出错点): - 若 `duty_user` 存储的是 **字符串类型 ID**(如 `["101","102"]`): ```sql OR JSON_CONTAINS(duty_user, CAST(CONCAT('"', #{currentUserId}, '"') AS JSON)) ``` *需要额外添加引号确保类型匹配* - 若 `duty_user` 存储的是 **数字类型 ID**(如 `[101,102]`): ```sql OR JSON_CONTAINS(duty_user, CAST(#{currentUserId} AS JSON)) -- 直接使用原始写法 ``` 2. **NULL 值处理**: ```sql OR (duty_user IS NOT NULL AND JSON_CONTAINS(duty_user, ...)) ``` 3. **完整示例**: ```sql SELECT * FROM tasks WHERE ( create_user_id = 123 OR ( duty_user IS NOT NULL AND JSON_CONTAINS(duty_user, CAST(123 AS JSON)) ) ) -- 假设duty_user存储数字类型ID: [123, 456] ``` ### 性能优化建议 1. **索引策略**: ```sql ALTER TABLE your_table ADD INDEX (create_user_id); -- MySQL 8.0+ 使用多值索引 ALTER TABLE your_table ADD INDEX idx_duty_user ((CAST(duty_user AS UNSIGNED ARRAY))); ``` 2. **替代方案**(MySQL 8.0.17+): ```sql OR #{currentUserId} MEMBER OF(duty_user) ``` 此语法更简洁且可利用多值索引[^1] ### 使用场景说明 | 场景 | 推荐方案 | |------|----------| | 简单权限检查 | 原始 `JSON_CONTAINS` | | 高频查询/大数据量 | 多值索引 + `MEMBER OF` | | 混合类型ID存储 | 应用层统一ID类型 |
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值