MySQL JSON数据类型

json据说是MySQL5.7新增数据类型，以下为学习笔记

1）创建使用json数据类型的表：

CREATE TABLE oper_log (
　id INT NOT NULL AUTO_INCREMENT,
　oper_name VARCHAR(30) NOT NULL,
　oper_detail JSON DEFAULT NULL,#注意字段类型为json
　PRIMARY KEY(id)
);

2）插入数据

-- 插入含有json数组的记录
INSERT INTO oper_log(oper_name,oper_detail ) VALUES( 'admin', JSON_ARRAY(1, "course", NULL, TRUE, CURTIME()));

-- 插入含有json对象的记录
-- 可以使JSON_OBJECT()方法来创建json对象，方法内带有偶数个参数，一个键一个值得形式转换为json
INSERT INTO oper_log(oper_name,oper_detail ) VALUES( 'admin', JSON_OBJECT("course", "维修保养课程", "currentTime", now()));
-- 如果如下面向JSON_OBJECT()方法传入奇数个参数，会报错：Incorrect parameter count in the call to native function 'JSON_OBJECT'
INSERT INTO oper_log(oper_name,oper_detail ) VALUES( 'admin', JSON_OBJECT("course", "维修保养课程", "currentTime"));//错误的
-- 还可以使用json字符串的形式传入json对象
INSERT INTO oper_log(oper_name,oper_detail ) VALUES( 'admin', '{"course":"数据结构课程", "currentTime":"2018-010-1 14:01:00"}');
-- 一定要转换为字符串，如下直接传入对象，会报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"course":"数据结构课程", "currentTime":"2018-010-1 14:01:00"})' at line 1
INSERT INTO oper_log(oper_name,oper_detail ) VALUES( 'admin', {"course":"数据结构课程", "currentTime":"2018-010-1 14:01:00"}); //错误的

上面插入的数据：

3）查询json中某个字段的值

SELECT id,JSON_EXTRACT(oper_detail,'$.course') as course FROM oper_log;

或：
SELECT id,oper_detail->'$.course' as course FROM oper_log;

4）以json中的某个字段进行查询

 SELECT * FROM oper_log WHERE JSON_EXTRACT(oper_detail,'$.course') LIKE '%2%';