mysql json 类型_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%';