创建表时出错：您的SQL语法中出现错误'order（order_id INT UNSIGNED NOT NULL AUTO_INCREMENT，user_id'在第1行[重复]

This question already has an answer here:

这个问题在这里已有答案：

Syntax error due to using a reserved word as a table or column name in MySQL 1 answer
由于在MySQL 1答案中使用保留字作为表或列名称而导致语法错误

I am trying to create 2 tables in the same MySQL database with a PHP-script: table 'user' with primary key 'user_id' and table 'order' with primary key 'order_id' and foreign key 'user_id' from the 'user' table (1 to many relationship).

我试图用PHP脚本在同一个MySQL数据库中创建2个表：表'user'主键'user_id'和表'order'主键'order_id'和外键'user_id'来自'user'表（1对多关系）。

Table user creates successfully without problems:

表用户成功创建没有问题：

$sql="CREATE TABLE user(
    user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    type ENUM('member','admin') NOT NULL,
    username VARCHAR(30) NOT NULL,
    email VARCHAR(80) NOT NULL,
    pass VARBINARY(32) NOT NULL,
    first_name VARCHAR(40) NOT NULL,
    last_name VARCHAR(40) NOT NULL,
    date_expires DATE NOT NULL,
    date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    date_modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (user_id),
    UNIQUE (username),
    UNIQUE (email) 
    )ENGINE=InnoDB DEFAULT CHARSET=utf8";

However, I am not able to create table order:

但是，我无法创建表顺序：

$sql="CREATE TABLE order(
    order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    transaction_id VARCHAR(19) NOT NULL,
    payment_status VARCHAR(15) NOT NULL,
    payment_amount DECIMAL(6,2) UNSIGNED NOT NULL,
    payment_date_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id) REFERENCES user (user_id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8";

I get the following error:

我收到以下错误：

Error creating table: 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 'order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ' at line 1

Already checked the syntax and cannot find the mistake. Could you please advise what went wrong? Thanks a lot.

已经检查了语法并找不到错误。你能告诉我出了什么问题吗？非常感谢。

2 个解决方案

#1

You need to escape reserved words like order with backticks

你需要用反引号来逃避保留的单词

CREATE TABLE `order` ( ...

#2

order is keyword used by mysql like (select from tbl_name order by id ASC) so for escaping from using keywords you have to use quotes `` to avoid my sql error

order是mysql使用的关键字（从id ASC中选择tbl_name顺序）所以为了逃避使用关键字你必须使用引号``来避免我的sql错误

so your query should by

所以你的查询应该由

$sql="CREATE TABLE `order` (
order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
transaction_id VARCHAR(19) NOT NULL,
payment_status VARCHAR(15) NOT NULL,
payment_amount DECIMAL(6,2) UNSIGNED NOT NULL,
payment_date_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (order_id),
FOREIGN KEY (user_id) REFERENCES user (user_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8";

enjoy :D

享受：D

#1