创建表时出错:您的 SQL 语法在第 1 行的“order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id "附近有错误

2021-11-20 00:00:00 sql database mysql mysql-error-1064

我正在尝试使用 PHP 脚本在同一个 MySQL 数据库中创建 2 个表:主键为user_id"的表user"和主键为order_id"的表order"和来自user"表的外键user_id"(一对多关系).

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).

表用户创建成功，没有问题:

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";

我收到以下错误:

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.

推荐答案

你需要转义 保留字，如带反引号的order

You need to escape reserved words like order with backticks

CREATE TABLE `order` ( ...

或者更好地使用其他名称.

or better use another name instead.

相关文章