MYSQL JSON字段操作
create
CREATE TABLE t_test
(
salary_data
json NULL COMMENT 'JSON类型字段'
);
-- insert
INSERT INTO t_test(salary_data) VALUES('{"param_1": "111","param_2": "222","param_3": "333","param_4": "444"}');
INSERT INTO t_test(salary_data) VALUES('{"param_1": "xxx","param_2": "yyy","param_3": "333","param_4": 444}');
-- select -> json_keys
SELECT json_keys(salary_data) from t_test;
-- select -> one param
SELECT salary_data->'$.param_4' FROM t_test;
-- select -> json_object
select salary_data from t_test;
-- select -> where
SELECT salary_data->'$.param_4' FROM t_test where salary_data->'$.param_4'='444';
SELECT salary_data->'$.param_4' FROM t_test where salary_data->'$.param_4'=444;
SELECT salary_data->'$.param_4' FROM t_test where JSON_CONTAINS(salary_data, '"444"', '$.param_4');
SELECT salary_data->'$.param_4' FROM t_test where JSON_CONTAINS(salary_data, '444', '$.param_4');
-- update -> json_insert
UPDATE t_test SET salary_data = json_insert(salary_data, '$.param_5', 555, '$.param_6', 666) WHERE salary_data->'$.param_1'= 'xxx';
-- update -> json_set
UPDATE t_test SET salary_data = json_set(salary_data, '$.param_2', 222, '$.param_3', 333) WHERE salary_data->'$.param_1'= '111';
-- update -> json_remove
UPDATE t_test SET salary_data = json_remove(salary_data, '$.param_5', '$.param_6') WHERE salary_data->'$.param_1'= 'xxx';
最新文章
- https简介/原理/部署【转】
- inline-boock的默认间距
- Android开发之时间日期2
- Fixed Responsive Nav – 响应式的单页网站导航插件
- 【转】Serverless架构
- tomee.xml
- merry Christmas
- windows平台时间函数性能比较QueryPerformanceCounter,GetTickCount,ftime,time,GetLocalTime,GetSystemTimeAsFileTime
- 在VS2012中使用GDI+
- Android 之使用LocalBroadcastManager解决BroadcastReceiver安全问题
- iOS绘图教程
- webstrom 常用快捷键
- ubuntu vi编辑insert时上下左右建为ABCD
- Google帝国研究——Google的产业构成
- iOS中常见的锁
- golang文件操作
- C# 最牛逼的Utility工具类
- vue+sass实现切换字体大小
- Pyhon学习笔记-基础3
- Java 创建线程/停止线程