MySQL INSERT - Are field names needed for backward markup / accent?

Question

I am a little new to MySQL (came from MS SQL) and recently encountered something very confusing for me when trying to insert INSERT values ​​in a database.

I created the INSERT SQL command through PHP, but found that an error occurred while trying to execute it.

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

To my way of thinking, the above line should work. But this is not so. I even tried using it directly in phpMyAdmin, but I got the same syntax error.

The only thing that ended up making it work was that I surrounded the field names in the SQL expression with the "backtick" or "accent" symbol (another symbol on the tilde on the keyboard). For instance...

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

I never knew that it was necessary in MySQL or MS SQL Server. I always simply listed the names of the fields without limiting them. Has anyone come across this before? I am using MySQL 5.0.77. Thanks.

Answer 1

The column names contain hyphens, which is usually not very good, since the analyzer may be incorrectly interpreted as the first minus name . Back-ticks is a MySQL method for quoting column names to make sure they are processed correctly.

If your columns were first_name and last_name, back ticks are not needed.

Answer 2

You only need return outputs if the column names match reserved words or SQL characters. In your case, a dash separating first and name (and one between last and name ) is interpreted as a minus sign. In addition, the word first used in the MySQL ALTER TABLE statement (although it does not actually appear in the MySQL Reserved Word List ). If you can rename your columns using underscores instead of dashes to separate words, you won't need backlinks.

Answer 3

Well, even the syntax of SO displays last as blue in your first code (a little joke.) I'm not a MYSQL professional, but I always use backreferences because I want to program so they always use them. If you use feedback signals naturally, you can avoid many problems in the future. I would also pay attention to your table:

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