2020年5月

正常的key查询哪些文档大家都能用, 但是如果key是 abc:de-fg 这样的值呢, 那么文档中那些办法无法实现查询了

尝试了好久, 终于找到了对应的解决办法

非法键值查询办法

比如demo为
json_data
{"abc:de-fg": "aa"}
那么取出这个值的办法为 json_data->'$."abc:de-fg"'
注意, 只能里面使用双引号的写法

JSON内的键值是否为null的判断方法

json_data
{"abc:de-fg": "aa","b":null}

// 判断里面的值等于null的判断方式
json_data->'$.b'=CAST('null' AS json)

业务场景
有一张表 project_users

表的数据列表
openid
------
[{"app_id": "1", "openid": "oromp5ZEhkLz1BXELF-cxyqap-uw"}, {"app_id": "2", "openid": "oKTQ41OqjJ848_SPe7wbTZO9RmOE"}]
[{"app_id": "1", "openid": "oromp5QBh9-a8VQlM99knmq2eM7k"}]
(NULL)
把表扁平化下来
SELECT 
uid
,openid
,openid->'$[*].app_id'
,json_search(openid->'$[*].app_id','all','2')
,openid->'$[0].openid'
,a.app_id
,a.json_openid
 FROM `project_users`,JSON_TABLE(openid, '$[*]' COLUMNS(app_id INT path '$.app_id',json_openid VARCHAR(50) path '$.openid')) a

-----

      uid  openid                                                                                                                  openid->'$[*].app_id'  json_search(openid->'$[*].app_id','all','2')  openid->'$[0].openid'           app_id  json_openid                   
---------  ----------------------------------------------------------------------------------------------------------------------  ---------------------  --------------------------------------------  ------------------------------  ------  ------------------------------
100001002  [{"app_id": "1", "openid": "oromp5ZEhkLz1BXELF-cxyqap-uw"}, {"app_id": "2", "openid": "oKTQ41OqjJ848_SPe7wbTZO9RmOE"}]  ["1", "2"]             "$[1]"                                        "oromp5ZEhkLz1BXELF-cxyqap-uw"       1  oromp5ZEhkLz1BXELF-cxyqap-uw  
100001002  [{"app_id": "1", "openid": "oromp5ZEhkLz1BXELF-cxyqap-uw"}, {"app_id": "2", "openid": "oKTQ41OqjJ848_SPe7wbTZO9RmOE"}]  ["1", "2"]             "$[1]"                                        "oromp5ZEhkLz1BXELF-cxyqap-uw"       2  oKTQ41OqjJ848_SPe7wbTZO9RmOE  
100001006  [{"app_id": "1", "openid": "oromp5QBh9-a8VQlM99knmq2eM7k"}]                                                             ["1"]                  (NULL)                                        "oromp5QBh9-a8VQlM99knmq2eM7k"       1  oromp5QBh9-a8VQlM99knmq2eM7k

查询指定key

demo 数据包
data_cache
----------
{"courses:220-0": 24, "course_classes:1": 25701,"test":"666"}

如果我们查询test值或者移除非常简单
SELECT data_cache->'$.test',json_remove(data_cache,'$.test') FROM `daily_caches` WHERE id=32

result
data_cache->'$.test'  json_remove(data_cache,'$.test')                  
--------------------  --------------------------------------------------
"666"                 {"courses:220-0": 24, "course_classes:1": 25701}  


但是如果我们查询里面的 course_classes:1 key, 上面的方法就不行了, 需要下面的写法才行, 试了好久才试出来的

SELECT data_cache->'$."course_classes:1"',json_remove(data_cache,'$."course_classes:1"') FROM `daily_caches` WHERE id=32

result
data_cache->'$."course_classes:1"'  json_remove(data_cache,'$."course_classes:1"')  
----------------------------------  ------------------------------------------------
25701                               {"test": "666", "courses:220-0": 24}

创建日志表

DROP TABLE IF EXISTS `table_change_log`;
CREATE TABLE `table_change_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_name` varchar(20) NOT NULL DEFAULT '' COMMENT '表名',
  `table_op` varchar(20) NOT NULL DEFAULT '' COMMENT '操作',
  `table_id` int(11) NOT NULL DEFAULT '0' COMMENT '表ID',
  `add_time` datetime NOT NULL COMMENT '添加时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='表操作记录';

创建触发器, 跟踪增删改

DROP TRIGGER IF EXISTS `tg_project_users_insert`;
CREATE TRIGGER `tg_project_users_insert` AFTER INSERT ON `project_users`
FOR EACH ROW INSERT INTO table_change_log (`table_name`, `table_op`, `table_id`, `add_time`) VALUES('project_users', 'insert', new.pro_id, NOW());

DROP TRIGGER IF EXISTS `tg_project_users_update`;
CREATE TRIGGER `tg_project_users_update` AFTER UPDATE ON `project_users`
FOR EACH ROW INSERT INTO table_change_log (`table_name`, `table_op`, `table_id`, `add_time`) VALUES('project_users', 'update', old.pro_id, NOW());

DROP TRIGGER IF EXISTS `tg_project_users_delete`;
CREATE TRIGGER `tg_project_users_delete` AFTER DELETE ON `project_users`
FOR EACH ROW INSERT INTO table_change_log (`table_name`, `table_op`, `table_id`, `add_time`) VALUES('project_users', 'delete', old.pro_id, NOW());

用户的特地优惠券列表明细

select uid,group_concat(coupons.price SEPARATOR '+') as '优惠券明细',sum(coupons.price) as '合计金额' from `user_coupons` 
inner join coupons on coupons.`id`=user_coupons.`coupon_id`
where user_coupons.pid=1 
AND user_coupons.state=1 
AND user_coupons.expire_at>NOW()
and user_coupons.uid in (select uid from `project_users` where pid=1 AND json_search(tag_ids,'all','2') is not null) 
AND user_coupons.coupon_id in (select id from `coupons` where use_type='new_agent1')
group by uid

  • 数组部分
将原数组倒序 array_reverse()
  • 移除数值无意义的0
$num = '0.123456789012345678900000000000';
echo rtrim(rtrim($num, '0'), '.');
  1. 1
  2. 2
  3. 3