MySQL常見的腳本語句格式參考指南

更新時間：2022年06月07日 16:37:56 作者：lang20150928

無論是運維、開發(fā)、測試,還是架構(gòu)師,數(shù)據(jù)庫技術(shù)是一個必備加薪神器,下面這篇文章主要給大家介紹了關(guān)于MySQL常見的腳本語句格式參考指南的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

表整體（TABLE ）操作

針對數(shù)據(jù)庫的表進行新增操作，考慮到腳本可重復(fù)執(zhí)行，有以下兩種方案

使用TRYADDTABLE存儲過程

CALL TRYADDTABLE('ACT_GE_PROPERTY', 'CREATE TABLE ACT_GE_PROPERTY  (
  NAME_ varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  VALUE_ varchar(600) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  REV_ decimal(22, 0) NULL DEFAULT NULL,
  PRIMARY KEY (NAME_) USING BTREE,
  INDEX SYS_C001769640(NAME_) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;')

使用原生判斷語句

CREATE TABLE IF NOT EXISTS ACT_GE_PROPERTY  (
  NAME_ varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  VALUE_ varchar(600) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  REV_ decimal(22, 0) NULL DEFAULT NULL,
  PRIMARY KEY (NAME_) USING BTREE,
  INDEX SYS_C001769640(NAME_) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

使用以上兩種方式可能存在一個問題，就是數(shù)據(jù)庫中存在一個表結(jié)構(gòu)不同的表，此時以上的語句其實就是無效的，很多人都會采用先刪表然后再建表的方式，如下所示

DROP TABLE IF EXISTS ACT_GE_PROPERTY;
CREATE TABLE ACT_GE_PROPERTY  (
  NAME_ varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  VALUE_ varchar(600) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  REV_ decimal(22, 0) NULL DEFAULT NULL,
  PRIMARY KEY (NAME_) USING BTREE,
  INDEX SYS_C001769640(NAME_) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

但是刪除表是存在巨大風(fēng)險的，如果只是一些緩存表倒還好，如果是存放重要業(yè)務(wù)數(shù)據(jù)或者參數(shù)信息的表，刪除表會導(dǎo)致程序運行失敗。所以是不允許直接刪除表的，那么如何避免數(shù)據(jù)庫存在同樣表名而結(jié)構(gòu)不同呢？可以考慮在建表語句后面添加一個查詢語句，其中包含完整的字段。

SELECT NAME_,VALUE_,REV_ FROM ACT_GE_PROPERTY LIMIT 0,1;

一旦表結(jié)構(gòu)不一致，就需要考慮針對表字段的增刪操作以及數(shù)據(jù)同步操作。

刪除表的操作是存在巨大風(fēng)險的，刪除表之前必須準備評估是否導(dǎo)致業(yè)務(wù)數(shù)據(jù)丟失的問題。

表字段（COLUMN ）操作

新增表字段

通過TRYADDTABCOLUMN存儲過程保證可重復(fù)執(zhí)行。

CALL TRYADDTABCOLUMN('ACT_GE_PROPERTY','VALUE_ ','varchar(600) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL');

修改表字段

原生語句已經(jīng)支持可重復(fù)執(zhí)行，直接使用即可

ALTER TABLE ACT_GE_PROPERTY MODIFY COLUMN VALUE_ varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

刪除表字段

刪除表字段同樣是存在巨大風(fēng)險的，需要仔細評估?？赡軙婕暗膱龊暇褪菍⒛硞€字段替換為另一個字段（二者名稱不一致）。通過先刪除字段然后再添加字段會導(dǎo)致原來數(shù)據(jù)丟失，MySQL中可以通過ALTER TABLE CHANGE語句來修改字段。但是沒法保證可重復(fù)性。

-- 將ACT_GE_PROPERTY字段VALUE_的名字修改為VALUE2_
-- 對應(yīng)的MySQL原語句 ALTER TABLE ACT_GE_PROPERTY CHANGE VALUE_ VALUE2_ varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
CALL TRYCHANGETABCOLUMN('ACT_GE_PROPERTY','VALUE_','VALUE2_','varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL');
-- 將ACT_GE_PROPERTY字段VALUE2_的名字修改為VALUE_
CALL TRYCHANGETABCOLUMN('ACT_GE_PROPERTY','VALUE2_','VALUE_','varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL');

表索引（INDEX ）操作

添加索引

主要考慮可重復(fù)執(zhí)行問題，首先通過TRYDROPINDEX刪除索引。然后再創(chuàng)建索引

-- 創(chuàng)建普通索引
CALL TRYDROPINDEX('ACT_GE_PROPERTY','IDX_REV_');
CREATE INDEX IDX_REV_ ON ACT_GE_PROPERTY (REV_ ASC)  USING BTREE;
-- 創(chuàng)建唯一索引
CALL TRYDROPINDEX('ACT_GE_PROPERTY','IDX_VALUE_');
CREATE UNIQUE INDEX IDX_VALUE_ ON ACT_GE_PROPERTY (VALUE_ ASC)  USING BTREE;
-- 多個字段的索引
CALL TRYDROPINDEX('ACT_GE_PROPERTY','IDX_VALUE_REV_');
CREATE UNIQUE INDEX IDX_VALUE_REV_ ON ACT_GE_PROPERTY (VALUE_ ASC,REV_ ASC)  USING BTREE;

刪除索引

考慮可重復(fù)執(zhí)行問題，直接使用存儲過程。

CALL TRYDROPINDEX('ACT_GE_PROPERTY','IDX_REV_');
CALL TRYDROPINDEX('ACT_GE_PROPERTY','IDX_VALUE_');
CALL TRYDROPINDEX('ACT_GE_PROPERTY','IDX_VALUE_REV_');

表數(shù)據(jù)操作

新增數(shù)據(jù)

絕大多數(shù)的表新增數(shù)據(jù)都只需考慮先根據(jù)主鍵或者關(guān)鍵字刪除，然后插入數(shù)據(jù)即可。如下所示

DELETE FROM act_ge_property WHERE NAME_ IN('next.dbid','schema.history','schema.version');
INSERT INTO act_ge_property(NAME_, VALUE_, REV_) VALUES ('next.dbid', '1215401', 12155);
INSERT INTO act_ge_property(NAME_, VALUE_, REV_) VALUES ('schema.history', 'create(5.15.1)', 1);
INSERT INTO act_ge_property(NAME_, VALUE_, REV_) VALUES ('schema.version', '5.15.1', 1);
COMMIT;

以上表是沒有數(shù)字型的主鍵的，如果涉及到數(shù)據(jù)型主鍵的，有兩種方案：

第一種采用主鍵自增，只用在表結(jié)構(gòu)定義的時候?qū)⒅麈I字段設(shè)置為AUTO_INCREMENT即可。

第二種模仿oracle中的序列，在mysql中通過表SEQUENCE維護這些序列信息。在插入數(shù)據(jù)的時候，需要通過存儲過程getSequenceNextVal獲取序列值（其中S_TSYS_CUSTOMER對應(yīng)SEQUENCE表的SEQ_NAME字段）

DELETE FROM TSYS_CUSTOMER WHERE S_CODE = 'PY2022';
INSERT INTO `TSYS_CUSTOMER`(`S_ID`, `S_CODE`, `C_CODE`, `C_NAME`) VALUES (getSequenceNextVal('S_TSYS_CUSTOMER'),  'PY2022', 'PUFA_BANK', '浦銀自營');
COMMIT;

但是有部分的表是不允許這樣操作的，比如TPARA表、SEQUENCE表。前者在規(guī)范（http://191.168.0.126/svn/xIR_Web_J2EE/2.銀行資管/文檔/技術(shù)文檔/腳本規(guī)范/初始化TPARA數(shù)據(jù)腳本規(guī)范.html）中已經(jīng)詳述，后者在規(guī)范（http://191.168.0.126/svn/xIR_Web_J2EE/2.銀行資管/文檔/技術(shù)文檔/腳本規(guī)范/MySQL序列操作規(guī)范.html）中詳述。

TPARA表新增數(shù)據(jù)

CALL TRYEXCUTEIFNOTEXISTS('TPARA','P_CODE','IsUsedInstVerifyApproval','
 INSERT INTO TPARA(P_ID, P_NAME, P_CODE, P_VALUE, P_VALUE_NAME, P_TYPE, P_ISVISIBLE, P_ENUM_VALUE, P_ENUM_TYPE, P_ISMULTI, P_CREAT_DATE, P_REMARK) VALUES (''31020'', ''是否使用指令驗證審批流程'', ''IsUsedInstVerifyApproval'', ''false'', ''否'', ''3'', ''1'', ''true_是@false_否'', ''1'', ''0'', ''20171124'', NULL)
');

SEQUENCE表新增數(shù)據(jù)

CALL TRYADDSEQUENCE('S_TTRD_AUTH_PORTAL_CONFIG', 'INSERT INTO SEQUENCE(SEQ_NAME, CURRENT_VAL, INCREMENT_VAL, MIN_VAL, MAX_VAL, CACHE_SIZE) VALUES (''S_TTRD_AUTH_PORTAL_CONFIG'', 1, 1, 0, 9223372036854775807, 20)');

新增表數(shù)據(jù)類似于初始化操作，如果這個數(shù)據(jù)不會變化，無論操作多少次都是一樣的，先刪除后插入沒有任何問題。但是這個數(shù)據(jù)可能會被修改的時候，就要考慮你的腳本會不會因為重復(fù)執(zhí)行導(dǎo)致業(yè)務(wù)沒法操作了。比如序列初始化的時候是1，然后在程序里使用了一段時間，再次執(zhí)行你的腳本，數(shù)據(jù)庫的序列值回去了，程序肯定會報錯的（對應(yīng)的主鍵值已經(jīng)存在了）。

FUNCTION 函數(shù)操作

DROP FUNCTION IF EXISTS GZB_GET_PRECISION_VALUE;
-- 在創(chuàng)建函數(shù)語句前修改默認分割符
DELIMITER $
CREATE FUNCTION GZB_GET_PRECISION_VALUE(VAL VARCHAR(100),V_CASH_ACCT_ID VARCHAR(10)) RETURNS VARCHAR(100) 
BEGIN
  DECLARE  RET_VAL VARCHAR(100);
  DECLARE V_INDEX_PRECISION INTEGER ;
  DECLARE V_CALC_TYPE VARCHAR(1);
  IF VAL IS NULL OR V_CASH_ACCT_ID IS NULL THEN
  SET RET_VAL=COALESCE(VAL, '0');
    RETURN RET_VAL;
  ELSE
  SELECT MAX(PRC.INDEX_PRECISION), MAX(PRC.CALC_TYPE)
    INTO V_INDEX_PRECISION, V_CALC_TYPE
    FROM V_GZB_INDEX_PRECISION PRC
    INNER JOIN TTRD_WMPS_DEFINE WPS
      ON PRC.I_CODE = WPS.I_CODE
     AND PRC.A_TYPE = WPS.A_TYPE
     AND PRC.M_TYPE = WPS.M_TYPE
    INNER JOIN TTRD_ACC_CASH ACC_CASH
    ON WPS.UNIT_ID = ACC_CASH.PC1
   WHERE WPS.ZMCP_FLAG = '0'
     AND INSTR(WPS.I_CODE, 'TEMP') = 0
     AND PRC.F_CODE = 'NAV_OTHER'
     AND ACC_CASH.ACCID = V_CASH_ACCT_ID;
  END IF;
   IF COALESCE(V_CALC_TYPE, '1') = '1' THEN
      SET RET_VAL = LEFT(VAL, INSTR(VAL,'.')+COALESCE(V_INDEX_PRECISION, 8));
   ELSE
    SET RET_VAL = ROUND(VAL, COALESCE(V_INDEX_PRECISION, 8));
   END IF;
    RETURN RET_VAL;
-- 結(jié)束時使用指定的分隔符    
END $
--  最后修改分割符為默認值
DELIMITER ;

PROCEDURE 存儲過程操作

DROP PROCEDURE IF EXISTS TRYADDTABCOLUMN;

DELIMITER $
CREATE PROCEDURE `TRYADDTABCOLUMN`(IN tableName VARCHAR(100),IN colName VARCHAR(50),IN colType VARCHAR(1000))
BEGIN
? ? DECLARE colCount INT;
? ? SELECT COUNT(*) INTO colCount FROM information_schema.COLUMNS WHERE TABLE_NAME = tableName AND COLUMN_NAME = colName;
? ? IF(colCount = 0) THEN
? -- @表示全局變量 相當于php $ 拼接賦值 INTO 必須要用全局變量不然語句會報錯
? SET @add_sql = CONCAT('ALTER TABLE ',tableName,' ADD COLUMN ',colName,' ',colType,';');
? -- 預(yù)處理需要執(zhí)行的動態(tài)SQL，其中stmt是一個變量
? PREPARE stmt FROM @add_sql;
? -- 執(zhí)行SQL語句
? EXECUTE stmt;
? -- 釋放掉預(yù)處理段
? deallocate prepare stmt;
END IF;
END $
DELIMITER ;

附錄

TRYADDTABLE存儲過程

DROP PROCEDURE
    IF
    EXISTS TRYADDTABLE;

DELIMITER $
CREATE PROCEDURE `TRYADDTABLE`(IN tableName VARCHAR(50),IN createTableSql VARCHAR(3000))
BEGIN
    DECLARE tableCount INT;
    SELECT COUNT(*) INTO tableCount FROM information_schema.TABLES WHERE TABLE_NAME = tableName;
    IF(tableCount = 0) THEN
	  -- @表示全局變量 相當于php $ 拼接賦值 INTO 必須要用全局變量不然語句會報錯
    SET @create_sql = createTableSql;
    -- 預(yù)處理需要執(zhí)行的動態(tài)SQL，其中stmt是一個變量
    PREPARE stmt FROM @create_sql;
    -- 執(zhí)行SQL語句
    EXECUTE stmt;
    -- 釋放掉預(yù)處理段
    deallocate prepare stmt;
END IF;
END $
DELIMITER ;

TRYADDTABCOLUMN存儲過程

DROP PROCEDURE
    IF
    EXISTS TRYADDTABCOLUMN;

DELIMITER $
CREATE PROCEDURE `TRYADDTABCOLUMN`(IN tableName VARCHAR(100),IN colName VARCHAR(50),IN colType VARCHAR(1000))
BEGIN
    DECLARE colCount INT;
    SELECT COUNT(*) INTO colCount FROM information_schema.COLUMNS WHERE TABLE_NAME = tableName AND COLUMN_NAME = colName;
    IF(colCount = 0) THEN
  -- @表示全局變量 相當于php $ 拼接賦值 INTO 必須要用全局變量不然語句會報錯
  SET @add_sql = CONCAT('ALTER TABLE ',tableName,' ADD COLUMN ',colName,' ',colType,';');
  -- 預(yù)處理需要執(zhí)行的動態(tài)SQL，其中stmt是一個變量
  PREPARE stmt FROM @add_sql;
  -- 執(zhí)行SQL語句
  EXECUTE stmt;
  -- 釋放掉預(yù)處理段
  deallocate prepare stmt;
END IF;
END $
DELIMITER ;

TRYDROPINDEX存儲過程

DROP PROCEDURE
    IF
    EXISTS TRYDROPINDEX;

DELIMITER $
CREATE PROCEDURE `TRYDROPINDEX`(IN tableName VARCHAR ( 2000 ), IN indexName VARCHAR ( 2000 ))
BEGIN
    DECLARE row1 INT;
    SELECT
    COUNT( * ) INTO row1
    FROM
    INFORMATION_SCHEMA.STATISTICS
    WHERE
    TABLE_SCHEMA = (SELECT DATABASE())
    AND TABLE_NAME = tableName
    AND INDEX_NAME = indexName;
    IF
    ( row1 > 0 ) THEN
    SET @exeuteSQL = CONCAT( 'ALTER table ', tableName, ' DROP INDEX ', indexName );
    PREPARE stmt FROM @exeuteSQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;
END $
DELIMITER ;

TRYCHANGETABCOLUMN存儲過程

DROP PROCEDURE IF EXISTS TRYCHANGETABCOLUMN;

delimiter //
CREATE PROCEDURE TRYCHANGETABCOLUMN (IN tableName VARCHAR(50),IN origiColName VARCHAR(50),IN targetColName VARCHAR(50),IN targetColType VARCHAR(100))
BEGIN
	DECLARE colCount INT;
  SELECT COUNT(*) INTO colCount FROM information_schema.COLUMNS WHERE TABLE_NAME = tableName AND COLUMN_NAME = targetColName;
	IF(colCount > 0) THEN
		SET @excute_sql = CONCAT('ALTER TABLE ',tableName,' MODIFY COLUMN ',targetColName,' ',targetColType,';');
	ELSE
		SET @excute_sql = CONCAT('ALTER TABLE ',tableName,' CHANGE ',origiColName,' ',targetColName,' ',targetColType,';');
	END IF;
	-- 預(yù)處理需要執(zhí)行的動態(tài)SQL，其中stmt是一個變量
	PREPARE stmt FROM @excute_sql;
	-- 執(zhí)行SQL語句
	EXECUTE stmt;
	-- 釋放掉預(yù)處理段
	deallocate prepare stmt;
END //
delimiter ;