如何转义用作列名的保留字？MySQL/创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2889871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I escape reserved words used as column names? MySQL/Create Table
提问by Martin Smith
I am generating tables from classes in .NET and one problem is a class may have a field name keywhich is a reserved MySQL keyword. How do I escape it in a create table statement? (Note: The other problem below is text must be a fixed size to be indexed/unique)
我正在从 .NET 中的类生成表，一个问题是一个类可能有一个字段名称key，它是一个保留的 MySQL 关键字。如何在 create table 语句中转义它？（注意：下面的另一个问题是文本必须是固定大小才能被索引/唯一）
create table if not exists misc_info (
id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
key TEXT UNIQUE NOT NULL,
value TEXT NOT NULL)ENGINE=INNODB;
回答by Martin Smith
You can use double quotes if ANSI SQL modeis enabled
如果启用了ANSI SQL 模式，则可以使用双引号
CREATE TABLE IF NOT EXISTS misc_info
(
id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
"key" TEXT UNIQUE NOT NULL,
value TEXT NOT NULL
)
ENGINE=INNODB;
or the proprietary back tick escaping otherwise. (Where to find the `character on various keyboard layouts is covered in this answer)
或以其他方式转义的专有反勾号。（此答案`涵盖了在各种键盘布局上的何处查找字符）
CREATE TABLE IF NOT EXISTS misc_info
(
id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
`key` TEXT UNIQUE NOT NULL,
value TEXT NOT NULL
)
ENGINE=INNODB;
(Source: MySQL Reference Manual, 9.3 Reserved Words)
（来源：MySQL 参考手册，9.3 保留字）
回答by Martin Smith
You should use back tick character (`) eg:
您应该使用反勾号 (`) 例如：
create table if not exists misc_info (
id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
`key` TEXT UNIQUE NOT NULL,
value TEXT NOT NULL)ENGINE=INNODB;
回答by Charalampos Serenis
If you are interested in portability between different SQL servers you should use ANSI SQL queries. String escaping in ANSI SQL is done by using double quotes ("). Unfortunately, this escaping method is not portable to MySQL, unless it is set in ANSI compatibility mode.
如果您对不同 SQL 服务器之间的可移植性感兴趣，您应该使用 ANSI SQL 查询。ANSI SQL 中的字符串转义是通过使用双引号 (") 完成的。不幸的是，这种转义方法不能移植到 MySQL，除非它在 ANSI 兼容模式下设置。
Personally, I always start my MySQL server with the --sql-mode='ANSI' argument since this allows for both methods for escaping. If you are writing queries that are going to be executed in a MySQL server that was not setup / is controlled by you, here is what you can do:
就个人而言，我总是使用 --sql-mode='ANSI' 参数启动我的 MySQL 服务器，因为这允许使用两种方法进行转义。如果您正在编写将在未设置/由您控制的 MySQL 服务器中执行的查询，您可以执行以下操作：
- Write all you SQL queries in ANSI SQL
Enclose them in the following MySQL specific queries:
SET @OLD_SQL_MODE=@@SQL_MODE; SET SESSION SQL_MODE='ANSI'; -- ANSI SQL queries SET SESSION SQL_MODE=@OLD_SQL_MODE;
- 用 ANSI SQL 编写所有 SQL 查询
将它们包含在以下 MySQL 特定查询中：
SET @OLD_SQL_MODE=@@SQL_MODE; SET SESSION SQL_MODE='ANSI'; -- ANSI SQL queries SET SESSION SQL_MODE=@OLD_SQL_MODE;
This way the only MySQL specific queries are at the beginning and the end of your .sql script. If you what to ship them for a different server just remove these 3 queries and you're all set. Even more conveniently you could create a script named: script_mysql.sql that would contain the above mode setting queries, source a script_ansi.sql script and reset the mode.
这样，唯一的 MySQL 特定查询位于 .sql 脚本的开头和结尾。如果您要将它们运送到不同的服务器，只需删除这 3 个查询即可。更方便的是，您可以创建一个名为：script_mysql.sql 的脚本，该脚本将包含上述模式设置查询、获取 script_ansi.sql 脚本并重置模式。
