您好,登錄后才能下訂單哦!
這篇文章主要介紹“MySQL常見的腳本語句格式有哪些”的相關(guān)知識(shí),小編通過實(shí)際案例向大家展示操作過程,操作方法簡(jiǎn)單快捷,實(shí)用性強(qiáng),希望這篇“MySQL常見的腳本語句格式有哪些”文章能幫助大家解決問題。
針對(duì)數(shù)據(jù)庫的表進(jìn)行新增操作,考慮到腳本可重復(fù)執(zhí)行,有以下兩種方案
使用TRYADDTABLE存儲(chǔ)過程
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;
使用以上兩種方式可能存在一個(gè)問題,就是數(shù)據(jù)庫中存在一個(gè)表結(jié)構(gòu)不同的表,此時(shí)以上的語句其實(shí)就是無效的,很多人都會(huì)采用先刪表然后再建表的方式,如下所示
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)險(xiǎn)的,如果只是一些緩存表倒還好,如果是存放重要業(yè)務(wù)數(shù)據(jù)或者參數(shù)信息的表,刪除表會(huì)導(dǎo)致程序運(yùn)行失敗。所以是不允許直接刪除表的,那么如何避免數(shù)據(jù)庫存在同樣表名而結(jié)構(gòu)不同呢?可以考慮在建表語句后面添加一個(gè)查詢語句,其中包含完整的字段。
SELECT NAME_,VALUE_,REV_ FROM ACT_GE_PROPERTY LIMIT 0,1;
一旦表結(jié)構(gòu)不一致,就需要考慮針對(duì)表字段的增刪操作以及數(shù)據(jù)同步操作。
刪除表的操作是存在巨大風(fēng)險(xiǎn)的,刪除表之前必須準(zhǔn)備評(píng)估是否導(dǎo)致業(yè)務(wù)數(shù)據(jù)丟失的問題。
新增表字段
通過TRYADDTABCOLUMN存儲(chǔ)過程保證可重復(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)險(xiǎn)的,需要仔細(xì)評(píng)估。可能會(huì)涉及的場(chǎng)合就是將某個(gè)字段替換為另一個(gè)字段(二者名稱不一致)。通過先刪除字段然后再添加字段會(huì)導(dǎo)致原來數(shù)據(jù)丟失,MySQL中可以通過ALTER TABLE CHANGE語句來修改字段。但是沒法保證可重復(fù)性。
-- 將ACT_GE_PROPERTY字段VALUE_的名字修改為VALUE2_ -- 對(duì)應(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');
添加索引
主要考慮可重復(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; -- 多個(gè)字段的索引 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í)行問題,直接使用存儲(chǔ)過程。
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ù)的表新增數(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)定義的時(shí)候?qū)⒅麈I字段設(shè)置為AUTO_INCREMENT即可。
第二種模仿oracle中的序列,在mysql中通過表SEQUENCE維護(hù)這些序列信息。在插入數(shù)據(jù)的時(shí)候,需要通過存儲(chǔ)過程getSequenceNextVal獲取序列值(其中S_TSYS_CUSTOMER對(duì)應(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', '浦銀自營(yíng)'); 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'', ''是否使用指令驗(yàn)證審批流程'', ''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ù)類似于初始化操作,如果這個(gè)數(shù)據(jù)不會(huì)變化,無論操作多少次都是一樣的,先刪除后插入沒有任何問題。但是這個(gè)數(shù)據(jù)可能會(huì)被修改的時(shí)候,就要考慮你的腳本會(huì)不會(huì)因?yàn)橹貜?fù)執(zhí)行導(dǎo)致業(yè)務(wù)沒法操作了。比如序列初始化的時(shí)候是1,然后在程序里使用了一段時(shí)間,再次執(zhí)行你的腳本,數(shù)據(jù)庫的序列值回去了,程序肯定會(huì)報(bào)錯(cuò)的(對(duì)應(yīng)的主鍵值已經(jīng)存在了)。
DROP FUNCTION IF EXISTS GZB_GET_PRECISION_VALUE; -- 在創(chuàng)建函數(shù)語句前修改默認(rèn)分割符 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é)束時(shí)使用指定的分隔符 END $ -- 最后修改分割符為默認(rèn)值 DELIMITER ;
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 -- @表示全局變量 相當(dāng)于php $ 拼接賦值 INTO 必須要用全局變量不然語句會(huì)報(bào)錯(cuò) SET @add_sql = CONCAT('ALTER TABLE ',tableName,' ADD COLUMN ',colName,' ',colType,';'); -- 預(yù)處理需要執(zhí)行的動(dòng)態(tài)SQL,其中stmt是一個(gè)變量 PREPARE stmt FROM @add_sql; -- 執(zhí)行SQL語句 EXECUTE stmt; -- 釋放掉預(yù)處理段 deallocate prepare stmt; END IF; END $ DELIMITER ;
TRYADDTABLE存儲(chǔ)過程
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 -- @表示全局變量 相當(dāng)于php $ 拼接賦值 INTO 必須要用全局變量不然語句會(huì)報(bào)錯(cuò) SET @create_sql = createTableSql; -- 預(yù)處理需要執(zhí)行的動(dòng)態(tài)SQL,其中stmt是一個(gè)變量 PREPARE stmt FROM @create_sql; -- 執(zhí)行SQL語句 EXECUTE stmt; -- 釋放掉預(yù)處理段 deallocate prepare stmt; END IF; END $ DELIMITER ;
TRYADDTABCOLUMN存儲(chǔ)過程
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 -- @表示全局變量 相當(dāng)于php $ 拼接賦值 INTO 必須要用全局變量不然語句會(huì)報(bào)錯(cuò) SET @add_sql = CONCAT('ALTER TABLE ',tableName,' ADD COLUMN ',colName,' ',colType,';'); -- 預(yù)處理需要執(zhí)行的動(dòng)態(tài)SQL,其中stmt是一個(gè)變量 PREPARE stmt FROM @add_sql; -- 執(zhí)行SQL語句 EXECUTE stmt; -- 釋放掉預(yù)處理段 deallocate prepare stmt; END IF; END $ DELIMITER ;
TRYDROPINDEX存儲(chǔ)過程
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存儲(chǔ)過程
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í)行的動(dòng)態(tài)SQL,其中stmt是一個(gè)變量 PREPARE stmt FROM @excute_sql; -- 執(zhí)行SQL語句 EXECUTE stmt; -- 釋放掉預(yù)處理段 deallocate prepare stmt; END // delimiter ;
關(guān)于“MySQL常見的腳本語句格式有哪些”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí),可以關(guān)注億速云行業(yè)資訊頻道,小編每天都會(huì)為大家更新不同的知識(shí)點(diǎn)。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。