MySQL，使用SQL检查表中是否存在列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3395798/
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, Check if a column exists in a table with SQL
提问by clops
I am trying to write a query that will check if a specific table in MySQL has a specific column, and if not —?create it. Otherwise do nothing. This is really an easy procedure in any enterprise-class database, yet MySQL seems to be an exception.
我正在尝试编写一个查询，该查询将检查 MySQL 中的特定表是否具有特定列，如果没有，则创建它。否则什么都不做。在任何企业级数据库中，这确实是一个简单的过程，但 MySQL 似乎是一个例外。
I thought something like
我想像
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;
would work, but it fails badly. Is there a way?
会工作，但它失败了。有办法吗？
回答by Mfoo
This works well for me.
这对我很有效。
SHOW COLUMNS FROM `table` LIKE 'fieldname';
With PHP it would be something like...
使用 PHP 它会是这样的......
$result = mysql_query("SHOW COLUMNS FROM `table` LIKE 'fieldname'");
$exists = (mysql_num_rows($result))?TRUE:FALSE;
回答by Iain
@julio
@julio
Thanks for the SQL example. I tried the query and I think it needs a small alteration to get it working properly.
感谢您提供 SQL 示例。我尝试了查询，我认为它需要进行一些小改动才能使其正常工作。
SELECT *
FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'db_name'
AND TABLE_NAME = 'table_name'
AND COLUMN_NAME = 'column_name'
That worked for me.
那对我有用。
Thanks!
谢谢！
回答by julio
Just to help anyone who is looking for a concrete example of what @Mchl was describing, try something like
只是为了帮助任何正在寻找@Mchl 所描述内容的具体示例的人，请尝试类似
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'my_schema' AND TABLE_NAME = 'my_table' AND COLUMN_NAME = 'my_column'
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'my_schema' AND TABLE_NAME = 'my_table' AND COLUMN_NAME = 'my_column'
If it returns false (zero results) then you know the column doesn't exist.
如果它返回 false（零结果），那么您就知道该列不存在。
回答by wvasconcelos
Following is another way of doing it using plain PHP without the information_schema database:
以下是使用没有 information_schema 数据库的普通 PHP 执行此操作的另一种方法：
$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'");
回答by quickshiftin
I threw this stored procedure together with a start from @lain's comments above, kind of nice if you need to call it more than a few times (and not needing php):
我把这个存储过程和上面@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 ;
Working with fieldExists
与 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 |
+----------+
回答by Mchl
Select just column_name from information schema and put the result of this query into variable. Then test the variable to decide if table needs alteration or not.
从信息模式中仅选择 column_name 并将此查询的结果放入变量中。然后测试变量以确定表是否需要更改。
P.S. Don't foget to specify TABLE_SCHEMA for COLUMNS table as well.
PS 不要忘记为 COLUMNS 表指定 TABLE_SCHEMA。
回答by vio
I am using this simple script:
我正在使用这个简单的脚本：
mysql_query("select $column from $table") or mysql_query("alter table $table add $column varchar (20)");
It works if you are already connected to the database.
如果您已经连接到数据库，它就可以工作。
回答by webblover
Many thanks to Mfoo who has put the really nice script for adding columns dynamically if not exists in the table. I have improved his answer with PHP. The script additionally helps you find how many tables actually needed 'Add column'mysql comand. Just taste the recipe. Works like charm.
非常感谢 Mfoo，他编写了非常好的脚本，用于动态添加列（如果表中不存在）。我用 PHP 改进了他的回答。该脚本还可以帮助您找到实际需要“添加列”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/>';
}
?>
回答by user3706926
This work for me with sample PDO :
这对我来说适用于示例 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());}
}
回答by gmize
DO NOT put ALTER TABLE/MODIFY COLS or any other such table mod operations inside a TRANSACTION. Transactions are for being able to roll back a QUERY failure not for ALTERations...it will error out every time in a transaction.
不要将 ALTER TABLE/MODIFY COLS 或任何其他此类 table mod 操作放在 TRANSACTION 中。事务是为了能够回滚 QUERY 失败而不是为了 ALTERations……它每次在事务中都会出错。
Just run a SELECT * query on the table and check if the column is there...
只需在表上运行 SELECT * 查询并检查该列是否存在...
