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%';