mysql - 如果存在于mysql中，如何删除列？

Question

这个问题在这里已经有了答案:

Using ALTER to drop a column if it exists in MySQL (8 个回答)

关闭 6 年前。

ALTER TABLE user DROP COLUMN registered_date

我们可以使用上面的命令删除一列。但是，只有当列 registered_date 存在时，我如何才能删除它？

Answer 1

IF EXISTS(SELECT * 
          FROM information_schema.COLUMNS 
          WHERE 
          TABLE_SCHEMA = 'db_name' 
          AND TABLE_NAME = 'table_name' 
          AND COLUMN_NAME = 'column_name')
BEGIN
 ALTER TABLE table_name DROP COLUMN column_name
END

或

IF (SELECT COUNT(*) 
          FROM information_schema.COLUMNS 
          WHERE 
          TABLE_SCHEMA = 'db_name' 
          AND TABLE_NAME = 'table_name' 
          AND COLUMN_NAME = 'column_name'>0)
BEGIN
 ALTER TABLE table_name DROP COLUMN column_name
END