创建表时出错:第1行的'order(order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,user_id')附近的SQL语法中有错误 [英] Error creating table: You have an error in your SQL syntax near 'order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ' at line 1
问题描述
我正在尝试使用PHP脚本在同一MySQL数据库中创建2个表: 主表为"user_id"的表"user"和主表为"order_id"的表"order"和用户"表中的外键"user_id"(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).
表用户创建成功,没有问题:
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.
推荐答案
You need to escape reserved words like order
with backticks
CREATE TABLE `order` ( ...
或者最好改用其他名称.
or better use another name instead.
这篇关于创建表时出错:第1行的'order(order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,user_id')附近的SQL语法中有错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!