如何检查mysql表列是否存在？

How can I check if mysql table field even exists ?

如何检查mysql表字段是否存在？

The column name is 'price' and I need to see if it exists.

列名是'price'，我需要查看它是否存在。

Haven't understood really how the 'EXISTS' works...

还没有真正理解'EXISTS'是如何工作的......

Any examples or ideas ?

任何例子或想法？

Thanks

谢谢

8 个解决方案

#1

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 here before.

此前在SO上提出了类似的问题。

#2

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

#3

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...";

#4

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.

那它就不存在了。

#5

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%'

#6

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

#7

You could get a description of all the column in your table.

您可以获得表中所有列的描述。

desc your_table;

desc your_table;

#8

I just done something like this using this function for Wordpress, this for update tables that having new chnages on it

我刚刚使用WordPress的这个函数做了这样的事情，这对于有新的chnages的更新表

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);
    }

}

#1