检查表中是否存在带 SQL 的列

我正在尝试编写一个查询，它将检查 MySQL 中的特定表是否有特定的列，如果没有，则创建它。否则什么都别做。这在任何企业级数据库中都是一个非常简单的过程，但 MySQL 似乎是个例外。

我以为

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='prefix_topic' AND column_name='topic_last_update')
BEGIN
ALTER TABLE `prefix_topic` ADD `topic_last_update` DATETIME NOT NULL;
UPDATE `prefix_topic` SET `topic_last_update` = `topic_date_add`;
END;

可以，但是失败了，有办法吗？

263929 次浏览
小开

从信息模式中选择 column _ name，并将此查询的结果放入变量。然后对变量进行测试，以确定表是否需要修改。

P.S. 不要忘记为 COLUMNS 表指定 TABLE _ SCHEMA。

小开

只是为了帮助那些正在寻找@Mchl 所描述的具体例子的人，试试这样的东西

 SELECT * FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'my_schema' AND TABLE_NAME = 'my_table'
AND COLUMN_NAME = 'my_column'`

如果它返回 false (零个结果) ，那么您就知道该列不存在。

小开

@ Julio

感谢 SQL 示例。我尝试了查询，我认为它需要一个小的改变，让它正常工作。

SELECT *
FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'db_name'
AND TABLE_NAME = 'table_name'
AND COLUMN_NAME = 'column_name'

这招对我管用。

谢谢！

小开
最佳答案

这对我很有用。

SHOW COLUMNS FROM `table` LIKE 'fieldname';

对于 PHP 来说，它就像是... ..。

$result = mysql_query("SHOW COLUMNS FROM `table` LIKE 'fieldname'");
$exists = (mysql_num_rows($result))?TRUE:FALSE;
小开

下面是另一种使用普通 PHP 而不使用 information _ schema 数据库的方法:

$chkcol = mysql_query("SELECT * FROM `my_table_name` LIMIT 1");
$mycol = mysql_fetch_array($chkcol);
if(!isset($mycol['my_new_column']))
mysql_query("ALTER TABLE `my_table_name` ADD `my_new_column` BOOL NOT NULL DEFAULT '0'");
小开

我将这个存储过程与上面@lain 的注释一起放在一起，如果需要多次调用它(并且不需要 php) ，那么这种方式很不错:

delimiter //
-- ------------------------------------------------------------
-- Use the inforamtion_schema to tell if a field exists.
-- Optional param dbName, defaults to current database
-- ------------------------------------------------------------
CREATE PROCEDURE fieldExists (
OUT _exists BOOLEAN,      -- return value
IN tableName CHAR(255),   -- name of table to look for
IN columnName CHAR(255),  -- name of column to look for
IN dbName CHAR(255)       -- optional specific db
) BEGIN
-- try to lookup db if none provided
SET @_dbName := IF(dbName IS NULL, database(), dbName);


IF CHAR_LENGTH(@_dbName) = 0
THEN -- no specific or current db to check against
SELECT FALSE INTO _exists;
ELSE -- we have a db to work with
SELECT IF(count(*) > 0, TRUE, FALSE) INTO _exists
FROM information_schema.COLUMNS c
WHERE
c.TABLE_SCHEMA    = @_dbName
AND c.TABLE_NAME  = tableName
AND c.COLUMN_NAME = columnName;
END IF;
END //
delimiter ;

使用 fieldExists

mysql> call fieldExists(@_exists, 'jos_vm_product', 'child_option', NULL) //
Query OK, 0 rows affected (0.01 sec)


mysql> select @_exists //
+----------+
| @_exists |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


mysql> call fieldExists(@_exists, 'jos_vm_product', 'child_options', 'etrophies') //
Query OK, 0 rows affected (0.01 sec)


mysql> select @_exists //
+----------+
| @_exists |
+----------+
|        1 |
+----------+
小开

我使用这个简单的脚本:

mysql_query("select $column from $table") or mysql_query("alter table $table add $column varchar (20)");

如果您已经连接到数据库，它就可以工作。

小开

不要将 ALTER TABLE/MODIFY COLS 或任何其他类似的表 mod 操作放在 TRANSAION 中。事务是为了能够回滚一个查询失败，而不是为了更改... ... 在事务中每次都会出错。

只需对表运行一个 SELECT * 查询并检查该列是否存在..。

小开

非常感谢 Mfoo，他提供了一个非常好的脚本，可以在表中不存在的情况下动态添加列。我用 PHP 改进了他的答案.Script 还可以帮助您查找实际需要“添加列”的表的数量 mysql 命令。尝尝食谱。非常有效。

<?php
ini_set('max_execution_time', 0);


$host = 'localhost';
$username = 'root';
$password = '';
$database = 'books';


$con = mysqli_connect($host, $username, $password);
if(!$con) { echo "Cannot connect to the database ";die();}
mysqli_select_db($con, $database);
$result=mysqli_query($con, 'show tables');
$tableArray = array();
while($tables = mysqli_fetch_row($result))
{
$tableArray[] = $tables[0];
}


$already = 0;
$new = 0;
for($rs = 0; $rs < count($tableArray); $rs++)
{
$exists = FALSE;


$result = mysqli_query($con, "SHOW COLUMNS FROM ".$tableArray[$rs]." LIKE 'tags'");
$exists = (mysqli_num_rows($result))?TRUE:FALSE;


if($exists == FALSE)
{
mysqli_query($con, "ALTER TABLE ".$tableArray[$rs]." ADD COLUMN tags VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL");
++$new;
echo '#'.$new.' Table DONE!<br/>';
}
else
{
++$already;
echo '#'.$already.' Field defined alrady!<br/>';
}
echo '<br/>';
}
?>
小开

这个工作为我与样品 PDO:

public function GetTableColumn() {
$query  = $this->db->prepare("SHOW COLUMNS FROM `what_table` LIKE 'what_column'");
try{
$query->execute();
if($query->fetchColumn()) { return 1; }else{ return 0; }
}catch(PDOException $e){die($e->getMessage());}
}

提交答案