MySQL实现类似于connect_by_isleaf的功能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万数据也卡成球了~~~

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

赞 (0)

相关推荐

  • 实例解析MySQL中的存储过程及存储过程的调用方法

    mysql在5.1之后增加了存储过程的功能, 存储过程运行在mysql内部,语句都已经编译好了,速度比sql更快. 存储过程与mysql相当于shell和linux系统.如果你是程序员的话,那我告诉你存储过程实际上是一个方法,你只要调用这个方法,并且输入它设置好的参数就可以获取或者执行你想要的操作了. 看了如下存储过程实例,你会发现mysql存储过程和shell很像. 下面存储过程内容为:调用存储过程,并且传入用户名,密码参数.存储过程会将这她们存储到process_test表里面.看实例 一,

  • mysql 存储过程判断重复的不插入数据

    mysql存储过程中 下面是一个较常见的场景,判断表中某列是否存在某值,如果存在执行某操作 需要注意的是不能用if exists; exists可以在where后面或者在create object是使用,但是在if语句中不可以使用,只能用变通的方法. delimiter $$ create procedure proc_add_book(in $bookName varchar(200),in $price float) begin declare $existsFlag int default

  • Mybatis调用MySQL存储过程的简单实现

    Mybatis调用MySQL存储过程的简单实现

    1.存储过程的简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它. 一个存储过程是一个可编程的函数,它在数据库中创建并保存.它可以有SQL语句和一些特殊的控制结构组成.当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的.数据库中的存储过程可以看做是对编程中

  • 在Mysql数据库里通过存储过程实现树形的遍历

    关于多级别菜单栏或者权限系统中部门上下级的树形遍历,oracle中有connect by来实现,mysql没有这样的便捷途径,所以MySQL遍历数据表是我们经常会遇到的头痛问题,下面通过存储过程来实现. 1,建立测试表和数据: DROP TABLE IF EXISTS csdn.channel; CREATE TABLE csdn.channel ( id INT(11) NOT NULL AUTO_INCREMENT, cname VARCHAR(200) DEFAULT NULL, pare

  • MySQL与Oracle差异比较之五存储过程&Function

    存储过程&Function 编号 类别 ORACLE MYSQL 注释 1 创建存储过程语句不同 create or replace procedure P_ADD_FAC(    id_fac_cd  IN ES_FAC_UNIT.FAC_CD%TYPE) is DROP PROCEDURE IF EXISTS `SD_USER_P_ADD_USR`; create procedure P_ADD_FAC(        id_fac_cd  varchar(100)) 1.在创建存储过程时如

  • MySQL存储过程的优化实例

    前言 在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用存储过程来封装数据库操作.如果项目的存储过程较多,书写又没有一定的规范,将会影响以后的系统维护困难和大存储过程逻辑的难以理解,另外如果数据库的数据量大或者项目对存储过程的性能要求很,就会遇到优化的问题,否则速度有可能很慢,经过亲身经验,一个经过优化过的存储过程要比一个性能差的存储过程的效率甚至高几百倍.下面介绍某一个MySQL存储过程优化的整个过程. 在本文中,需要被优化的存储过程如下: drop procedu

  • Linux 下mysql通过存储过程实现批量生成记录

    Linux 下mysql通过存储过程实现批量生成记录 在开发中有一个这样的需求:需要在数据库的表中生成大量的记录,这些记录只有很少的差别,只会在创建表的时候进行一次这个操作.当然,要是人工一条一条的做也不是不可以,也可以用外部的程序来完成,但是这里我选择使用mysql的存储过程来实现. 0.相关版本信息 mysql使用官方打包发布的5.6.29版本,安装在linux上 1.数据表结构 表有一个自增的主键,另外有一个字段是需要保存的有少许差异的信息,另外有一个字段是其他信息,创建表的代码: DRO

  • mysql的存储过程、游标 、事务实例详解

    mysql的存储过程.游标 .事务实例详解 下面是自己曾经编写过的mysql数据库存储过程,留作存档,以后用到的时候拿来参考. 其中,涉及到了存储过程.游标(双层循环).事务. [说明]:代码中的注释只针对当时业务而言,无须理会. 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS `transferEmailTempData`$$ CREATE PROCEDURE transferEmailTempData(IN jobId VARCHAR(24)) BEG

  • Java实现调用MySQL存储过程详解

    Java实现调用MySQL存储过程详解

    前言 存储过程(Stored Procedure)是存储在数据库中经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它. Java调用MySQL的存储过程,需要用JDBC连接,环境eclipse 首先查看MySQL中的数据库的存储过程,接着编写代码调用 mysql> show procedure status; +------+-------------+-----------+----------------+-------------

  • MySQL实现类似于connect_by_isleaf的功能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记录的这个I

  • PHP+Mysql基于事务处理实现转账功能的方法

    本文实例讲述了PHP+Mysql基于事务处理实现转账功能的方法.分享给大家供大家参考.具体如下: <?php header("Content-Type:text/html;charset=utf-8"); $mysqli=new mysqli("localhost","root","","test"); if(mysqli_connect_errno()) { printf("连接失败:%s

  • windows和linux安装mysql后启用日志管理功能的方法

    查看是否启用了日志 复制代码 代码如下: mysql>show variables like 'log_bin'; 怎样知道当前的日志 复制代码 代码如下: mysql> show master status; 展示二进制日志数目 复制代码 代码如下: mysql> show master logs; 看二进制日志文件用mysqlbinlog 复制代码 代码如下: shell>mysqlbinlog mail-bin.000001 或者 复制代码 代码如下: shell>my

  • MySQL开启慢查询日志功能的方法

    mysql慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里是否有很耗费资源的sql语句,这是一个有用的日志.它对于性能的影响不大(假设所有查询都很快),并且强调了那些最需要注意的查询(丢失了索引或索引没有得到最佳应用),那如何打开mysql的慢查询日志记录呢? 开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能. (1)配置开启 Linux: 在mysql配置文件 my.cnf 中增加如下语句: log-slow-qu

  • php+mysql结合Ajax实现点赞功能完整实例

    本文实例讲述了php+mysql结合Ajax实现点赞功能的方法.分享给大家供大家参考.具体如下: 要实现点赞功能,有多种实现方式,这里总结一下利用Ajax,php和mysql来实现点赞的数据的功能.具体步骤如下: 一.页面中的HTML代码部分: <span>0</span> <button onclick="goodplus(1);">good+1</button> <span>0</span> <butto

  • php利用scws实现mysql全文搜索功能的方法

    本文实例讲述了php利用scws实现mysql全文搜索功能的方法.分享给大家供大家参考.具体方法如下: scws这样的中文分词插件比较不错,简单的学习了一下,它包涵一些专有名称.人名.地名.数字年代等规则集合,可以直接将语句按这些规则分开成一个一个关键词,准确率在90%-95%之间,按照安装说明把scws的扩展放入php的扩展目录里,下载规则文件和词典文件,并在php配置文件中引用它们,就可以用scws进行分词了. 1) 修改 php 扩展代码以兼容支持 php 5.4.x 2) 修正 php

  • Python操作mysql数据库实现增删查改功能的方法

    本文实例讲述了Python操作mysql数据库实现增删查改功能的方法.分享给大家供大家参考,具体如下: #coding=utf-8 import MySQLdb class Mysql_Oper: def __init__(self,host,user,passwd,db): self.host=host self.user=user self.passwd=passwd self.database=db def db_connecet(self): try: #连接 conn=MySQLdb.

  • Mysql 数据库双机热备的配置方法

    1.mysql数据库没有增量备份的机制,当数据量太大的时候备份是一个很大的问题.还好mysql数据库提供了一种主从备份的机制,其实就是把主数据库的所有的数据同时写到备份数据库中.实现mysql数据库的热备份. 2.要想实现双机的热备首先要了解主从数据库服务器的版本的需求.要实现热备mysql的版本都要高于3.2,还有一个基本的原则就是作为从数据库的数据库版本可以高于主服务器数据库的版本,但是不可以低于主服务器的数据库版本. 3.设置主数据库服务器: a.首先查看主服务器的版本是否是支持热备的版本

  • mysql、mssql及oracle分页查询方法详解

    本文实例讲述了mysql.mssql及oracle分页查询方法.分享给大家供大家参考.具体分析如下: 分页查询在web开发中是最常见的一种技术,最近在通过查资料,有一点自己的心得 一.mysql中的分页查询 注: m=(pageNum-1)*pageSize;n= pageSize; pageNum是要查询的页码,pageSize是每次查询的数据量, 方法一: select * from table order by id limit m, n; 该语句的意思为,查询m+n条记录,去掉前m条,返

  • PHP结合Mysql数据库实现留言板功能

    PHP结合Mysql数据库实现留言板功能

    先给大家展示下留言板效果图: 最近看了下PHP基础语法,就想利用这些基本东西实现留言板,也是对基础知识的一个巩固. 什么是留言板?一种可以用来记录,展示文字信息的载体. 现切入正题,说说本次留言板是怎么实现! 首先用户提交留言后,相关内容存入服务器,当他想看的时候后台再把所有留言读出来,最后显示在浏览器上,用户就可以看到留言了. 这其中后台需要便于读写数据的一个工具,我选择mysql数据库来帮助我完成这些事. 我写了主要是三个php文件,分别是: conn.php 连接数据库: addmsg.p

随机推荐