英文:
Getting syntax error on SQL but i know the syntax should be right
问题
[Toad 语法错误](https://i.stack.imgur.com/CX8PF.png)
ALTER TABLE db_group 添加列 IF NOT EXISTS `isPublic` tinyint(4) NOT NULL 默认 0;
ALTER TABLE db_group_point 修改列 group_code varchar(50) NOT NULL 默认 0 字符集 utf8_bin;
-- ALTER TABLE db_group_point 删除主键;
-- ALTER TABLE db_group_point 添加列 ID int(11) 主键 NOT NULL 自增;
-- ALTER TABLE db_group_point 添加约束 db_group_point_Contraint1 唯一 (group_code, TagName, TagOrder);
删除存储过程 IF EXISTS UpgradeInsight3_1;
创建存储过程 UpgradeInsight3_1 ()
BEGIN
如果不存在 (选择 COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = 'db_group_point')
AND (table_schema = 'prism')
AND (column_name = 'ID'))
THEN
ALTER TABLE db_group_point 删除主键;
ALTER TABLE db_group_point 添加列 IF NOT EXISTS ID int(11) 主键 NOT NULL 自增;
END IF;
如果不存在 (选择 * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'db_group_point_Contraint1')
THEN
ALTER TABLE db_group_point 添加约束 db_group_point_Contraint1 唯一 (group_code, TagName, TagOrder);
END IF;
END;
调用 UpgradeInsight3_1();
删除存储过程 IF EXISTS UpgradeInsight3_1;
ALTER TABLE db_group_point 添加列 IF NOT EXISTS TagAlias varchar(255) 默认 NULL 字符集 utf8_bin;
插入忽略 INTO sys_user_role(ROLE_ID,USER_ID) 值 (1, 1);
ALTER TABLE sys_user
修改列 ADDRESS varchar(200) 默认 NULL 注释 '用户地址',
修改列 EMAIL varchar(200) 默认 NULL 注释 '用户电子邮件',
修改列 POSITION varchar(200) 默认 NULL 注释 '用户在工厂中的职位',
修改列 DESCRIPTION varchar(300) 默认 NULL 注释 '有关用户的更多描述';
删除存储过程 IF EXISTS prism.sp_mimic_stringtag_latest_values;
DELIMITER //
创建存储过程 prism.`sp_mimic_stringtag_latest_values`(
IN tagname1 varchar(255)
)
英文:
ALTER TABLE db_group ADD COLUMN IF NOT EXISTS `isPublic` tinyint(4) not null default 0;
ALTER TABLE db_group_point MODIFY COLUMN group_code varchar(50) NOT NULL DEFAULT 0 COLLATE utf8_bin;
-- ALTER TABLE db_group_point DROP PRIMARY KEY;
-- ALTER TABLE db_group_point ADD COLUMN ID int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT;
-- ALTER TABLE db_group_point ADD CONSTRAINT db_group_point_Contraint1 UNIQUE (group_code, TagName, TagOrder);
DROP PROCEDURE IF EXISTS UpgradeInsight3_1;
CREATE PROCEDURE UpgradeInsight3_1 ()
BEGIN
IF NOT EXISTS (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = 'db_group_point')
AND (table_schema = 'prism')
AND (column_name = 'ID'))
THEN
ALTER TABLE db_group_point DROP PRIMARY KEY;
ALTER TABLE db_group_point ADD COLUMN IF NOT EXISTS ID int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT;
END IF;
IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'db_group_point_Contraint1')
THEN
ALTER TABLE db_group_point ADD CONSTRAINT db_group_point_Contraint1 UNIQUE (group_code, TagName, TagOrder);
END IF;
END;
CALL UpgradeInsight3_1();
DROP PROCEDURE IF EXISTS UpgradeInsight3_1;
ALTER TABLE db_group_point ADD COLUMN IF NOT EXISTS TagAlias varchar(255) DEFAULT NULL COLLATE utf8_bin;
INSERT IGNORE INTO sys_user_role(ROLE_ID,USER_ID) VALUES (1, 1);
ALTER TABLE sys_user
MODIFY COLUMN ADDRESS varchar(200) DEFAULT NULL COMMENT 'Address of user',
MODIFY COLUMN EMAIL varchar(200) DEFAULT NULL COMMENT 'Email of user',
MODIFY COLUMN POSITION varchar(200) DEFAULT NULL COMMENT 'Position of user in plant',
MODIFY COLUMN DESCRIPTION varchar(300) DEFAULT NULL COMMENT 'Description more about user';
DROP PROCEDURE IF EXISTS prism.sp_mimic_stringtag_latest_values;
DELIMITER //
CREATE PROCEDURE prism.`sp_mimic_stringtag_latest_values`(
IN tagname1 varchar(255)
)
I tried removing the default value and putting something else.
答案1
得分: 1
这是因为“ADD COLUMN IF NOT EXISTS”子句似乎不存在，据我所知。
英文:
That's because
ADD COLUMN IF NOT EXISTS
clause doesn't exist, as far as I can tell.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论