开始运行MYSQL 5.7 作者: 王十三 时间: 2018-01-03 分类: 服务器运维 参考文档 > http://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html 编辑my.cnf [mysqld] 下面增加 > sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES' 运行 MYSQL : > /etc/init.d/mysqld start > > mysql -uroot -p > JSON查询方法: > JSON_EXTRACT(json_doc, path[, path] ... 普通查询 mysql> SELECT JSON_EXTRACT('{"a":1,"b":0}','$.a') AS json ; +------+ | json | +------+ | 1 | +------+ 1 row in set (0.00 sec) 数组查询 mysql> SELECT JSON_EXTRACT('[10,20,30]','$[*]') AS json ; +--------------+ | json | +--------------+ | [10, 20, 30] | +--------------+ 1 row in set (0.00 sec) 查询数组 键值 内容 mysql> SELECT JSON_EXTRACT('{"a":1,"b":0,"c":[10,20,30]}','$.c[1]') AS json ; +------+ | json | +------+ | 20 | +------+ 1 row in set (0.00 sec) > JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) > > one_or_all : one or all > > PS:适用于搜索数组 搜索数组 SELECT JSON_SEARCH('["1","0",["10","20","30"]]','one','1') AS json ; 结合JSON_EXTRACT SELECT JSON_SEARCH(JSON_EXTRACT('{"a":1,"b":0,"c":["10","20","30"]}','$.c'),'all','10') AS json ; 标签: none