如何查找列名是否是各个数据库中的保留关键字
我们有一个旧模式文件，其中包含以下列名称，想了解如果我们将应用程序移植到 h2、mysql、postgres 或 oracle，它们中的任何一个是否会导致问题。
例如，我怎么知道在下面列出的数据库中使用它是否可以。有没有什么好的工具可以对此进行初步检查？
key
when
size
type
count
from
with
We have a legacy schema file which has the following column names, would like to understand if any of them would cause an issue if we port our application to h2, mysql, postgres or oracle.
e.g how would I know if using this would be ok across the databases listed below. Are there any good tools which can do a preliminary check on this?
key
when
size
type
count
from
with
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
应该很容易获得保留字列表并建立保留字表。以下是一些保留字列表：
http://msdn.microsoft.com/en-us/library/ms189822。 PostgreSQL
http://www.postgresql.org/docs/current/ static/sql-keywords-appendix.html
https://dev.mysql。 com/doc/refman/5.6/en/keywords.html#keywords-in-current-series
这是一个在线检查器：http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=on
Should be quite easy to get the list of reserved words and build a table of the reserved words. Here are some lists of reserved words:
http://msdn.microsoft.com/en-us/library/ms189822.aspx
http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
https://dev.mysql.com/doc/refman/5.6/en/keywords.html#keywords-in-current-series
And here's an online checker: http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=on
我不知道有任何表，但如果您有表名和列名的列表，那么过滤它们应该不难。
Oracle 有一个
V$RESERVED_WORDS在其数据字典中查看。您所要做的就是将表/列名称与此相匹配：只需将它们添加到临时表并与视图连接即可。如果您有查询结果，那么您就有一个保留字。
其他数据库也可能具有此类元数据。
I am not aware of any tables but it should not be difficult to filter them if you have the list of table names and column names.
Oracle has a
V$RESERVED_WORDSview in its data dictionary.All you have to do is to match your table/column names against this: Just add them to temp table and join with tis view. If you have a result for your query then you have a reserved word.
Other databases may have such metadata as well.
DatabaseMetaData.getSQLKeywords()应该返回此数据库中以逗号分隔的保留字列表。但是，此列表不包含 ANSI SQL 关键字，例如FROM。然而，我不完全确定这是否真的包含所有数据库中的所有关键字。对于 H2，关键字列表记录在关键字/保留字下。
DatabaseMetaData.getSQLKeywords()is supposed to return a comma-separated list of reserved words within this database. This list doesn't contain ANSI SQL keywords such asFROMhowever. I'm not completely sure if this really contains all keywords in all databases however.For H2, the list of keywords is documented under Keywords / Reserved Words.
找出答案的最简单方法就是尝试一下。您提到的所有数据库管理系统都是开源的或免费下载和试用。通过它们使用
CREATE TABLE语句运行 SQL 脚本，看看会发生什么。在不参考实际 SQL 脚本的情况下提出这个问题的问题是，某些解析器具有各种类别的保留字。某些关键字可能会被列为关键字，但可能仍然可以用作列名称，但稍后可能不会出现在棘手的 SELECT 语句中。所以尝试一下总是最好的。
我建议根据您提供的列表，它在大多数 SQL 系统中都不起作用。但是您始终可以始终对标识符加双引号以避免关键字问题。 （不过，您需要在 ANSI 模式下运行 MySQL。）
The easiest way to find out would be to try it. All of the database management systems you mention are open source or free to download and try. Run your SQL script with the
CREATE TABLEstatements through them and see what happens.The problem with asking this without reference to an actual SQL script is that some parsers have various classes of reserved words. Some key words might be listed as key words, but might still be OK to use as column names, but perhaps not in tricky
SELECTstatement later on. So it's always best to try it out.I suggest based on the list you give, it won't work in most SQL systems. But you can always consistently double quote the identifiers to steer clear of key word problems. (You will need to run MySQL in ANSI mode, though.)