开始运行MYSQL 5.7

安装笔记:

http://wangshisan.com/blog/install-mysql-5-7.html

参考文档

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 ;
Posted on Categories 数据库 Tags