V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
yukunZhan9
V2EX  ›  MySQL

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

  •  
  •   yukunZhan9 · 2023-02-10 15:10:21 +08:00 · 2342 次点击
    这是一个创建于 650 天前的主题,其中的信息可能已经有所发展或是发生改变。

    MySQL5.7 如何查询某个值是否在 JSON 类型的字段中呢。

    表结构类似这样:

    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 条回复    2023-02-10 18:40:02 +08:00
    gkair
        1
    gkair  
       2023-02-10 15:15:57 +08:00
    虚拟列?
    nitmali
        2
    nitmali  
       2023-02-10 15:17:58 +08:00
    JSON_CONTAINS_PATH(json, one_or_all, path[, path])
    yukunZhan9
        3
    yukunZhan9  
    OP
       2023-02-10 15:18:21 +08:00
    @gkair 虚拟列的数据是 [1,2],不能走索引,
    比如
    ```MySQL
    select * from xxxtable where 虚拟列 like '%1%';
    ```
    才能查出来
    MadbookPro
        4
    MadbookPro  
       2023-02-10 15:32:18 +08:00
    不懂 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
        5
    xiaoliu926  
       2023-02-10 16:05:39 +08:00
    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
        6
    realpg  
       2023-02-10 18:08:17 +08:00
    还想走索引,想要性能就不要瞎鸡儿搞

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

    真研究性能了,就不要搞啥花活,甚至万事先问问能不能存 int
    netnr
        7
    netnr  
       2023-02-10 18:40:02 +08:00 via Android
    存储的 JSON 是格式化的,就用 like '%"warnMonth": "2"%'
    理论上只会多不会少,要确保准确性,用数据时解析后再验证过滤一下
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3339 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 11:39 · PVG 19:39 · LAX 03:39 · JFK 06:39
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.