維護mysql 和postgres 的時候會踩到很多坑秒裕，這里總結(jié)一些埂息，一面后面再遇到良蒸。
如果不習慣簡書的markdown排版可以點這里，后續(xù)的更新可能不一定會及時同步到簡書粮呢。個人筆記版
執(zhí)行sql文件
sql腳本可以通過下面兩種方式執(zhí)行：
-- 直接連接執(zhí)行
mysql --host shanyy.me --user user --database test < test-func.sql
-- source 執(zhí)行
mysql > source some.sql
mycli執(zhí)行sql的時候好像老是會報錯婿失，不知道為啥，可能是我配置的不對吧豪硅，如果遇到同樣的情況換mysql自帶的就好了懒浮。
mysql的函數(shù)(function)和過程(procedure)
在維護mysql和procedure中總會遇到需要有些無法通過簡單的sql完成的需求砚著，一般會想到sql腳本完成痴昧。這時候就會遇到sql函數(shù)和過程赶撰。函數(shù)和過程很像扣囊，在官方文檔里也是放在一起講的侵歇。mysql官方文檔——函數(shù)和過程
函數(shù)
先說函數(shù)惕虑，函數(shù)相對比較簡單溃蔫，而且限制也多一些。下面是一個官方文檔抄過來的例子：
-- 定義函數(shù)名和輸入?yún)?shù)
mysql> CREATE FUNCTION hello (s CHAR(20))
-- 定義返回參數(shù)類型
mysql> RETURNS CHAR(50)
-- 函數(shù)代碼段
-> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
-- 可以直接在sql語句中執(zhí)行函數(shù)
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
函數(shù)的執(zhí)行過程和內(nèi)置函數(shù)一模一樣私痹，你就把它理解為內(nèi)置函數(shù)就可以了统刮，可以直接在sql中用。
定義一個函數(shù)很簡單暗膜，復雜的函數(shù)也就是函數(shù)體可能復雜點鞭衩，下面是一個稍微復雜點的论衍，實際上這個函數(shù)屁用沒有饲齐，就是為了展示幾個常見問題：
drop function if exists test;
DELIMITER $$
create function test()
returns varchar(100) NONDETERMINISTIC
begin
declare size int;
declare k int;
declare s datetime;
declare m float;
declare x double;
declare c1 int;
set size = 1000;
set k = 0;
while k < size do
select cast(count(*) as char) into c1 from draft;
set k = k + 1;
end while;
repeat
select cast(count(*) as char) into c1 from draft;
set k = k - 1;
until k > 0
end repeat;
if c1 > 1000 then
set c1 = 999;
else
set c1 = 1001;
end if;
return c1;
end $$
DELIMITER ;
為了大家好復制捂人，就不在代碼片里直接注釋了滥搭，下面來逐行分析瑟匆。
drop function if exists test;
- [x] 如果已經(jīng)存在test就丟棄掉疾嗅，不然第二次創(chuàng)建函數(shù)的時候會報重名的錯誤冕象。
DELIMITER $$
- [x] 定義代碼片分隔符渐扮，mysql默認見到分號就執(zhí)行了论悴，這里設(shè)置為
$$表明見到$$就執(zhí)行，可以理解問分號膀估。后面可以看到執(zhí)行完馬上又復原了。這個分隔符你想怎么取就怎么取察纯，只要不和其他的沖突就行帕棉，比如官方文檔里經(jīng)常會取//饼记。
returns varchar(100) NONDETERMINISTIC
- [x] 這里可以多了一個NONDETERMINISTIC笤昨，這個主要是標識你的函數(shù)在給定輸入的時候輸出是否固定的，類似純函數(shù)什么是純函數(shù)握恳，為了簡化分析瞒窒，后面統(tǒng)一稱其為純函數(shù)（可能會不嚴謹）乡洼。
如果是純函數(shù)束昵，則可以寫成DETERMINISTIC，如果不是則可以寫成NONDETERMINISTIC礁遵。如果不寫默認為不是純函數(shù)轻绞。按官方文檔說的是政勃，如果把純函數(shù)標識為非純函數(shù)兼砖，則可能會導致性能下降奸远，或者某些性能優(yōu)化策略失效；反之讽挟，把非純函數(shù)標識為純函數(shù)可能導致結(jié)果不對懒叛。所以如果不確定是不是純函數(shù)還是缺省比較保險。其實判斷是不是純函數(shù)的方法很簡單耽梅，主要看你函數(shù)里是否引入了外部變量薛窥，比如查表，隨機數(shù)啊之類的褐墅。上面給出官方的例子就是一個純函數(shù)拆檬，后面那個復雜一點的就是一個非純函數(shù)。
按說只要函數(shù)中調(diào)用了now或者rand就肯定是非純函數(shù)妥凳，標記錯了計算結(jié)果可能會出錯竟贯。不過mysql文檔說明了，可以認為系統(tǒng)會把隨機數(shù)和時間戳作為默認參數(shù)傳到函數(shù)中去逝钥，所以即便標記成NONDETERMINISTIC也不會出錯屑那。不過官方文檔只是說如果你只調(diào)用一次rand才不會出錯。原話如下：
A routine that contains the NOW() function (or its synonyms) or RAND() is nondeterministic, but it might still be replication-safe. For NOW(), the binary log includes the timestamp and replicates correctly. RAND() also replicates correctly as long as it is called only a single time during the execution of a routine. (You can consider the routine execution timestamp and random number seed as implicit inputs that are identical on the master and slave.)
begin
...
end
- [x] begin和end組成了一個代碼片艘款，你可以在里面執(zhí)行函數(shù)體的代碼持际，比如這里的函數(shù)就在begin和end里。只需要注意別把return之類的句子寫到end后面就行了哗咆。
- [ ] 如果函數(shù)體代碼不是直接返回蜘欲，就需要加一個begin...end包一下。
- [x] 后面的就是基本語法了晌柬，這里寫的三個是比較常用的姥份，兩個循環(huán)一個條件判斷，實際用的過程中只需要照葫蘆畫瓢就行了年碘，人家非要這么寫澈歉，你也沒辦法。
基本常用的函數(shù)就照這個這個復雜的例子改改就可以了屿衅，不過需要注意的是
declare一定要放在最前面埃难，比如上面你把從c1的declare放到set的后面看著也沒啥問題，實際上就是會報錯涤久，我也不知道為啥涡尘。
有了這些基礎(chǔ)，我們開始嘗試著做一些完成一個很常見的需求：如何用mysql快速填充一個測試表响迂。雖然利用python或者nodejs填充表格也很簡單悟衩，但是效率最高的還是直接使用sql來做。
于是乎栓拜，我們嘗試寫出了下面的代碼：
-- 實際上這個代碼是無法執(zhí)行的Ｗ尽！幕与！
drop function if exists initProj;
DELIMITER $$
create function initProj()
begin
declare size int;
declare k int;
drop table tmp_table;
create table tmp_table ( id int(11) not null auto_increment primary key,
time datetime,
rand_data int(10),
rand_str varchar(100));
set size = 1000;
set k = 0;
while size > k do
insert into tmp_table (time, rand_data, rand_str) values (now(), 1000000 * rand(), rand_string(100));
set k = k + 1;
end while ;
end $$
DELIMITER ;
這里之所以錯誤是因為mysql不允許隱式或顯示提交挑势，簡單理解就是
drop table,create table,truncate table之類的操作都沒法執(zhí)行。有時候你需要清理表格可以考慮利用delete from some_table where id > 0;來做啦鸣，不過不建議這么做潮饱，下面介紹的procedure來做更加優(yōu)雅。
過程
過程和函數(shù)很類似诫给，只不過過程的限制沒有函數(shù)那么多香拉，帶來的副作用就是調(diào)用過程需要主動地call procedure而不是直接在sql中調(diào)用啦扬。
-- 調(diào)用procedure的例子
-- \G 參數(shù)主要是輸出的排版更加友好
call exp_procedure() \G;
過程的定義，函數(shù)體都和函數(shù)是一樣的凫碌，這里就不具體介紹了扑毡，可以簡單理解procedure為一坨代碼放到一起實現(xiàn)一個功能。下面是利用procedure和function一起實現(xiàn)的初始化表格的需求盛险。
-- 生成隨機字符串
DELIMITER $$
drop function if exists rand_string;
CREATE FUNCTION `rand_string`(n INT)
RETURNS varchar(255)
BEGIN
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END $$
DELIMITER ;
select rand_string(100);
-- 初始化表格
drop procedure if exists initProj;
DELIMITER $$
create procedure initProj(size int)
begin
declare k int;
drop table tmp_table;
create table tmp_table ( id int(11) not null auto_increment primary key,
time datetime,
rand_data int(10),
rand_str varchar(100));
set k = 0;
while size > k do
insert into tmp_table (time, rand_data, rand_str) values (now(), 1000000 * rand(), rand_string(100));
set k = k + 1;
end while ;
end $$
DELIMITER ;
只需要call initProj(100000);即可完成初始化瞄摊。
其中隨機字符串為了圖簡單沒有自己寫，網(wǎng)上一搜一大堆苦掘，我找了一個拿過來用了换帜，丟掉了一些我不太關(guān)心的內(nèi)容。比如權(quán)限之類的鹤啡，有興趣可以自己去看惯驼，這里吧連接放出來了。隨機字符串博客鏈接
最后初始化十萬個數(shù)據(jù)花費了5分鐘的時間递瑰， 如果不做隨機跳座，速度會更快。
