1. 存储过程和游标
   核心： 
   #变量必须声明在最前面 declare noMoreRows bool default false; declare _id int; declare _name varchar(25);      #声明游标 declare xxRows cursor for select * from xx;      #当游标移动到最后空行时设置noMoreRows为true declare CONTINUE handler for NOT FOUND set noMoreRows=true;      #打开游标 open xxRows;      #创建临时内存表 drop table if exists xxTem; create temporary table xxTem      (id int,name varchar(25), primary key(id))      engine=memory; truncate table xxTem;      #遍历游标      repeat          #取出数据到变量 fetch xxRows into _id,_name;             #过滤掉重复行，将游标取出结果插入到临时表        if not exists(select 1 from xxTem where id=_id) then insert into xxTem(id,name) values(_id,_name); end if;              until noMoreRows=1 end repeat;      #关闭游标 close xxRows;       #根据参数裁剪临时表结果返回 select * from xxTem limit `skip`,`count`;
   完整： 
   CREATE TABLE `xx` (     `id` INT(11) NOT NULL,     `name` VARCHAR(45) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB; CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`(IN `skip` int, IN `count` int)     LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL     SQL SECURITY DEFINER     COMMENT '' BEGIN         #变量必须声明在最前面 declare noMoreRows bool default false; declare _id int; declare _name varchar(25);      #声明游标 declare xxRows cursor for select * from xx;      #当游标移动到最后空行时设置noMoreRows为true declare CONTINUE handler for NOT FOUND set noMoreRows=true;      #打开游标 open xxRows;      #创建临时内存表 drop table if exists xxTem; create temporary table xxTem      (id int,name varchar(25), primary key(id))      engine=memory; truncate table xxTem;      #遍历游标      repeat          #取出数据到变量 fetch xxRows into _id,_name;             #过滤掉重复行，将游标取出结果插入到临时表        if not exists(select 1 from xxTem where id=_id) then insert into xxTem(id,name) values(_id,_name); end if;              until noMoreRows=1 end repeat;      #关闭游标 close xxRows;       #根据参数裁剪临时表结果返回 select * from xxTem limit `skip`,`count`; END
2. 动态sql：随机获取某表的若干行
   下面是存储过程的代码。过程包含三个参数，分别是表名，行数和主键id。过程只支持int类型单主键的表。主键可以省略为空，这样过程会自动查找主键，不过这样比较慢。
   1 CREATE DEFINER=`root`@`%` PROCEDURE `rand_data`(IN `tbName` VARCHAR(50), IN `rowCnt` INT, IN `tbKey` VARCHAR(50))  2     LANGUAGE SQL  3     NOT DETERMINISTIC  4     CONTAINS SQL  5     SQL SECURITY DEFINER  6     COMMENT '随机获取若干记录,只适用于单主键表'  7 BEGIN  8  9     #获取主键名 10     IF tbKey IS NOT NULL THEN 11         SET @tbKey=tbKey;#参数里面已经有，这种情况比较快 12     ELSE 13         #参数里面没，从系统表查找主键，比较耗时 14         SELECT @tbKey:=c.COLUMN_NAME 15         FROM 16               INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, 17               INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c 18         WHERE 19               t.TABLE_NAME = c.TABLE_NAME 20               AND t.TABLE_SCHEMA = c.TABLE_SCHEMA 21             AND t.TABLE_SCHEMA = database() 22             AND t.TABLE_NAME = tbName 23             AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'; 24     END IF; 25         26     #获取最大id,最小id和记录数 27     SET @getMaxIdSql = CONCAT('SELECT @maxId:=MAX(', @tbKey, '),', 28         '@minId:=MIN(', @tbKey, '),', 29         '@totalCnt:=COUNT(', @tbKey, ')', 30         ' FROM `', tbName, '`;'); 31     PREPARE getMaxId FROM @getMaxIdSql; 32     EXECUTE getMaxId; 33     DEALLOCATE PREPARE getMaxId; 34     35     #创建临时表 36     DROP TABLE IF EXISTS rand_tt; 37     SET @temTbSql = CONCAT('CREATE TEMPORARY TABLE rand_tt SELECT 0 aid,tb.* FROM `',tbName,'` tb LIMIT 0;'); 38     PREPARE temTb FROM @temTbSql; 39     EXECUTE temTb; 40     DEALLOCATE PREPARE temTb; 41     42     #构建获取一条记录的sql 43     SET @randRowSql = CONCAT('INSERT INTO rand_tt SELECT @cnt:=@cnt+1 aid,tb.* FROM ', 44         tbName, ' tb WHERE tb.', @tbKey, '=?;'); 45     PREPARE addRow FROM @randRowSql; 46     47     #生成随机记录 48     SET @cnt=0; 49     insertLoop: LOOP 50         SET @id=FLOOR(RAND()*(@maxId-@minId)+@minId); 51         IF NOT EXISTS (SELECT id FROM rand_tt WHERE id=@id) THEN 52             EXECUTE addRow USING @id; 53             IF @cnt >= rowCnt OR @cnt >= @totalCnt THEN 54                 LEAVE insertLoop; 55             END IF; 56         END IF; 57     END LOOP insertLoop;    58     DEALLOCATE PREPARE addRow; 59     60     #返回数据 61     ALTER TABLE rand_tt DROP COLUMN aid; 62     SELECT * FROM rand_tt; 63 END
3. ...