mysql ddl可以改_ddl-MySQL：如果列不存在，则更改表

ddl-MySQL：如果列不存在，则更改表

我有以下代码：

ALTER TABLE `settings`

ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1

而且我只想在此列不存在时更改此表。

我正在尝试许多不同的方法，但是没有任何效果：

ALTER TABLE `settings`

ADD COLUMN IF NOT EXISTS `multi_user` TINYINT(1) NOT NULL DEFAULT 1

附带程序：

DELIMITER $$

CREATE PROCEDURE Alter_Table()

BEGIN

DECLARE _count INT;

SET _count = ( SELECT COUNT(*)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'settings' AND

COLUMN_NAME = 'multi_user');

IF _count = 0 THEN

ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1

END IF;

END $$

DELIMITER ;

我在END IF中出错，然后在END中又在1中出错

我怎样才能使它尽可能简单？

8个解决方案

39 votes

在存储过程中使用以下命令：

IF NOT EXISTS( SELECT NULL

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'tablename'

AND table_schema = 'db_name'

AND column_name = 'columnname') THEN

ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;

Harsh answered 2020-07-31T16:21:58Z

29 votes

使用PREPARE/EXECUTE并查询架构。主机不需要创建或运行过程的权限：

SET @dbname = DATABASE();

SET @tablename = "tableName";

SET @columnname = "colName";

SET @preparedStatement = (SELECT IF(

(

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS

WHERE

(table_name = @tablename)

AND (table_schema = @dbname)

AND (column_name = @columnname)

) > 0,

"SELECT 1",

CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT(11);")

));

PREPARE alterIfNotExists FROM @preparedStatement;

EXECUTE alterIfNotExists;

DEALLOCATE PREPARE alterIfNotExists;

abahet answered 2020-07-31T16:22:18Z

28 votes

这是一种不涉及查询?的解决方案，如果该列确实存在，它只会忽略该错误。

DROP PROCEDURE IF EXISTS `?`;

DELIMITER //

CREATE PROCEDURE `?`()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;

ALTER TABLE `table_name` ADD COLUMN `column_name` INTEGER;

END //

DELIMITER ;

CALL `?`();

DROP PROCEDURE `?`;

附言 随意给它起另一个名字，而不是?

raugfer answered 2020-07-31T16:22:45Z

5 votes

希望这个能对您有所帮助

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'tbl_name'

AND table_schema = 'db_name'

AND column_name = 'column_name'

要么

delimiter '//'

CREATE PROCEDURE addcol() BEGIN

IF NOT EXISTS(

SELECT * FROM information_schema.COLUMNS

WHERE COLUMN_NAME='new_column' AND TABLE_NAME='tablename' AND TABLE_SCHEMA='the_schema'

)

THEN

ALTER TABLE `the_schema`.`the_table`

ADD COLUMN `new_column` TINYINT(1) NOT NULL DEFAULT 1;;

END IF;

END;

//

delimiter ';'

CALL addcol();

DROP PROCEDURE addcol;

Ezhil answered 2020-07-31T16:23:10Z

4 votes

添加字段(如果不存在)：

CALL addFieldIfNotExists ('settings', 'multi_user', 'TINYINT(1) NOT NULL DEFAULT 1');

addFieldIfNotExists代码：

DELIMITER $$

DROP PROCEDURE IF EXISTS addFieldIfNotExists

$$

DROP FUNCTION IF EXISTS isFieldExisting

$$

CREATE FUNCTION isFieldExisting (table_name_IN VARCHAR(100), field_name_IN VARCHAR(100))

RETURNS INT

RETURN (

SELECT COUNT(COLUMN_NAME)

FROM INFORMATION_SCHEMA.columns

WHERE TABLE_SCHEMA = DATABASE()

AND TABLE_NAME = table_name_IN

AND COLUMN_NAME = field_name_IN

)

$$

CREATE PROCEDURE addFieldIfNotExists (

IN table_name_IN VARCHAR(100)

, IN field_name_IN VARCHAR(100)

, IN field_definition_IN VARCHAR(100)

)

BEGIN

SET @isFieldThere = isFieldExisting(table_name_IN, field_name_IN);

IF (@isFieldThere = 0) THEN

SET @ddl = CONCAT('ALTER TABLE ', table_name_IN);

SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ;

SET @ddl = CONCAT(@ddl, ' ', field_name_IN);

SET @ddl = CONCAT(@ddl, ' ', field_definition_IN);

PREPARE stmt FROM @ddl;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END IF;

END;

$$

这不是原始的，而是复制粘贴

来源：javajon.blogspot.com/2012/10/

Jonathan answered 2020-07-31T16:23:43Z

2 votes

我使用了这种方法(不使用存储过程)：

ALTER TABLE tbl_name

ADD COLUMN column_name TINYINT(1) NOT NULL DEFAULT 1

如果未返回任何行，则该列不存在，然后更改表：

tbl_name加栏column_nameTINYINT(1)非空默认值1

希望这可以帮助。

Adiyya Tadikamalla answered 2020-07-31T16:24:29Z

1 votes

SET @dbname = DATABASE();

SET @tablename = "table";

SET @columnname = "fieldname";

SET @preparedStatement = (SELECT IF(

(

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS

WHERE

(table_name = @tablename)

AND (table_schema = @dbname)

AND (column_name = @columnname)

) > 0,

"SELECT 1",

CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " DECIMAL(18,4) NULL;")

));

PREPARE alterIfNotExists FROM @preparedStatement;

EXECUTE alterIfNotExists;

DEALLOCATE PREPARE alterIfNotExists;

Mukund answered 2020-07-31T16:24:45Z

0 votes

$sql="SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Your_Table_Name' AND COLUMN_NAME = 'Your_New_Column_Name'";

$RESULT = mysqli_query($conn,$sql);

如果表中不存在该列，则上面的查询返回0，那么您需要运行如下所示的alter query

if($RESULT){

$sqll="ALTER TABLE Your_table_Name ADD COLUMN Your_New_Column_Name varchar(20) NOT NULL DEFAULT 0";

}

ABHISHEK TRIPATHI answered 2020-07-31T16:25:05Z