JSON序列一

示例：

create table t_json(id int au_increment primary key,e_json);
insert into t_json values(1,'{"id": 11,"icid":111,"rank": 1,"tname": "bourne"}');
insert into t_json values(2,'{"id": 11,"icid":111,"rank": 1,"tname": "som"}');
复制

获取key为icid的value值

select e_json->'$.icid' FROM t_json;
复制

查询所有rank为11的记录

SELECT * FROM t_json WHERE e_json->>'$.rank' = 11;
复制

字符类型属性加索引

alter table t_json add index idx_ejson_tname((cast(e_json->>'$.tname' AS char(64)));
select * from t_json where cast(e_json->>'$.tname' AS CHAR(64)) = 'bourne';
复制

注意：char(N), N要和创建索引时保持一致，不然索引不生效。

数值类型属性加索引

alter table t_json add index idx_ejson_rank((cast(e_json->>'$.rank' as unsigned)));
select * from t_json where e_json->>'$.rank' = 11;


复制