Why Does Creating a MySQL Table Named 'order' Result in a SQL Syntax Error?

SQL syntax error while creating MySQL table named "order"

When using a PHP script to create a MySQL table named "order", you may encounter the error: "Create table error: Your SQL syntax has an error near 'order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ', p. 1 row [duplicate]". This issue may be caused by a syntax error in the SQL statement.

Specifically, a syntax error occurs because the reserved word "order" is used and not escaped correctly. In SQL, reserved words must be escaped using backticks (`) to distinguish them from table or column names.

To work around this error, you can escape the "order" reserved word as follows:

<code class="language-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;</code>

By doing this, MySQL will recognize "order" as a valid table name and the table will be created successfully.

Alternatively, you can avoid using reserved words and use a different name for your table. This helps prevent potential syntax errors in the future.

The above is the detailed content of Why Does Creating a MySQL Table Named 'order' Result in a SQL Syntax Error?. For more information, please follow other related articles on the PHP Chinese website!