MySQL，使用SQL检查表中是否存在列 [英] MySQL, Check if a column exists in a table with SQL

问题描述

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

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.

我想过

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?

推荐答案

这对我来说很好.

SHOW COLUMNS FROM `table` LIKE 'fieldname';

使用PHP就像...

With PHP it would be something like...

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

这篇关于MySQL，使用SQL检查表中是否存在列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！