SQL语法错误

发布于 2024-10-05 11:07:00 字数 523 浏览 1 评论 0原文

我正在尝试执行此查询：

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns 
WHERE table_name = 'MyTableName' AND column_name = 'ColumnInQuestion')
THEN ALTER TABLE MyTableName DROP COLUMN ColumnInQuestion;

并且收到以下错误消息：

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本对应的手册，了解在第 1 行 'IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'articles'' at line 1
附近使用的正确语法

我正在使用 MySQL 5.1.something，所以我不'不认为 information_schema 没有被定义有什么想法吗？

谢谢您的帮助！

原文

I'm trying to execute this query:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns 
WHERE table_name = 'MyTableName' AND column_name = 'ColumnInQuestion')
THEN ALTER TABLE MyTableName DROP COLUMN ColumnInQuestion;

And I get the following error message:

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 EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'articles'' at line 1

I'm using MySQL 5.1.something, so I don't think information_schema not being defined is the issue. Any ideas?

Thank you for your help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

久夏青 2024-10-12 11:07:00

你不能这样做。 IF EXISTS 仅对创建表/数据库有意义。

如果您需要删除存在的列 - 您可以使用

ALTER IGNORE TABLE DROP COLUMN ...

IGNORE 上的注释。

You cannot do this. IF EXISTS is meaningful only for creating tables/databases

If you need to drop column if it exists - you could use

ALTER IGNORE TABLE DROP COLUMN ...

Note on IGNORE.

回复收藏 0 原文

还不是爱你 2024-10-12 11:07:00

你有几个选择。

一种选择是仅运行 ALTER TABLE 命令，并在该列不存在时忽略错误。

与您最初尝试更接近的另一个选项是，如果该列存在，则有条件地将 DDL 转储到磁盘上的 SQL 脚本，然后获取该 SQL 脚本来运行 DDL（如果有）。

这是一个例子：

-- delete the SQL script if it exists
\! rm /tmp/drop_column.sql

-- Conditionally dump the DDL to a SQL script if the column exists
SELECT concat('ALTER TABLE ',table_schema,'.',table_name,
' DROP COLUMN ',column_name,';') as sql_stmt
into outfile '/tmp/drop_column.sql'
FROM INFORMATION_SCHEMA.columns 
WHERE table_name = 'MyTableName' AND column_name = 'ColumnInQuestion';

-- execute the SQL script
\. /tmp/drop_column.sql

-- delete the SQL script from disk
\! rm /tmp/drop_column.sql

You have a couple of options.

One option is to just run the ALTER TABLE command and ignore the error if the column doesn't exist.

Another option closer in spirit to your original attempt is to conditionally dump the DDL to a SQL script on disk if the column exists, then source that SQL script to run the DDL (if any).

Here's an example:

-- delete the SQL script if it exists
\! rm /tmp/drop_column.sql

-- Conditionally dump the DDL to a SQL script if the column exists
SELECT concat('ALTER TABLE ',table_schema,'.',table_name,
' DROP COLUMN ',column_name,';') as sql_stmt
into outfile '/tmp/drop_column.sql'
FROM INFORMATION_SCHEMA.columns 
WHERE table_name = 'MyTableName' AND column_name = 'ColumnInQuestion';

-- execute the SQL script
\. /tmp/drop_column.sql

-- delete the SQL script from disk
\! rm /tmp/drop_column.sql

回复收藏 0 原文

~没有更多了~