MySQL：如果列不存在，则更改表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24571611/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: ALTER TABLE if column not exists
提问by BrunoRamalho
I have this code:
我有这个代码：
ALTER TABLE `settings`
ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1
And I want to alter this table only if this column doesn't exist.
而且我只想在此列不存在时更改此表。
I'm trying a lot of different ways, but nothing works:
我尝试了很多不同的方法，但没有任何效果：
ALTER TABLE `settings`
ADD COLUMN IF NOT EXISTS `multi_user` TINYINT(1) NOT NULL DEFAULT 1
With procedure:
附程序：
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 ;
I got error in END IF, then in END and then in 1
我在 END IF 中出错，然后在 END 中，然后在 1 中
How can I make this as simple as possible?
我怎样才能使这尽可能简单？
采纳答案by Harsh
Use the following in a stored procedure:
在存储过程中使用以下内容：
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;
回答by abahet
Use PREPARE/EXECUTEand querying the schema.
The host doesn't need to have permission to create or run procedures :
使用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;
回答by raugfer
Here is a solution that does not involve querying INFORMATION_SCHEMA, it simply ignores the error if the column does exist.
这是一个不涉及查询的解决方案，INFORMATION_SCHEMA如果列确实存在，它只会忽略错误。
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 `?`;
P.S. Feel free to give it other name rather than ?
PS随意给它其他名字而不是 ?
回答by Ezhil
hope this will help you
希望能帮到你
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
AND table_schema = 'db_name'
AND column_name = 'column_name'
or
或者
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;
回答by Jonathan
Add fieldif not exist:
如果不存在则添加字段：
CALL addFieldIfNotExists ('settings', 'multi_user', 'TINYINT(1) NOT NULL DEFAULT 1');
addFieldIfNotExistscode:
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;
$$
- this is not original, but it IS copy-and-paste
- source: javajon.blogspot.com/2012/10/
- 这不是原创，但它是复制和粘贴
- 来源：javajon.blogspot.com/2012/10/
回答by Adiyya Tadikamalla
I used this approach (Without using stored procedure):
我使用了这种方法（不使用存储过程）：
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_name' AND COLUMN_NAME = 'column_name'
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_name' AND COLUMN_NAME = 'column_name'
If it didnt return any rows then the column doesn't exists then alter the table:
如果它没有返回任何行，则该列不存在，然后更改表：
ALTER TABLE tbl_nameADD COLUMN column_nameTINYINT(1) NOT NULL DEFAULT 1
ALTER TABLE tbl_nameADD COLUMN column_nameTINYINT(1) NOT NULL DEFAULT 1
Hope this helps.
希望这可以帮助。
回答by ABHISHEK TRIPATHI
$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);
The abobe query return 0 if the column is not present in your table then you need to run alter query like below
如果该列不存在于您的表中，则 abobe 查询返回 0，那么您需要运行如下所示的更改查询
if($RESULT){
$sqll="ALTER TABLE Your_table_Name ADD COLUMN Your_New_Column_Name varchar(20) NOT NULL DEFAULT 0";
}
回答by Mukund
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;
