How to Reliably Check for and Add Missing Columns in MySQL Tables?

Find and Create Missing Columns in MySQL Tables

In MySQL, it's not uncommon to need to check whether a specific column exists in a table and if not, create it. While this is a straightforward operation in many other databases, MySQL has its own nuances.

The provided query attempt:

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;

fails due to MySQL's limitations. However, there's a reliable method for checking column existence:

SHOW COLUMNS FROM `table` LIKE 'fieldname';

In PHP:

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

If the query returns any rows, the column exists; otherwise, it does not. With this information, you can then proceed to create the column as needed.

The above is the detailed content of How to Reliably Check for and Add Missing Columns in MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!