MySQL存储过程

前提：因为之前查询健康百科文章过慢，能力问题暂时优化不了sql，暂时改用存储过程调用，很久没有写存储过程了，而且公司的mysql又是5.1的，也没有oracle的强大，菜鸟一把辛酸泪，先记录一下！

MySQL存储过程语法

原先的sql：在加组合索引的情况下还要3s多！！！

SELECT
        t.id sid,
        t.sectionName sname,
        a.id uuid,
        a.title title,
        a.summary summary,
        a.imageTitle imgUrl 
    FROM
        TB_CM_NewsSections t, TB_CM_NewsSummary a
    WHERE
        3 > (
            SELECT
                COUNT(*) 
            FROM
                TB_CM_NewsSummary b 
            WHERE                
                b.ID>a.ID 
                AND b.Section=a.Section 
                AND b.Status=a.Status
                AND b.IsValid=a.IsValid 
        )
       AND t.parentCode = 'jkbk0'  
       AND t.Status = 0        
       AND a.Section = t.id 
       AND a.Status=0 
       AND a.IsValid=1 
   ORDER BY
       t.sort DESC,
       a.isTop DESC,
       a.Order,
       a.createdTime DESC

执行计划：

存储过程:

CREATE DEFINER = `root`@`%` PROCEDURE `proc_findNewsSummaryBySections`(IN p_sids VARCHAR(100),IN p_parentCode VARCHAR(20), IN p_page INT, IN p_pageSize INT)
BEGIN
   DECLARE v_i INT DEFAULT 0;
   DECLARE v_total INT;
   DECLARE v_section_id INT; 
   DECLARE v_done INT DEFAULT 0; 
   DECLARE v_sql VARCHAR(255);
   # 声明游标 
   DECLARE myCursor CURSOR FOR   
			 (SELECT ID FROM TB_CM_NewsSections WHERE parentCode = p_parentCode AND status = 0 ORDER BY sort DESC);
   # 定义退出标志
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=1; 
    
   DROP TABLE IF EXISTS tmp_table;  
   #临时表存储结果集
   CREATE TEMPORARY TABLE tmp_table(  
     sid INT(11),
     sname VARCHAR(255),
     uuid INT(11),
     title VARCHAR(255),
     summary text,
     imgUrl VARCHAR(255)
  )ENGINE=MyISAM DEFAULT CHARSET=utf8;

   IF (LENGTH(p_sids) = 0) THEN #查询全部
      BEGIN
      # 打开游标
      OPEN myCursor; 
      #循环遍历
      emp_loop: LOOP  
                FETCH myCursor INTO v_section_id;   
		IF v_done=1 THEN       
		LEAVE emp_loop;  
		END IF; 
                #相应的记录插入到临时表 
                SET @stmt =  concat('INSERT INTO tmp_table(sid,sname,uuid,title,summary,imgUrl)
				      SELECT t.id,t.sectionName,a.id,a.title,a.summary,a.imageTitle 
				      FROM TB_CM_NewsSections t, TB_CM_NewsSummary a 
				      WHERE t.ID = ? AND a.Section = t.ID AND a.Status=0 AND a.IsValid=1  
				      ORDER BY a.isTop DESC, a.order,a.createdTime DESC ', 'LIMIT ?,?;');
				      
                 PREPARE s from @stmt;  #mysql5.6不需要，可直接limit page，pageSize
		 SET @sid = v_section_id;
		 SET @page = (p_page - 1)*p_pageSize;
		 SET @pageSize = p_pageSize;
		 EXECUTE s USING @sid,@page,@pageSize;
		 DEALLOCATE PREPARE s;         
		 END LOOP emp_loop;  
       CLOSE myCursor; 
     END;
   ELSE 
     BEGIN
	SET v_total = 1+(LENGTH(p_sids) - LENGTH(REPLACE(p_sids,',','')));#获取字符串(p_sids = '39,40,44')中id的总数;
	WHILE v_i<v_total DO
	SET v_i = v_i + 1;
	SET v_section_id = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(p_sids,',',v_i)),',',1));#从字符串中获取某个id 
	SET @stmt = concat('INSERT INTO tmp_table(sid,sname,uuid,title,summary,imgUrl)
			    SELECT t.id,t.sectionName,a.id,a.title,a.summary,a.imageTitle 
			    FROM TB_CM_NewsSections t, TB_CM_NewsSummary a 
			    WHERE t.ID = ? AND a.Section = t.ID AND a.Status=0 AND a.IsValid=1  
			    ORDER BY a.isTop DESC, a.order,a.createdTime DESC ','LIMIT ?,?;');
	PREPARE s from @stmt;
	SET @sid = cast(v_section_id as UNSIGNED);#字符id转int
	SET @page = (p_page - 1)*p_pageSize;
	SET @pageSize = p_pageSize;
	EXECUTE s USING @sid,@page,@pageSize;
	DEALLOCATE PREPARE s;
	END WHILE;
     END; 
  END IF;
  SELECT * FROM tmp_table;              
  TRUNCATE tmp_table;#清除临时表
END;

调用：

CALL proc_findNewsSummaryBySections('39,40','jkbk0',2,3);
或
CALL proc_findNewsSummaryBySections('','jkbk0',2,3);

mysql存储过程没有oracle强大，不能接受数组类型的参数，因为刚好业务要传入整型的参数，而且mysql也不能接受，所以就只能以"12,23,24,..."字符串传入然后再做相应的转换；

第一：先循环获取id，这里才用如下方式：

-- 得到分割符的总数。

DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string_total`(
f_string VARCHAR(1000),f_delimiter VARCHAR(5)
) RETURNS INT(11)
BEGIN
  -- Get the total number of given string.
  RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')));
END$$
DELIMITER;

-- 得到具体下表的子字符。

DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string`(
f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
  -- Get the separated number of given string.
  DECLARE result VARCHAR(255) DEFAULT '';
  SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
  RETURN result;
END$$
DELIMITER ;

第二：将获取的字符id转为int类型

//cast和convert
cast(v_section_id as UNSIGNED);
或
convert(v_section_id , UNSIGNED);

参考：http://yueliangdao0608.blog.51cto.com/397025/1349748