mysql 动态更新数据库_动态更新数据库脚本——Mysql

秒速五厘米 2023-01-12 07:59 233阅读 0赞

具体的upgrade脚本如下：

动态删除索引

DROP PROCEDURE IF EXISTS UPGRADE;

DELIMITER $$

CREATE PROCEDURE UPGRADE()

BEGIN

\-- RESOURCE.AUDIO\_ATTRIBUTE

IF EXISTS(SELECT \* FROM INFORMATION\_SCHEMA.STATISTICS WHERE TABLE\_SCHEMA = 'RESOURCE' AND TABLE\_NAME = 'AUDIO\_ATTRIBUTE' AND INDEX\_NAME = 'resource\_publish\_resource\_id\_index')

THEN

ALTER TABLE AUDIO\_ATTRIBUTE DROP INDEX resource\_publish\_resource\_id\_index;

END IF;

END$$

DELIMITER ;

CALL UPGRADE();

DROP PROCEDURE IF EXISTS UPGRADE;

动态添加字段

DROP PROCEDURE IF EXISTS UPGRADE;

DELIMITER $$

CREATE PROCEDURE UPGRADE()

BEGIN

\-- HOMEWORK.HOMEWORK\_QUESTION\_GROUP.FROM\_ID

IF NOT EXISTS(SELECT \* FROM INFORMATION\_SCHEMA.COLUMNS WHERE TABLE\_SCHEMA = 'HOMEWORK' AND TABLE\_NAME = 'HOMEWORK\_QUESTION\_GROUP' AND COLUMN\_NAME = 'FROM\_ID')

THEN

ALTER TABLE HOMEWORK\_QUESTION\_GROUP ADD COLUMN FROM\_ID VARCHAR(50) NULL;

END IF;

\-- HOMEWORK.HOMEWORK\_QUESTION\_GROUP.QUESTION\_TYPE

IF NOT EXISTS(SELECT \* FROM INFORMATION\_SCHEMA.COLUMNS WHERE TABLE\_SCHEMA = 'HOMEWORK' AND TABLE\_NAME = 'HOMEWORK\_QUESTION\_GROUP' AND COLUMN\_NAME = 'QUESTION\_TYPE')

THEN

ALTER TABLE HOMEWORK\_QUESTION\_GROUP ADD COLUMN QUESTION\_TYPE VARCHAR(50) NULL;

END IF;

\-- HOMEWORK.HOMEWORK\_QUESTION\_GROUP.DIFFICULTY

IF NOT EXISTS(SELECT \* FROM INFORMATION\_SCHEMA.COLUMNS WHERE TABLE\_SCHEMA = 'HOMEWORK' AND TABLE\_NAME = 'HOMEWORK\_QUESTION\_GROUP' AND COLUMN\_NAME = 'DIFFICULTY')

THEN

ALTER TABLE HOMEWORK\_QUESTION\_GROUP ADD COLUMN DIFFICULTY VARCHAR(50) NULL;

END IF;

END$$

DELIMITER ;

CALL UPGRADE();

DROP PROCEDURE IF EXISTS UPGRADE;

其他语法类似，主要区分EXISTS和 NOT EXISTS的用法。