MySQL-procedure（loop，repeat）

標籤：

在 MySQL-procedure（cursor,loop） 中將spam_keyword表中的文字全部分割到t表當中，且每一行的字都不重複，那t表可以用來當作一個小字典，只有1000來個字元，這次把t表當作字元來源，寫一個”以 t 表為字元庫產生不定長隨機字元的procedure“。

1、t表使用的是InnoDB引擎，為了有個區別比較，再建立一個t2表，用MyISAM引擎並複製t的資料，共1023行記錄

create table t2 like t;alter table t2 engine=myisam；insert into t2 select *from t;

CREATE TABLE `t2` (  `id` int(11) DEFAULT NULL,-- 行號id，不重複，共1023行  `t` varchar(3) DEFAULT NULL,  `cnt` int(11) DEFAULT NULL,  KEY `idx_id` (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

2、再建一個表，放放產生的隨機資料，表名 tx ，列 x ，每行存放0到10個字元。

CREATE TABLE `tx` (  `x` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

3、預存程序，用rand()產生10以內的隨機數來確定每個插入字串的長度，插入50000次

 1 drop procedure if exists proctx; 2 create procedure proctx() 3 begin 4 declare xid int;-- 隨機來源id 5 declare xstr varchar(1);-- 來源id對應的單字 6 declare oid int;-- 字串隨機增長計數 7 declare xostr varchar(10);-- 字串結果 8 declare txid int;-- 全體迴圈計數 9 set txid=0;10 truncate tx;11 12 loop1:loop13 set txid=txid+1;14 set oid=ceil(rand()*10);--  用隨機產生的迴圈次數來決定字元的長度15 set xostr=‘‘;-- 在進入增長迴圈前要定為空白字串，否則預設為null，concat連null的結果都是null16 17 repeat18 set xid=ceil(rand()*1023);19 select t into xstr from t where id=xid;20 set xostr=CONCAT(xstr,xostr);-- 最終要插入 tx 表的字串結果21 set oid=oid-1;22 until oid=0 end repeat;23 24 insert into tx set x=xostr;-- 增長結束後插入表25 if txid>=50000 then leave loop1;end if;26 end loop;27 end;

寫過程中一開始沒有注意要set xostr ，給這個字串變數一個初始值，使它在後面的concat函數中的結果都變成了null，結果就是在tx表中插入了全是null

4、call proctx 後看結果

從 t 表，InnoDB，441.677s，7 min 21 s，113.205 lines per sec

[SQL]call proctx()受影響的行: 1時間: 441.677s

mysql> select * from tx;+----------------------+| x                    |+----------------------+| 災#中育知斑列燈郎    || 無降                 || u松戲                || 扁                   || 訂賣櫃試擊比所店     || 濕如個               || 癬表億               || 龍石周價險           || {糯                  || Ｑ門裝寄司口附妻     |......省略......

修改過程，從 t2 表，MyISAM，439.339s，7 min 19 s，113.895 lines per sec

[SQL]call proctx()受影響的行: 1時間: 439.339s

mysql> select * from tx;+----------------------+| x                    |+----------------------+| 象死宮勢拍李反       || p南展                || 分進卡               || 旗接酒z弱樂晗揭      || 好富正奇陰園找縮     || 風G起旗.證雅於       || 計w合                || 鄭麻債空義海門箱招生 || 差                   || 你高幹加六非認自徐   |
......省略......

兩個執行結果沒有差距，不能說明什麼問題，而且由於兩次執行過程中實際產生的迴圈操作次數應該是不一樣的，因為隨機數的不同，其實兩個結果的可比性還和隨機數的性質有關聯。另外僅僅是5w條的隨機數用了7分多鐘，實在是慢了點。如果能在幾分鐘內完成5kw的插入，說不定還能將隨機數對時間產生的影響比例縮小，“那就得看這個隨機到底是真隨機還是假隨機了”，兩個引擎客觀上應該是存在查詢效能不同的特點的，但是這次這個實驗應該是用錯了測驗對象，t 表記錄量少，procedure語句執行時間側重迴圈和隨機了大概是。雖然沒有檢驗出兩種引擎的特點，但是產生隨機字串的目的還是達到了，就是效能有待最佳化。

MySQL-procedure（loop，repeat）