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

Posted on 2022-06-07 by WalkonNet

表整體（TABLE ）操作

針對數據庫的表進行新增操作，考慮到腳本可重復執行，有以下兩種方案

使用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;

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

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;

但是刪除表是存在巨大風險的，如果隻是一些緩存表倒還好，如果是存放重要業務數據或者參數信息的表，刪除表會導致程序運行失敗。所以是不允許直接刪除表的，那麼如何避免數據庫存在同樣表名而結構不同呢？可以考慮在建表語句後面添加一個查詢語句，其中包含完整的字段。

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

一旦表結構不一致，就需要考慮針對表字段的增刪操作以及數據同步操作。

刪除表的操作是存在巨大風險的，刪除表之前必須準備評估是否導致業務數據丟失的問題。

表字段（COLUMN ）操作

新增表字段

通過TRYADDTABCOLUMN存儲過程保證可重復執行。

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

修改表字段

原生語句已經支持可重復執行，直接使用即可

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

刪除表字段

刪除表字段同樣是存在巨大風險的，需要仔細評估。可能會涉及的場合就是將某個字段替換為另一個字段（二者名稱不一致）。通過先刪除字段然後再添加字段會導致原來數據丟失，MySQL中可以通過ALTER TABLE CHANGE語句來修改字段。但是沒法保證可重復性。

-- 將ACT_GE_PROPERTY字段VALUE_的名字修改為VALUE2_
-- 對應的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 ）操作

添加索引

主要考慮可重復執行問題，首先通過TRYDROPINDEX刪除索引。然後再創建索引

-- 創建普通索引
CALL TRYDROPINDEX('ACT_GE_PROPERTY','IDX_REV_');
CREATE INDEX IDX_REV_ ON ACT_GE_PROPERTY (REV_ ASC)  USING BTREE;
-- 創建唯一索引
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;

刪除索引

考慮可重復執行問題，直接使用存儲過程。

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

表數據操作

新增數據

絕大多數的表新增數據都隻需考慮先根據主鍵或者關鍵字刪除，然後插入數據即可。如下所示

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;

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

第一種采用主鍵自增，隻用在表結構定義的時候將主鍵字段設置為AUTO_INCREMENT即可。

第二種模仿oracle中的序列，在mysql中通過表SEQUENCE維護這些序列信息。在插入數據的時候，需要通過存儲過程getSequenceNextVal獲取序列值（其中S_TSYS_CUSTOMER對應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表。前者在規范（http://191.168.0.126/svn/xIR_Web_J2EE/2.銀行資管/文檔/技術文檔/腳本規范/初始化TPARA數據腳本規范.html）中已經詳述，後者在規范（http://191.168.0.126/svn/xIR_Web_J2EE/2.銀行資管/文檔/技術文檔/腳本規范/MySQL序列操作規范.html）中詳述。

TPARA表新增數據

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表新增數據

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)');

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

FUNCTION 函數操作

DROP FUNCTION IF EXISTS GZB_GET_PRECISION_VALUE;
-- 在創建函數語句前修改默認分割符
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;
-- 結束時使用指定的分隔符    
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,';');
  -- 預處理需要執行的動態SQL，其中stmt是一個變量
  PREPARE stmt FROM @add_sql;
  -- 執行SQL語句
  EXECUTE stmt;
  -- 釋放掉預處理段
  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;
    -- 預處理需要執行的動態SQL，其中stmt是一個變量
    PREPARE stmt FROM @create_sql;
    -- 執行SQL語句
    EXECUTE stmt;
    -- 釋放掉預處理段
    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,';');
  -- 預處理需要執行的動態SQL，其中stmt是一個變量
  PREPARE stmt FROM @add_sql;
  -- 執行SQL語句
  EXECUTE stmt;
  -- 釋放掉預處理段
  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;
	-- 預處理需要執行的動態SQL，其中stmt是一個變量
	PREPARE stmt FROM @excute_sql;
	-- 執行SQL語句
	EXECUTE stmt;
	-- 釋放掉預處理段
	deallocate prepare stmt;
END //
delimiter ;

總結

到此這篇關於MySQL常見的腳本語句格式南的文章就介紹到這瞭,更多相關MySQL腳本語句格式內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

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

目錄

表整體（TABLE ）操作

表字段（COLUMN ）操作

表索引（INDEX ）操作

表數據操作

FUNCTION 函數操作

PROCEDURE 存儲過程操作

附錄

總結

推薦閱讀：

發佈留言取消回覆

近期文章

目錄

表整體（TABLE ）操作

表字段（COLUMN ）操作

表索引（INDEX ）操作

表數據操作

FUNCTION 函數操作

PROCEDURE 存儲過程操作

附錄

總結

推薦閱讀：

發佈留言 取消回覆

近期文章

標籤

發佈留言取消回覆