添加列到 mysql 表，如果它不存在

小开

最佳答案

注意，在5.0之前，MySQL 不支持 INFORMATION_SCHEMA。在5.0之前也不支持存储过程，所以如果需要支持 MySQL 4.1，这个解决方案就不太好。

One solution used by frameworks that use 数据库迁移 is to record in your database a revision number for the schema. Just a table with a single column and single row, with an integer indicating which revision is current in effect. When you update the schema, increment the number.

Another solution would be to just 试试看 the ALTER TABLE ADD COLUMN command. It should throw an error if the column already exists.

ERROR 1060 (42S21): Duplicate column name 'newcolumnname'

捕获错误并在升级脚本中忽略它。

小开

Just tried the stored procedure script. Seems the problem is the ' marks around the delimiters. The MySQL 文档 show that delimiter characters do not need the single quotes.

所以你想要:

delimiter //

而不是:

delimiter '//'

对我有用:)

小开

下面是一个可行的解决方案(刚刚在 Solaris 上的 MySQL 5.0中试用过) :

DELIMITER $$


DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$
CREATE PROCEDURE upgrade_database_1_0_to_2_0()
BEGIN


-- rename a table safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
AND TABLE_NAME='my_old_table_name') ) THEN
RENAME TABLE
my_old_table_name TO my_new_table_name,
END IF;


-- add a column safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
AND COLUMN_NAME='my_additional_column' AND TABLE_NAME='my_table_name') ) THEN
ALTER TABLE my_table_name ADD my_additional_column varchar(2048) NOT NULL DEFAULT '';
END IF;


END $$


CALL upgrade_database_1_0_to_2_0() $$


DELIMITER ;

乍一看，它可能看起来比实际情况更复杂，但我们必须处理以下问题:

IF语句只能在存储过程中工作，不能直接运行, 例如，在 mysql 客户端
更优雅和简洁的 SHOW COLUMNS在存储过程中不起作用，因此必须使用 INformATION _ SCHEMA
分隔语句的语法在 MySQL 中很奇怪，所以你必须重新定义分隔符以便能够创建存储过程忘记把分隔符切换回来！
InformATION _ SCHEMA 对于所有数据库都是全局的，不要忘记过滤器。 DATABASE()返回当前选定的数据库。

小开

另一种方法是忽略 declare continue handler的错误:

delimiter ;;
create procedure foo ()
begin
declare continue handler for 1060 begin end;
alter table atable add subscriber_surname varchar(64);
end;;
call foo();;

我认为这种方式比使用 exists子查询更简单。特别是如果您有很多列要添加，并且需要多次运行脚本。

更多关于继续处理程序的信息可以在 http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html找到

小开

如果在脚本中运行这个过程，那么需要在后面添加以下代码行，以使其可重新运行，否则就会得到一个过程已经存在的错误。

drop procedure foo;

小开

在 PHP > PDO 中添加列的最佳方法:

$Add = $dbh->prepare("ALTER TABLE `YourCurrentTable` ADD `YourNewColumnName` INT NOT NULL");
$Add->execute();

注意: 表中的列是不可重复的，这意味着我们不需要检查列的存在，但是为了解决这个问题，我们检查上面的代码:

例如，如果它的工作警报1，如果不是0，这意味着列存在! :)

小开

检查 PDO 中是否存在列(100%)

{
if(isset($_POST['Add']))
{
$ColumnExist = $dbh->prepare("SELECT * FROM ColumnChecker where column_name='$insert_column_name' LIMIT 1");
$ColumnExist ->execute();
$ColumnName = $ColumnExist->fetch(2);
$Display_Column_Name = $ColumnName['column_name'];


if($Display_Column_Name == $insert_column_name)
{
echo "$Display_Column_Name already exist";
} //*****************************
else
{
$InsertColumn = $dbh->prepare("insert into ColumnChecker ( column_name ) values ('$insert_column_name')");
$InsertColumn->execute();


if($InsertColumn)
{
$Add = $dbh->prepare("ALTER TABLE `$Table` ADD `$insert_column_name` $insert_column_type($insert_column_Length) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ");
$Add->execute();


if($Add)
{
echo 'Table has been updated';
}
else
{
echo 'Sorry! Try again...';
}
}
}
}
}#Add Column into Table :)

小开

我使用的是 MySQL 5.5.19。

我喜欢拥有可以在没有错误的情况下运行和重新运行的脚本，特别是在警告似乎仍然存在的情况下，当我运行没有错误/警告的脚本时，这些脚本会再次出现。至于添加字段，我为自己编写了一个过程，以减少输入量:

-- add fields to template table to support ignoring extra data
-- at the top/bottom of every page
CALL addFieldIfNotExists ('template', 'firstPageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageFooterBeginY', 'INT NOT NULL DEFAULT 792');

The code to create the AddFieldIfNotExists procedure is as follows:

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


-- http://javajon.blogspot.com/2012/10/mysql-alter-table-add-column-if-not.html


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;
$$

我没有编写一个过程来安全地修改一个列，但是我认为可以很容易地修改上面的过程来做到这一点。

小开

大多数的答案都是关于如何在存储过程中安全地添加一个列，我需要在不使用存储过程的情况下安全地向表中添加一个列，并且发现 MySQL 不允许在 SP之外使用 IF Exists()。我会发表我的解决方案，它可能会帮助在同样的情况下的人。

SELECT count(*)
INTO @exist
FROM information_schema.columns
WHERE table_schema = database()
and COLUMN_NAME = 'original_data'
AND table_name = 'mytable';


set @query = IF(@exist <= 0, 'alter table intent add column mycolumn4 varchar(2048) NULL after mycolumn3',
'select \'Column Exists\' status');


prepare stmt from @query;


EXECUTE stmt;

小开

我已经采用了 OP 的 sproc 并使其可重用和模式独立，显然它仍然需要 MySQL5。

DROP PROCEDURE IF EXISTS AddCol;


DELIMITER //


CREATE PROCEDURE AddCol(
IN param_schema VARCHAR(100),
IN param_table_name VARCHAR(100),
IN param_column VARCHAR(100),
IN param_column_details VARCHAR(100)
)
BEGIN
IF NOT EXISTS(
SELECT NULL FROM information_schema.COLUMNS
WHERE COLUMN_NAME=param_column AND TABLE_NAME=param_table_name AND table_schema = param_schema
)
THEN
set @paramTable = param_table_name ;
set @ParamColumn = param_column ;
set @ParamSchema = param_schema;
set @ParamColumnDetails = param_column_details;
/* Create the full statement to execute */
set @StatementToExecute = concat('ALTER TABLE `',@ParamSchema,'`.`',@paramTable,'` ADD COLUMN `',@ParamColumn,'` ',@ParamColumnDetails);
/* Prepare and execute the statement that was built */
prepare DynamicStatement from @StatementToExecute ;
execute DynamicStatement ;
/* Cleanup the prepared statement */
deallocate prepare DynamicStatement ;


END IF;
END //


DELIMITER ;

小开

$smpt = $pdo->prepare("SHOW fields FROM __TABLE__NAME__");
$smpt->execute();
$res = $smpt->fetchAll(PDO::FETCH_ASSOC);
//print_r($res);

然后按循环在 $res 中查找列的键像这样:

    if($field['Field'] == '_my_col_'){
return true;
}
+


**Below code is good for checking column existing in the WordPress tables:**
public static function is_table_col_exists($table, $col)
{
global $wpdb;
$fields = $wpdb->get_results("SHOW fields FROM {$table}", ARRAY_A);
foreach ($fields as $field)
{
if ($field['Field'] == $col)
{
return TRUE;
}
}


return FALSE;
}

小开

ALTER TABLE `subscriber_surname` ADD  IF NOT EXISTS  `#__comm_subscribers`.`subscriber_surname`;


ALTER TABLE `#__comm_subscribers` MODIFY `subscriber_surname` varchar(64) NOT NULL default '';

小开

如果你在 MariaDB 上，不需要使用存储过程，只需要使用，例如:

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name tinyint(1) DEFAULT 0;

看这里

小开

来自 Jake https://stackoverflow.com/a/6476091/6751901的过程对于添加新的列来说是非常简单和好的解决方案，但是还有一行:

DROP PROCEDURE IF EXISTS foo;;

你可以稍后在那里添加新的栏目，下次也可以这样做:

delimiter ;;
DROP PROCEDURE IF EXISTS foo;;
create procedure foo ()
begin
declare continue handler for 1060 begin end;
alter table atable add subscriber_surname varchar(64);
alter table atable add subscriber_address varchar(254);
end;;
call foo();;

小开

Below are the Stored procedure in MySQL To Add Column(s) in different Table(s) in different Database(s) if column does not exists in a Database(s) Table(s) with following advantages

可以同时添加多个列，用于更改不同数据库中的多个表
运行三个 mysql 命令，即 DROP、 CREATE、 CALL For Process
DATABASE 名称应根据 USE 进行更改，否则多个数据可能出现问题

DROP PROCEDURE  IF EXISTS `AlterTables`;
DELIMITER $$
CREATE PROCEDURE `AlterTables`()
BEGIN
DECLARE table1_column1_count INT;
DECLARE table2_column2_count INT;
SET table1_column1_count = (  SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_SCHEMA = 'DATABASE_NAME' AND
TABLE_NAME = 'TABLE_NAME1' AND
COLUMN_NAME = 'TABLE_NAME1_COLUMN1');
SET table2_column2_count = (  SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_SCHEMA = 'DATABASE_NAME' AND
TABLE_NAME = 'TABLE_NAME2' AND
COLUMN_NAME = 'TABLE_NAME2_COLUMN2');
IF table1_column1_count = 0 THEN
ALTER TABLE `TABLE_NAME1`ADD `TABLE_NAME1_COLUMN1` text COLLATE 'latin1_swedish_ci' NULL AFTER `TABLE_NAME1_COLUMN3`,COMMENT='COMMENT HERE';
END IF;
IF table2_column2_count = 0 THEN
ALTER TABLE `TABLE_NAME2` ADD `TABLE_NAME2_COLUMN2` VARCHAR( 100 ) NULL DEFAULT NULL COMMENT 'COMMENT HERE';
END IF;
END $$
DELIMITER ;
call AlterTables();

小开

下面是我的 PHP/PDO 解决方案:

function addColumnIfNotExists($db, $table, $col, $type, $null = true) {
global $pdo;
if($res = $pdo->query("SHOW COLUMNS FROM `$db`.`$table` WHERE `Field`='$col'")) {
if(!$res = $res->fetchAll(PDO::FETCH_ASSOC)) {
$null = ($null ? 'NULL' : 'NOT NULL');
$pdo->query("ALTER TABLE `$db`.`$table` ADD `$col` $type $null");
}
}
}

注意，它将自动在表的末尾添加列。我还没有实现查询的 DEFAULT部分。