如何转义用作列名的保留字?MySQL/创建表

2021-11-20 00:00:00 escaping mysql

我正在从 .NET 中的类生成表，一个问题是一个类可能有一个字段名称 key，这是一个保留的 MySQL 关键字.如何在 create table 语句中转义它?(注意:下面的另一个问题是文本必须是固定大小才能被索引/唯一)

I am generating tables from classes in .NET and one problem is a class may have a field name key which 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)

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;

推荐答案

如果 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;

(来源:MySQL 参考手册，9.3 保留字)

相关文章