我正在尝试ALTER
一个新列IF NOT EXITS
。我修改了这个问题的第三个答案的代码:
DELIMITER $$
DROP PROCEDURE IF EXISTS myUpgrade $$
CREATE PROCEDURE myUpgrade()
BEGIN
IF NOT EXISTS
((SELECT * FROM information_schema.COLUMNS WHERE `TABLE_SCHEMA` = DATABASE() AND`COLUMN_NAME` = 'myNewColumn' `TABLE_NAME` = 'my_table'))
THEN
ALTER TABLE `my_table` ADD `myNewColumn` VARCHAR( 255 ) NOT NULL AFTER `id`;
ENDIF;
END $$
CALL myUpgrade() $$
DELIMITER;
错误:
#1064 - 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 '`TABLE_NAME` = 'my_table'))
THEN
ALTER TABLE `my_table` ADD `myNewColumn` VA' at line 5
也许有用:
- 我在PHPMyAdmin(4.0.10deb1)中运行代码
- 服务器版本:5.5.38-ubuntu10.14.04.1
- 协议版本:10
- Apache/2.4.7
- 数据库客户端版本:libmysql-5.5.38
- PHP扩展:mysqli
提前感谢!
WHERE
子句中TABLE_NAME
条件之前缺少一个AND
:
IF NOT EXISTS
((SELECT * FROM information_schema.COLUMNS
WHERE `TABLE_SCHEMA` = DATABASE()
AND `COLUMN_NAME` = 'myNewColumn'
AND `TABLE_NAME` = 'my_table'))
此外,END IF
中需要有一个空间。
我想你是missinhg AND'myNewColumn'和TABLE_NAME
这个特定的部分应该是这样的:
(从information_schema.COLUMNS中选择*,其中TABLE_SCHEMA
=数据库()AND COLUMN_NAME
='myNewColumn'ANDTABLE_NAME
='my_table')