创建查询时出现SQL语法错误

When I run query below on phpMyAdmin and Sequel, I got the following error

当我在phpMyAdmin和Sequel上运行查询时,我收到以下错误

#1064 - 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 'id Int UNSIGNED NOT NULL AUTO_INCREMENT, login Char(128) NOT NULL, passw' at line 3.

#1064 - 您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,以便在'id Int UNSIGNED NOT NULL AUTO_INCREMENT附近使用正确的语法,在第3行登录Char(128)NOT NULL,passw'。

I can't figure out the error on this query. Mysql is running on 5.5.25 version.

我无法弄清楚这个查询的错误。 Mysql在5.5.25版本上运行。

CREATE TABLE user
(
  id Int UNSIGNED NOT NULL AUTO_INCREMENT,
  login Char(128) NOT NULL,
  password Char(128) NOT NULL,
  emailaddress Char(128),
  alternateemailaddress Char(128),
  officephone Char(20),
  officefax Char(20),
  mobilephone Char(20),
  client Int UNSIGNED,
  facility Int UNSIGNED,
  user_status Int UNSIGNED NOT NULL DEFAULT 0,
  valid_from Datetime NOT NULL,
  valid_to Datetime NOT NULL,
  last_login Datetime,
  last_login_from Char(48),
  modified_by Int NOT NULL,
  modified_time Datetime,

  PRIMARY KEY (id),
  FOREIGN KEY client REFERENCES client (id) ON DELETE CASCADE,
  FOREIGN KEY facility REFERENCES facility (id) ON DELETE CASCADE
 
 ) ENGINE = InnoDB
  AUTO_INCREMENT = 0
;

2 个解决方案

#1

You should use backsticks when writing names of tables and fields to avoid MySQL parse it as reserved keywords. You should also use parentheses wrapping your foreign keys names. Working query should look like this:

在编写表和字段名称时应该使用后台,以避免MySQL将其解析为保留关键字。您还应该使用包装外键名称的括号。工作查询应如下所示:

CREATE TABLE `user`
(
  `id` Int UNSIGNED NOT NULL AUTO_INCREMENT,
  `login` Char(128) NOT NULL,
  `password` Char(128) NOT NULL,
  `emailaddress` Char(128),
  `alternateemailaddress` Char(128),
  `officephone` Char(20),
  `officefax` Char(20),
  `mobilephone` Char(20),
  `client` Int UNSIGNED,
  `facility` Int UNSIGNED,
  `user_status` Int UNSIGNED NOT NULL DEFAULT 0,
  `valid_from` Datetime NOT NULL,
  `valid_to` Datetime NOT NULL,
  `last_login` Datetime,
  `last_login_from` Char(48),
  `modified_by` Int NOT NULL,
  `modified_time` Datetime,

  PRIMARY KEY (`id`),
  FOREIGN KEY (`client`) REFERENCES `client` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`facility`) REFERENCES `facility` (`id`) ON DELETE CASCADE

 ) ENGINE = InnoDB
  AUTO_INCREMENT = 0
;

#2

This works for me.
See my successful SQLFiddle

这对我有用。看看我成功的SQLFiddle

I suspect you have some odd whitespace characters in your input, perhaps tabs.

我怀疑你的输入中有一些奇怪的空白字符,也许是标签。

#1