SQL Column Names same as names for data types - causing errors

Question

I am very new to SQL and having problems with creating a table structure.

I want to create a table with four columns - id,text,date and replace. Problem is this is giving me an error in MySQL, I think because the words TEXT and DATE are also names for data types and REPLACE is another term used in SQL so MySQL is getting confused about what my column names should be or doesn't realise the names I've given are actual names. Is there any way I can get around this to get the column names I want, without having to call the columns something else and then change them back manually once created?

Here's my SQL:

CREATE TABLE message (
    id INT UNIQUE AUTO_INCREMENT NOT NULL,
    text TEXT,
    date INT,
    replace INT DEFAULT 0
);

And the error I'm getting:

#1064 - 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 'replace INT DEFAULT 0 )' at line 5

CREATE TABLE message(
    id INT UNIQUE AUTO_INCREMENT NOT NULL ,
    TEXT TEXT,
    DATE INT,
    REPLACE INT DEFAULT 0
);

Answer 1

General rule is don't use these keywords, come up with something different than 'text' for your field name. If you must, use ` (back-tick) around the column name to tell SQL it's a column name and not what the keyword means. I recommend against calling your columns 'from' 'select' and 'where' as well ;)