mysql path妙用-白红宇

mysql path妙用

阅读量：4338 次

发布时间：2019-06-07

本文共 16171 字，大约阅读时间需要 53 分钟。

CREATE TABLE `lc_c_user_path` (  `id` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,  `user_id` VARCHAR ( 20 ) NOT NULL COMMENT '用户标识',  `username` VARCHAR ( 100 ) DEFAULT NULL COMMENT '用户名',  `store_id` VARCHAR ( 20 ) DEFAULT NULL COMMENT '店铺id',  `user_id_path` text COMMENT '当前用户上级用户标识',  `username_path` text COMMENT '当前用户上级用户账号',  `store_id_path` text COMMENT '当前用户上级用户店铺',  `complete_path` text COMMENT '绑定邀请码的用户path',  PRIMARY KEY ( `id` ),  UNIQUE KEY `IDX_U_I` ( `user_id` ) USING BTREE) ENGINE = INNODB AUTO_INCREMENT = 16 DEFAULT CHARSET = utf8 COMMENT = '用户user_id,username,store之间转化';

INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (1, 'us1', 'un1', 's1', 'us0', 'un0', 's0', 'us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (2, 'us2', 'un2', 's2', 'us1/us0', 'un1/un0', 's1/s0', 'us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (3, 'us3', 'un3', 's3', 'us2/us1/us0', 'un2/un1/un0', 's2/s1/s0', 'us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (4, 'us4', 'un4', NULL, 'us3/us2/us1/us0', 'un3/un2/un1/un0', 's3/s2/s1/s0', 'us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (5, 'us5', 'un5', 's5', 'us3/us2/us1/us0', 'un3/un2/un1/un0', 's3/s2/s1/s0', 'us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (6, 'us6', 'un6', 's6', 'us5/us3/us2/us1/us0', 'un5/un3/un2/un1/un0', 's5/s3/s2/s1/s0', 'us5/us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (7, 'us7', 'un7', 's7', 'us6/us5/us3/us2/us1/us0', 'un6/un5/un3/un2/un1/un0', 's6/s5/s3/s2/s1/s0', 'us6/us5/us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (8, 'us8', 'un8', 's8', 'us7/us6/us5/us3/us2/us1/us0', 'un7/un6/un5/un3/un2/un1/un0', 's7/s6/s5/s3/s2/s1/s0', 'us7/us6/us5/us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (9, 'us9', 'un9', '', 'us8/us7/us6/us5/us3/us2/us1/us0', 'un8/un7/un6/un5/un3/un2/un1/un0', 's8/s7/s6/s5/s3/s2/s1/s0', 'us8/us7/us6/us5/us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (10, 'us10', 'un10', 's10', 'us8/us7/us6/us5/us3/us2/us1/us0', 'un8/un7/un6/un5/un3/un2/un1/un0', 's8/s7/s6/s5/s3/s2/s1/s0', 'us9/us8/us7/us6/us5/us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (11, 'us11', 'un11', 's11', 'us10/us8/us7/us6/us5/us3/us2/us1/us0', 'un10/un8/un7/un6/un5/un3/un2/un1/un0', 's10/s8/s7/s6/s5/s3/s2/s1/s0', 'us10/us9/us8/us7/us6/us5/us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (12, 'us12', 'un12', '', 'us11/us10/us8/us7/us6/us5/us3/us2/us1/us0', 'un11/un10/un8/un7/un6/un5/un3/un2/un1/un0', 's11/s10/s8/s7/s6/s5/s3/s2/s1/s0', 'us11/us10/us9/us8/us7/us6/us5/us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (13, 'us13', 'un13', 's13', 'us11/us10/us8/us7/us6/us5/us3/us2/us1/us0', 'un11/un10/un8/un7/un6/un5/un3/un2/un1/un0', 's11/s10/s8/s7/s6/s5/s3/s2/s1/s0', 'us12/us11/us10/us9/us8/us7/us6/us5/us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (14, 'us14', 'un14', 's14', 'us13/us11/us10/us8/us7/us6/us5/us3/us2/us1/us0', 'un13/un11/un10/un8/un7/un6/un5/un3/un2/un1/un0', 's13/s11/s10/s8/s7/s6/s5/s3/s2/s1/s0', 'us13/us12/us11/us10/us9/us8/us7/us6/us5/us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (15, 'us15', 'un15', 's15', 'us14/us13/us11/us10/us8/us7/us6/us5/us3/us2/us1/us0', 'un14/un13/un11/un10/un8/un7/un6/un5/un3/un2/un1/un0', 's14/s13/s11/s10/s8/s7/s6/s5/s3/s2/s1/s0', 'us14/us13/us12/us11/us10/us9/us8/us7/us6/us5/us4/us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (16, 'us16', 'un16', 's16', 'us1/us0', 'un1/un0', 's1/s0', 'us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (17, 'us17', 'un17', 's17', 'us16/us1/us0', 'un16/un1/un0', 's16/s1/s0', 'us16/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (18, 'us18', 'un18', 's18', 'us3/us2/us1/us0', 'un3/un2/un1/un0', 's3/s2/s1/s0', 'us3/us2/us1/us0');INSERT INTO `lc_c_user_path`(`id`, `user_id`, `username`, `store_id`, `user_id_path`, `username_path`, `store_id_path`, `complete_path`) VALUES (19, 'us19', 'un19', 's19', 'us18/us3/us2/us1/us0', 'un18/un3/un2/un1/un0', 's18/s3/s2/s1/s0', 'us18/us3/us2/us1/us0');

View Code

CREATE TABLE `lc_c_customer` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `user_id` varchar(20) NOT NULL COMMENT '用户标识',  `partent_user_id` varchar(20) DEFAULT NULL COMMENT '上级店铺id',  `pid` varchar(20) DEFAULT NULL COMMENT '当前用户上级用户标识',  PRIMARY KEY (`id`),  UNIQUE KEY `IDX_U_I` (`user_id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='用户表';

INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (1, 'us1', NULL, 'us0');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (2, 'us2', 'us1', 'us1');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (3, 'us3', 'us2', 'us2');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (4, 'us4', 'us3', 'us3');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (5, 'us5', 'us3', 'us4');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (6, 'us6', 'us5', 'us5');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (7, 'us7', 'us6', 'us6');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (8, 'us8', 'us7', 'us7');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (9, 'us9', 'us8', 'us8');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (10, 'us10', 'us8', 'us9');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (11, 'us11', 'us10', 'us10');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (12, 'us12', 'us11', 'us11');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (13, 'us13', 'us11', 'us12');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (14, 'us14', 'us13', 'us13');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (15, 'us15', 'us14', 'us14');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (16, 'us16', 'us1', 'us15');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (17, 'us17', 'us16', 'us16');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (18, 'us18', 'us3', 'us17');INSERT INTO `lc_c_customer`(`id`, `user_id`, `partent_user_id`, `pid`) VALUES (19, 'us19', 'us18', 'us18');

View Code

CREATE TABLE `lc_c_store` (  `id` varchar(20) NOT NULL,  `user_id` varchar(20) NOT NULL DEFAULT '0' COMMENT '客户id',  `name` varchar(64) DEFAULT '0' COMMENT '店铺名字',  PRIMARY KEY (`id`),  UNIQUE KEY `IDX_U` (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='店铺表';

INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s1', 'us1', 'us1的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s10', 'us10', 'us10的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s11', 'us11', 'us11的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s13', 'us13', 'us13的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s14', 'us14', 'us14的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s15', 'us15', 'us15的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s16', 'us16', 'us16的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s17', 'us17', 'us17的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s18', 'us18', 'us18的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s19', 'us19', 'us19的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s2', 'us2', 'us2的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s3', 'us3', 'us3的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s5', 'us5', 'us5的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s6', 'us6', 'us6的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s7', 'us7', 'us7的云店');INSERT INTO `lc_c_store`(`id`, `user_id`, `name`) VALUES ('s8', 'us8', 'us8的云店');

View Code

CREATE TABLE `lc_c_store_general` (  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,  `store_id` varchar(20) DEFAULT NULL COMMENT '对应店铺表id',  `user_count` bigint(11) DEFAULT '0' COMMENT '客户数，即 下一级别的用户数',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='店铺销售数据表';

INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (1, 's1', 2);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (2, 's2', 1);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (3, 's3', 3);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (4, 's5', 1);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (5, 's6', 1);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (6, 's7', 1);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (7, 's8', 2);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (8, 's10', 1);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (9, 's11', 2);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (10, 's13', 1);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (11, 's14', 1);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (12, 's15', 0);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (13, 's16', 1);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (14, 's17', 0);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (15, 's18', 1);INSERT INTO `lc_c_store_general`(`id`, `store_id`, `user_count`) VALUES (16, 's19', 0);

View Code

查看用户us2的上级path complete_path

select * from lc_c_user_path where user_id='us2';

查看用户us2的所有下级用户

select * from lc_c_user_path where complete_path like '%us2/us1/us0';

某个开店用户下的客户数

select partent_user_id,count(1) as count from lc_c_customer where 1=1 GROUP BY partent_user_id

当某个店铺开店,更新lc_c_customer表的partent_user_id字段，即更新当前开店用户的所有下级的partent_user_id,此partent_user_id为最近的已开店的上级的user_id

partent_user_id可以通过获取user_id_path的第一个就是它的直接上级的已开店的partent_user_id

参考代码:

/**    * desc 更新用户链条关系    */    public function upUserChain(){        $where['create_time']=array('gt',"2016-01-01 00:00:00");        $storeM=M('c_store');        $data=$storeM->where($where)->order('create_time asc')->find();        if(empty($data)){            exit('没有数据需要更新');        }        $openStoreUserId=$data['user_id'];        $up_date=$data['create_time'];         //查询当前店铺的 对应的userPath信息        $userPathM=M('c_user_path');        $whereUP['user_id']=$openStoreUserId;        $field='user_id,username,store_id,user_id_path,username_path,store_id_path,complete_path';        $currentUserInfo=$userPathM->where($whereUP)->field($field)->find();        if(empty($currentUserInfo)){            exit('当前店铺不存在user_path');        }        //查询当前店铺的 对应的所有下级        $pidPath=$openStoreUserId.'/'.$currentUserInfo['complete_path'];        $whereAllSubordinate['complete_path']=array('like',"%{$pidPath}");        //按id升序        $list=$userPathM->where($whereAllSubordinate)->order('id asc')->field($field)->select();        //需要更新lc_store_general数据的店铺 -- 开店 + 开店所有下级（开店）+ 直接上级（开店）        $needUpStoreArr=array();        $needUpStoreArr[]=$currentUserInfo['store_id'];//开店        $store_id_path=$currentUserInfo['store_id_path'];        $storeIdPathArr=explode('/',$store_id_path);        $needUpStoreArr[]=reset($storeIdPathArr);//上级蜂店        try{            if(empty($list)){                exit('当前店铺无下级，更新完毕！');            }            //存在下级的情况            //更新lc_user_path 几个上级蜂店path            //更新所有下级的parent_user_id            $needUpPUIArr=array();            M()->startTrans();            foreach ($list as $key => $value) {                //当前用户的直接上级                $complete_path=$value['complete_path'];                $completePathArr=explode('/',$complete_path);                $firstLevel=reset($completePathArr);                //判断当前用户是否开店                if(!empty($value['store_id'])){
    //说明已经开店--更新lc_store_general数据                    $needUpStoreArr[]=$value['store_id'];                }                //判断当前用户的直接上级                $wherCurrenSuperior['user_id']=$firstLevel;                $currenSuperiorInfo=$userPathM->where($wherCurrenSuperior)->field($field)->find();                //判断当前用户的上级是否开店                if(!empty($currenSuperiorInfo['store_id'])){
    //说明已经开店                    $newUserIdPath=$currenSuperiorInfo['user_id'].'/'.$currenSuperiorInfo['user_id_path'];//上级蜂店用户编号+上级蜂店用户父级编号=当前用户的上级蜂店用户编号                    $newUserNamePath=$currenSuperiorInfo['username'].'/'.$currenSuperiorInfo['username_path'];//上级蜂店用户username+上级蜂店用户父级username=当前用户的上级蜂店用户username                    $newStoreIdPath=$currenSuperiorInfo['store_id'].'/'.$currenSuperiorInfo['store_id_path'];//上级蜂店用户店铺编号+上级蜂店用户父级店铺编号=当前用户的上级蜂店用户店铺编号                    //当前用户的上级蜂店                    $firstStoreUserLevel=$currenSuperiorInfo['user_id'];                }else{                    //上级没有开店 所有的店铺相关path 都和上级一样                    $newUserIdPath=$currenSuperiorInfo['user_id_path'];                    $newUserNamePath=$currenSuperiorInfo['username_path'];                    $newStoreIdPath=$currenSuperiorInfo['store_id_path'];                    //当前用户的上级蜂店                    $userIdPathArr=explode('/',$newUserIdPath);                    $firstStoreUserLevel=reset($userIdPathArr);                }                $needUpPUIArr[$value['user_id']]=$firstStoreUserLevel;                $whereUp['user_id']=$value['user_id'];                $upData=array(                    'user_id_path'=>$newUserIdPath,                    'username_path'=>$newUserNamePath,                    'store_id_path'=>$newStoreIdPath,                );                $reUp=$userPathM->where($whereUp)->data($upData)->save();                if($reUp===false){                    E('210001','更新user_path失败');                }            }            M()->commit();            exit('执行成功');        }catch (Exception $ex) {            M()->rollback();            $curCode=$ex->getErrorCode();            $curmsg=$ex->getMessage();                  exit($curmsg);        }    }

View Code

转载于:https://www.cnblogs.com/zouke1220/p/9378823.html

你可能感兴趣的文章

Oracle11g创建表空间、创建用户、角色授权、导入导出表以及中文字符乱码问题...

查看>>

我对 Window.Open 的认识

查看>>

restore db from production copy

查看>>

jQuery源码的基础知识

查看>>

HDU 2188 悼念512汶川大地震遇难同胞——选拔志愿者