MySQL INSERT - Do field names require backtick/accent delimination?

Question

I am somewhat new to MySQL (come from MS SQL) and have recently come across something very confusing to me when trying to INSERT values into the database.

I created an INSERT SQL command via PHP yet found it threw an error when trying to execute.

INSERT INTO myTableName (first-name, last-name) VALUES ('Ted', 'Johnson')

To my way of thinking the above string should have worked .. but it didn't. I even tried using it directly in phpMyAdmin but I got the same syntax error.

The only thing that ended up making it work is if I surrounded the field names in the SQL statement by the "backtick" or "accent" character (the other character on the tilde key on the keyboard). For example ...

INSERT INTO myTableName(`first-name`, `last-name`) VALUES ('Ted', 'Johnson')

I have never known this to be necessary in MySQL or MS SQL Server. I have always just listed the field names without delimiting them. Has anyone run across this before? I am using MySQL 5.0.77. Thank you.

Answer 1

Your column names contain hyphens, which is generally not a good idea, since the parser could misinterpret as first minus name. The back-ticks are the MySQL method of quoting column names to ensure they're treated correctly.

If your columns were first_name and last_name, the back-ticks would not be necessary.

Answer 2

You only need the backticks if your column names collide with SQL reserved words or symbols. In your case, the dash that separates first and name (and the one between last and name) is interpreted as a minus sign. Additionally the word first is used in MySQL's ALTER TABLE statement (Although it actually doesn't appear in MySQL's reserved words list). If you can rename your columns using underscores instead of dashes to separate words, you won't need the backticks.