'How to use special characters in MySQL column names?

Here is my failing MySQL code:

CREATE TABLE product (
            id int NOT NULL AUTO_INCREMENT,
            'item-name' VARCHAR(255) NOT NULL,
            'item-description' TEXT,
            'listing-id' VARCHAR(50),
            PRIMARY KEY (id)
        )

The error is:

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
'item-name' VARCHAR(255) NOT NULL, 'item-description' TEXT, 'listing-id'' at line 3

The documentation says to use quotes... What is wrong?

mysqlcreate-table


Solution 1:[1]

You should use the back-quote (`) to quote column names, not the single-quote ('). Look above the tilde key (~).

CREATE TABLE product (
    id INT NOT NULL AUTO_INCREMENT,
    `item-name` VARCHAR(255) NOT NULL,
    `item-description` TEXT,
    `listing-id` VARCHAR(50),
    PRIMARY KEY (id)
)

Solution 2:[2]

Why do you use quotes? You should use backticks. Try this:

CREATE TABLE product (
    id int NOT NULL AUTO_INCREMENT,
    `item-name` VARCHAR(255) NOT NULL,
    `item-description` TEXT,
    `listing-id` VARCHAR(50),
    PRIMARY KEY (id)
)

Solution 3:[3]

Please, use normal character in table definition. You can use underscore "_" instead "-".

When you'll query your db, you must always use quote. Impossible for me! :)

If you want follow this road, use the quote

CREATE TABLE product (
    id INT NOT NULL AUTO_INCREMENT,
    `item-name` VARCHAR(255) NOT NULL,
    `item-description` TEXT,
    `listing-id` VARCHAR(50),
    PRIMARY KEY (id)
)

Solution 4:[4]

maybe it is coming late. but this worked for me.

CREATE TABLE product ( id INT NOT NULL AUTO_INCREMENT, [item-name] VARCHAR(255) NOT NULL, [item-description] TEXT, [listing-id] VARCHAR(50), PRIMARY KEY (id) )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gustav Bertram
Solution 2 radbyx
Solution 3 Joe Taras
Solution 4 ADEOLU FABIYI

Related Questions