MySQL添加列(如果不存在) [英] MySQL add column if not exist
本文介绍了MySQL添加列(如果不存在)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'email_subscription' AND COLUMN_NAME = 'subscribe_all')
THEN
ALTER TABLE email_subscription
ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
ADD COLUMN subscribe_category varchar(512) DEFAULT NULL;
我看了很多例子.但是此查询不起作用,出现以下错误:
I had a look at huge amount of examples. but this query doesn't work, I got error of:
错误1064(42000):您的SQL语法有错误;检查 与您的MySQL服务器版本相对应的手册 在'IF NOT EXISTS(SELECT * FROM 第1行的INFORMATION_SCHEMA.COLUMNS TABLE_NAME ='
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =' at line 1
推荐答案
您可以为查询创建一个过程,
you can create a procedure for the query,
DELIMITER $$
CREATE PROCEDURE Alter_Table()
BEGIN
DECLARE _count INT;
SET _count = ( SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'email_subscription' AND
COLUMN_NAME = 'subscribe_all');
IF _count = 0 THEN
ALTER TABLE email_subscription
ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
ADD COLUMN subscribe_category varchar(512) DEFAULT NULL;
END IF;
END $$
DELIMITER ;
这篇关于MySQL添加列(如果不存在)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文