业务场景
有一张表 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

第一种方法：

select (@i:=@i+1) as i,table_name.* from table_name,(select @i:=0) as it ;

第二种方法：

set @rownum=0;
select @rownum:=@rownum+1 as rownum, t.username from auth_user t limit 1,5;

如果你的用户访问量较少, 比如你24小时访问一次你的站点 , 可能会出现 send of 5 bytes failed with errno=32 Broken pipe in

这只是一种可能 . 几率很小

如果出现此错误 请到网站根目录/Conf/config.php

打开文件找到

//PDO配置
    'SQL_OPTION' => array(
        PDO::ATTR_CASE => PDO::CASE_NATURAL,
        PDO::ATTR_PERSISTENT => true //长连接
    ),

改为:

//PDO配置
    'SQL_OPTION' => array(
        PDO::ATTR_CASE => PDO::CASE_NATURAL,//把长连接此项删除即可
    ),

原因:

长连接是一种缓存句柄, 他可以让服务器减少产生MYSQL链接, 然而如果你的网站程序很久未使用 MYSQL句柄将会断开, 但 缓存句柄仍然存在, 从而网站会调用一个已经废弃的句柄, 导致通信管道不存在, 产生此错误!

这是一个悲哀的站长故事,因为他没用户