人生最大的错误是不断担心会犯错 [登录·注册]

吕滔博客

首页 开发 运维 工具 摄影

MySQL实现类似于connect_by_isleaf的功能 MySQL方法或存储过程

数据库 memory发布于January 10, 2017 标签: MySQL

最近有个特别变态的业务需求,有一张表

CREATE TABLE `demo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tid` int(11) DEFAULT '0',
  `pid` int(11) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3000124 DEFAULT CHARSET=utf8;

大概就是这样,有300多万行记录,每个pid记录的这个ID的最顶级分类,tid是它的上级分类!
现在需求是:通过指定一个ID,查找出它的所有子集成员,并修改这个pid的值为新指定的值!!
在PHP中跑了一下,执行时间大概需要50秒+,很是痛苦!!!
需要递归找出所有子集,修改它的pid,工作量还是蛮大的。

而oracle中有一个方法是connect_by_isleaf,可以很方便的找出所有子集,但我是MySQL......

所以用这儿简单的写写用MySQL的方法或存储过程实现的经验

第一种:MySQL的方法

CREATE DEFINER=`root`@`localhost` FUNCTION `lvtao_demo_a`(rootId int) RETURNS text CHARSET utf8
    READS SQL DATA
    COMMENT 'demo'
BEGIN

DECLARE sTemp text;
DECLARE sTempChd text;

SET sTempChd =cast(rootId as CHAR);
SET sTemp = '';

WHILE sTempChd is not null DO
   SET sTemp = concat(sTemp,',',sTempChd);
   SELECT group_concat(id) INTO sTempChd FROM demo where FIND_IN_SET(tid,sTempChd)>0;
END WHILE;

RETURN sTemp;

END;

使用方法就是

select lvtao_demo_a(5);

但我在测试的时候,300万的数据基本上就是崩溃!!!

Data too long for column 'sTemp' at row 1

优点: 简单,方便,没有递归调用层次深度的限制 (max_sp_recursion_depth,最大255) ;
缺点:长度受限。

第二种:存储过程+中间表

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sss`(IN pid int, IN rootid int)
BEGIN
     
DECLARE done INT DEFAULT FALSE;
DECLARE id TEXT;
DECLARE cur1 CURSOR FOR SELECT id FROM demo WHERE tid=pid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

set max_sp_recursion_depth = 200;
  
OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO temp (rootid,zid) values (rootid, id);
        call sss(id,rootid);
    END LOOP;
CLOSE cur1;

END;;
DELIMITER ;

哈哈,300万数据也卡成球了~~~

再想办法吧~~~~不折腾了

相关推荐

添加新评论

网站状态

  • 栏目分类:35个
  • 发布文章:1089篇
  • 用户评论:583条

最新回复

  • PHP程序员雷雪松: 非常详细的windows下MySQL的安装!!!
  • 嘿嘿: 高大上 嘿嘿
  • 嘿嘿: 呦西~~
  • memory: 实在是抱歉,我昨天找了一下我WIN电脑上的编译,发现当时编译完测...
  • kavid: 楼主发到我的邮箱呗 kavid@sina.com
  • 凯哥自媒体: 这个很不错,谢谢分享
  • memory: 在单位WINPC上,周一吧,我努力记着想死这事儿,到时候打包了发上来!
  • kavid: 楼主能把dll分享出来吗??正好我用的php 7.1php官方的...
  • zhaoliang0112: 厉害了,我哥的!!
  • zhaoliang0112: 希望能够有个系统的教程,就不要在去网上找了,直接这里学习!! :)
  • memory: 这是13年淘宝dba分享的一片文章,网上应该还有,我拿过来还没有...
  • wqintel: 而net.ipv4.tcp_mem则是配置tcp的内存大小,其单...
  • memory: 我这个站不是java写的哟~~~这是typecho开源博客程序改...
  • memory: 可以使用nginx反向代理后台的这个5000端口。
  • jarjar: 博主的java站好快啊
  • 凤凰山草民: java站这么快,厉害
  • 若铭: psdash可以部署到nginx上吗?
  • PHP程序员雷雪松: 很实用的PHP代码块!!!
  • codes: 把 .dll 发出来让我试试啊
  • 凯哥自媒体: 这个挺不错的