原子增加数值
假设有表 create table emp_details ( emp_no int PRIMARY key, details json not null DEFAULT (json_object()) )
存在数据 insert into emp_details(emp_no, details) values (1, '{"location": "IN", "phone": "1180000000", "email": "abc@example.com", "address":{"line1": "abc", "line2": "xyz street", "city": "Bangalore", "pin": 560103}}' );
更新json里面的pin字段
update emp_details
set details=json_set(details, '$.address.pin', details->>'$.address.pin'+1)
where emp_no=1
这样的结果会是浮点数, 需要改为
update emp_details
set details=json_set(details, '$.address.pin', cast(details->>'$.address.pin'+1 as decimal(17,0) ))
where emp_no=1
修改json列
ALTER TABLE table_name ALTER column_name SET DEFAULT (JSON_ARRAY());