php 如何检查mysql表列是否存在？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1810223/
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
How can I check if mysql table column even exists?
提问by
How can I check if mysql table field even exists ?
如何检查 mysql 表字段是否存在？
The column name is 'price' and I need to see if it exists.
列名是“价格”，我需要查看它是否存在。
Haven't understood really how the 'EXISTS' works...
还没有真正理解“存在”是如何工作的......
Any examples or ideas ?
任何例子或想法？
Thanks
谢谢
回答by
In PHP:
在 PHP 中：
$fields = mysql_list_fields('database_name', 'table_name');
$columns = mysql_num_fields($fields);
for ($i = 0; $i < $columns; $i++) {$field_array[] = mysql_field_name($fields, $i);}
if (!in_array('price', $field_array))
{
$result = mysql_query('ALTER TABLE table_name ADD price VARCHAR(10)');
}
This should also help you:
这也应该可以帮助您：
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘TEST' AND COLUMN_NAME = ‘TEST_DATE')
BEGIN
ALTER TABLE TEST ADD TEST_DATE DATETIME
END
Or you can do:
或者你可以这样做：
Show columns from table like 'string';
There has been a similar question posed on SO herebefore.
回答by miku
Try:
尝试：
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TEST' AND COLUMN_NAME = 'Price')
BEGIN
-- do something, e.g.
-- ALTER TABLE TEST ADD PRICE DECIMAL
END
回答by wvasconcelos
Another way of doing it in PHP:
在 PHP 中执行此操作的另一种方法：
$chkcol = mysql_query("SELECT * FROM `table_name` LIMIT 1");
$mycol = mysql_fetch_array($chkcol);
if(isset($mycol['price']))
echo "Column price exists! Do something...";
回答by Paul van Jaarsveld
I found this very useful. It will list all the tables that has that column name.
我发现这非常有用。它将列出具有该列名称的所有表。
SELECT table_name,
column_name
FROM information_schema.columns
WHERE column_name LIKE '%the_column_name%'
回答by user3026711
well here is a function to check out if a particular column exists or not.
好吧，这是一个检查特定列是否存在的函数。
public function detect_column($my_db, $table, $column)
{
$db = mysql_select_db($my_db); //select the database
$sql = "SHOW COLUMNS FROM $table LIKE '$column'"; //query
$result = mysql_query($sql); //querying
if(mysql_num_rows($result) == 0) //checks the absence of column
echo "column $column doesn't exist !";
// write your code here!
else
echo "column $column exists!";
}
well if you are designing a frame work, then this function may come to your aid. This function checks for the presence of column when $flag is set to '1' and absence of column when $flag is set to '0'.
好吧，如果您正在设计框架，那么此功能可能会对您有所帮助。当 $flag 设置为 '1' 时，此函数检查列是否存在，而当 $flag 设置为 '0' 时，该函数检查列是否存在。
public function detect_column($my_db, $table, $column, $flag)
{
$this->select_db($my_db); //select the database
$sql = "SHOW COLUMNS FROM $table LIKE '$column'";
$result = mysql_query($sql);
if(mysql_num_rows($result) == $flag)
return true;
else
return false;
}
回答by Milan Babu?kov
Well, one way is to do:
嗯，一种方法是这样做：
select price from your_table limit 1
If you get an error:
如果出现错误：
#1054 - Unknown column 'price' in 'field list'
then it does not exists.
那么它不存在。
回答by Milan Babu?kov
You could get a description of all the column in your table.
您可以获得表格中所有列的描述。
desc your_table;
desc your_table;
回答by Rodrigo Manara
I just done something like this using this function for Wordpress, this for update tables that having new chnages on it
我刚刚使用此功能为 Wordpress 完成了类似的操作，这用于更新具有新更改的表
public function alterTable() {
$table_name = $this->prefix . $this->tables['name'];
$select = "select * from `{$table_name}` where 0=0 limit 1;";
$query = $this->db->get_results($select, ARRAY_A);
if (isset($query[0]) && !key_exists('adv', $query[0])) {
$sql = "ALTER TABLE `{$table_name}` ADD `me` INT NULL DEFAULT NULL ;";
$this->db->query($sql);
}
}
}
