mysql先判断是否存在某列_mysql判断列是否存在

ゝ一纸荒年。 2022-10-31 12:23 431阅读 0赞

本篇文章将通过存储过程来判断列(字段)是否存在。请看详情。

推荐课程：MySQL教程。

![13450f44d7aa5186ddd8889197a8dc1b.png][]

判断字段是否存在：DROP PROCEDURE IF EXISTS schema\_change; DELIMITER //CREATE PROCEDURE schema\_change() BEGIN DECLARE CurrentDatabase VARCHAR(100);SELECT DATABASE() INTO CurrentDatabase;IF NOT EXISTS (SELECT \* FROM information\_schema.columns WHERE table\_schema=CurrentDatabase AND table\_name = 'rtc\_order' AND column\_name = 'IfUpSend') THEN ALTER TABLE rtc\_order ADD COLUMN \`IfUpSend\` BIT NOT NULL DEFAULT 0 COMMENT '是否上传 是否上传';END IF; END// DELIMITER ; CALL schema\_change();

mysql 判断字段否存在，如果存在就修改字段：DROP PROCEDURE IF EXISTS proc\_tempPro;if(@count>0) THEN alter table 表名 change column \`旧列名\` \`新列名\` varchar(30) comment '字段说明';end if;end;call proc\_tempPro;DROP PROCEDURE IF EXISTS proc\_tempPro;

通过存储过程判断字段是否存在，不存在则增加：DROP PROCEDURE IF EXISTS pro\_AddColumn;CREATE PROCEDURE pro\_AddColumn() BEGINIF NOT EXISTS(SELECT 1 FROM information\_schema.columns WHERE table\_name='component' AND COLUMN\_NAME='PRINT\_CHECK\_STATUS') THENALTER TABLE component ADD PRINT\_CHECK\_STATUS int(10) default 0;END IF;IF NOT EXISTS(SELECT 1 FROM information\_schema.columns WHERE table\_name='component' AND COLUMN\_NAME='PRINT\_CHECK\_TIME') THENALTER TABLE component ADD PRINT\_CHECK\_TIME datetime NULL;END IF;IF NOT EXISTS(SELECT 1 FROM information\_schema.columns WHERE table\_schema=podcloud AND table\_name='component' AND COLUMN\_NAME='PRINT\_CHECK\_BACK\_REASON') THENALTER TABLE component ADD PRINT\_CHECK\_BACK\_REASON varchar(500) default null;END IF;END;CALL pro\_AddColumn;DROP PROCEDURE pro\_AddColumn;------------------------------------------------------------------------------------------------DROP PROCEDURE IF EXISTS pro\_AddIndex; DELIMITER; CREATE PROCEDURE pro\_AddIndex() BEGIN IF NOT EXISTS (SELECT \* FROM information\_schema.statistics WHERE table\_schema=CurrentDatabase AND table\_name = 'rtc\_phototype' AND index\_name = 'index\_name') THEN ALTER TABLE \`rtc\_Phototype\` ADD INDEX index\_name ( \`imgtype\` ); END IF; END; DELIMITER; CALL pro\_AddIndex(); Drop procedure pro\_AddIndex;

[13450f44d7aa5186ddd8889197a8dc1b.png]: /images/20221024/d62007b5e08a45efb3c2d7655e11fb3c.png