mysql文法備忘

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類型單主鍵的表。主鍵可以省略為空白，這樣過程會自動尋找主鍵，不過這樣比較慢。rand_data

    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. ...