最近公司生產(chǎn)環(huán)境的系統(tǒng)有個(gè)需求，需要往mysql數(shù)據(jù)庫的一張表里批量添加數(shù)據(jù)，業(yè)務(wù)部門給的數(shù)據(jù)大致如下：

這樣的數(shù)據(jù)，首先想到的是在我的macOS上連上服務(wù)器數(shù)據(jù)庫，然后把數(shù)據(jù)一條一條insert進(jìn)去。但是我insert了幾十條后，我發(fā)現(xiàn)業(yè)務(wù)部門給的數(shù)據(jù)累計(jì)有700多條。我當(dāng)然不愿意就這樣老老實(shí)實(shí)的一條一條insert了，因?yàn)閷?shí)在是太耗費(fèi)時(shí)間了。

根據(jù)以往的經(jīng)驗(yàn)，最先想到的是，把表導(dǎo)入到navicat里，然后生成批量sql插入腳本，再傳到服務(wù)器上去執(zhí)行。但是，業(yè)務(wù)部門給的Excel并不是標(biāo)準(zhǔn)的一條一條的數(shù)據(jù)展示的，而是像上面那樣給定箱子條碼范圍和數(shù)量。這樣，我還得先給它生成一個(gè)標(biāo)準(zhǔn)的Excel，也比較麻煩。

然后我想到用寫程序的方式，寫循環(huán)，連接數(shù)據(jù)庫插入數(shù)據(jù)。但是本地環(huán)境去連接生產(chǎn)環(huán)境的數(shù)據(jù)庫是需要走流程申請權(quán)限的，可能流程要走個(gè)一兩天，業(yè)務(wù)部門又急著要這個(gè)數(shù)據(jù)，這種方法又要摒棄掉了，只能使用下面一種方案了。

mysql自定義function實(shí)現(xiàn)批量添加數(shù)據(jù)

mysql定義function語法如下：

CREATE FUNCTION func_name ( [func_parameter] ) //括號是必須的，參數(shù)是可選的  
RETURNS type  
[ characteristic ...] routine_body 

• CREATE FUNCTION 用來創(chuàng)建函數(shù)的關(guān)鍵字
• func_name 表示函數(shù)的名稱
• func_parameters為函數(shù)的參數(shù)列表，參數(shù)列表的形式為：[IN|OUT|INOUT] param_name type
  1. IN：表示輸入?yún)?shù)
  2. OUT：表示輸出參數(shù)
  3. INOUT：表示既可以輸入也可以輸出
  4. param_name：表示參數(shù)的名稱
  5. type：表示參數(shù)的類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型
• RETURNS type：語句表示函數(shù)返回?cái)?shù)據(jù)的類型
• characteristic: 指定函數(shù)的特性，取值與存儲過程的定義相同，可以有以下幾種取值方式
  1. LANGUAGE SQL: 說明routine_body部分由SQL語句組成，當(dāng)前系統(tǒng)支持的語言為SQL, SQL是LANGUAGE特性的唯一值
  2. [NOT] DETERMINISTIC: 指明存儲過程執(zhí)行的結(jié)果是否正確。DETERMINISTIC表示結(jié)果是確定的。每次執(zhí)行存儲過程時(shí)，相同的輸入會得到相同的輸出；
     而NOT DETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個(gè)值，默認(rèn)為NOT DETERMINISTIC
  3. {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}: 指明子程序使用SQL語句限制
     1. CONTAINS SQL表明子程序包含SQL語句，但是不包含讀寫數(shù)據(jù)的語句
     2. NO SQL表明子程序不包含SQL語句
     3. READS SQL DATA說明子程序包含讀寫數(shù)據(jù)的語句
     4. MODIFIES SQL DATA表明子程序包含寫數(shù)據(jù)的語句
     5. 默認(rèn)情況下，系統(tǒng)會指定為CONTAINS SQL
  4. SQL SECURITY { DEFINER|INVOKER}: 指明誰有權(quán)限來執(zhí)行。DEFINER表示只有定義存儲過程者才能執(zhí)行；INVOKER表示擁有權(quán)限的調(diào)用者可以執(zhí)行。
     默認(rèn)情況下，系統(tǒng)指定為DEFINER
  5. COMMENT 'string': 注釋信息，可以用來描述存儲過程或者函數(shù)
• routine_body是SQL代碼內(nèi)容，可以用BEGIN...END來表示SQL代碼的開始與結(jié)束

實(shí)現(xiàn)批量添加數(shù)據(jù)的function過程如下：

首先，我把服務(wù)器數(shù)據(jù)庫這張表的表結(jié)構(gòu)拷到了本地，便于本地調(diào)試所寫function是否正確。該表結(jié)構(gòu)與所需要插入的數(shù)據(jù)相關(guān)的字段有三個(gè):

ybxtm, dept_id, box_size //dept_id對應(yīng)上面數(shù)據(jù)的中心

然后，實(shí)現(xiàn)function實(shí)現(xiàn)及調(diào)用：

DELIMITER $$ /*定義分隔符，用于在Linux環(huán)境下防止function定義的內(nèi)部分號造成的中途輸出*/
CREATE FUNCTION addData(deptId INT, boxSize DOUBLE, ybxtm VARCHAR(20), amount INT)
  RETURNS INT DETERMINISTIC /*該參數(shù)需要指定，不指定Linux環(huán)境下執(zhí)行時(shí)會提示你需要指定function的characteristic*/
  BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE result INT DEFAULT 1;
    DECLARE sampleBoxNum VARCHAR(20) DEFAULT '';
    myloop: LOOP
      SET sampleBoxNum = ybxtm;
      IF i > amount
      THEN LEAVE myloop;
      END IF;
      IF i < 10
      THEN SET sampleBoxNum = concat(sampleBoxNum, concat('0', i));
      ELSE
        SET sampleBoxNum = concat(sampleBoxNum, i);
      END IF;
      INSERT INTO lx_ybx (ybxtm, dept_id, box_size) VALUES (sampleBoxNum, deptId, boxSize);
      SET result = result + 1;
      SET i = i + 1;
    END LOOP myloop;
    RETURN result - 1;
  END $$ /*結(jié)束時(shí)需要掛上分隔符，告訴Linux終端，function定義結(jié)束*/
 
/*調(diào)用function*/  
SELECT addData(29, 20, 'X025714', 78);