各位 MySQL 大手子快来，请教一个问题

create table xxxtable
(
    id               bigint(22) auto_increment comment '主键 id'
        primary key,
    waring_value_msg json                                not null comment '',
    names_virtual    varchar(20) as (json_unquote(json_extract(`waring_value_msg`, '$.warnMonth')))
)
    comment '' charset = utf8;

create index device_waring_config_names_virtual_index
    on tnor.device_waring_config (names_virtual);

表中 json 格式的数据大致如下：

{
	"warnMonth": [1,2],
	"rouleItem": [{
		"maxValue": 10,
		"minValue": 0,
		"waringMsg": "这是提示信息",
		"warnMonth": "1"
	}, {
		"maxValue": 20,
		"minValue": 10,
		"waringMsg": "这是提示信息",
		"warnMonth": "2"
	}]
}

有什么办法可以通过 SQL 来查询我给出的 warnMonth 是不是在 JSON 里面呢，类似于这种：

select * from xxxtable where #{warnMonth} in JOSN 中的 warnMonth ;

7 replies • 2023-02-10 18:40:02 +08:00

gkair

Feb 10, 2023

虚拟列？

nitmali

Feb 10, 2023

JSON_CONTAINS_PATH(json, one_or_all, path[, path])

yukunZhan9

Feb 10, 2023

@gkair 虚拟列的数据是 [1,2]，不能走索引，
比如
```MySQL
select * from xxxtable where 虚拟列 like '%1%';
```
才能查出来

MadbookPro

Feb 10, 2023

不懂 SQL, 帮你问了一下 ChatGPT 。
------------------------------------------
您可以使用 MySQL 中的 JSON 函数来查询您给定的 "warnMonth" 是否存在于 JSON 数据中。您可以使用 "json_contains" 函数来实现此功能：

SELECT *
FROM xxxtable
WHERE json_contains(waring_value_msg, json_quote(#{warnMonth}), '$.warnMonth');

请注意，在上面的代码中，我们首先使用 "json_quote" 函数将 "warnMonth" 转换为字符串，然后再使用 "json_contains" 函数检查该字符串是否存在于 "waring_value_msg" 中的 "warnMonth" 键中。

如果要在 "rouleItem" 中搜索，您可以这样写：

SELECT *
FROM xxxtable
WHERE json_contains(waring_value_msg, json_quote(json_object('warnMonth', #{warnMonth})), '$.rouleItem');

xiaoliu926

Feb 10, 2023

ChatGPT 的回答：
MySQL 5.7 中可以使用 JSON 函数进行查询，例如 JSON_SEARCH() 函数。你可以使用如下语句来查询 warnMonth 是否在 JSON 中：

sql
Copy code
SELECT *
FROM xxxtable
WHERE JSON_SEARCH(waring_value_msg, 'one', CAST(#{warnMonth} AS JSON)) IS NOT NULL;
其中，#{warnMonth} 表示传入的查询参数，而 JSON_SEARCH 函数则用来搜索 JSON 数据中是否有给定的值。如果搜索结果不为空，说明该值在 JSON 中存在。

请注意，在 MySQL 5.7 中，JSON_SEARCH() 函数的返回值是一个 JSON 字符串，因此需要使用 IS NOT NULL 进行判断。

realpg

Feb 10, 2023

还想走索引，想要性能就不要瞎鸡儿搞

就 MYSQL 弱鸡的索引，你这么查都能有效利用的话那索引体积不得几倍表大

真研究性能了，就不要搞啥花活，甚至万事先问问能不能存 int

netnr

Feb 10, 2023 via Android

存储的 JSON 是格式化的，就用 like '%"warnMonth": "2"%'
理论上只会多不会少，要确保准确性，用数据时解析后再验证过滤一下