比别人多一点志气,你就会多一份出息 [登录·注册]

吕滔博客

首页 开发 运维 工具 摄影

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万数据也卡成球了~~~

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

相关推荐

添加新评论

网站状态

  • 栏目分类:49个
  • 发布文章:1512篇
  • 用户评论:729条
  • 开博至今:4070天

正则速查

[abc] 匹配中括号中的单个字符,如a或b或c
[^abc] 匹配除了a、b、c等字符的其他单个字符
[a-z] 匹配一个字符范围,如a到z
[a-zA-Z] 匹配一个字符范围,如a-z 或 A-Z
^ 匹配行的开始
$ 匹配行的结束
\A 匹配一个字符串的开始
\z 匹配一个字符串的结束
. 匹配任意单个字符
\s 匹配空白字符,如空格,TAB
\S 匹配非空白字符
\d 匹配一个数字
\D 匹配非数字
\w 匹配一个字母
\W 匹配非字母
\b 匹配字符边界
(...) 引用所有括号中的内容
(a|b) a或者b
a? 零个或1个a
a* 零个或多个a
a+ 1个或多个a
a{3} 3次重复的a
a{3,} 3次或3次以上重复的a
a{3,6} 3到6次重复的a

修正符

/g 查找所有可能的匹配
/i 不区分大小写
/m 多行匹配
/s 单行匹配
/x 忽略空白模式
/e 可执行模式,PHP专有
/A 强制从目标字符串开头匹配
/D 使用$限制结尾字符,则不允许结尾有换行
/U 只匹配最近的一个字符串;不重复匹配

最新回复

  • 夏目贵志: 我终于懂了是什么意思了!!!
  • 柳溪: 支持博主 很详细!
  • memory: 谢谢。以前没用到checkEmail,是注释掉的,所以这儿我也没...
  • 一个默默无闻的码农: function checkEmail的if里的变量写错了
  • memory: 谢谢大神鼓励。哈哈 有没想到的地方还望多多指正
  • 梦康: 感谢分享,前几天我刚接触这个命令,之前都没用到过。总结非常全面。
  • 梁兴健: Parse error: syntax error, unexp...
  • 耐火砖: 感谢博主分享,很有用
  • memory: 额。。。
  • 亚克力浴缸: 还好我不是学这些的,不然打错那个都不知道
  • memory: 哈哈。写的太2了,将就着吧。我自己看也晕了...
  • 亚克力浴缸: 看晕了,头大
  • 泽泽: 至少手机上图根本没动
  • memory: 呵呵,难道你现在看到的不是gif图?
  • 泽泽: 原文是gif图,无脑抓取也要读一读吧
  • 亚克力浴缸: 学习了 in 参数
  • asd: 感谢博主,功能已实现