MySQL INSERT INTO syntax

Question

I have the following structure

user_id int(11)
right   int(11)
group_id int(11)
value   tinyint(1)

And 3 queries

INSERT INTO  user_rights (`user_id`,`right`,`group_id`,`value`)
VALUES ( '42',  '160',  '1',  '1' );

INSERT INTO  user_rights ('user_id','right','group_id','value')
VALUES ( '42',  '160',  '1',  '1' );

INSERT INTO  user_rights (user_id,right,group_id,value)
VALUES ( '42',  '160',  '1',  '1' );

Explain me WHYYYY only the first works????

I have all my life used the third one!

The second should never work... – Michael Berkowski Jul 29 '12 at 16:02 — Michael Berkowski, Jul 29 '12 at 16:02
Use the word "permissions", it will be correct. – I159 Sep 14 '13 at 19:44 — I159, Sep 14 '13 at 19:44

score 13 · Accepted Answer · answered Jul 29 '12 at 16:01

13

RIGHT is a mySQL reserved word. It will work only when wrapped in backticks.

When you're not using reserved words, it will work without the backticks, as well.

The second way will never work because quotes are used to quote strings, but never database, table or column identifiers.

answered Jul 29 '12 at 16:01

Pekka

442,112
142
972
1,088

thanks, but ... this is an stupidity, why not ' and ` ?? ... nevermind, thanks – Master345 Jul 29 '12 at 16:04
@Row no problem. There are good reasons for this distinction - one is for objects like column and table names, the other for strings. But I agree mySQL's error messages are a bit cryptic in this regard – Pekka Jul 29 '12 at 16:27

score 0 · Answer 2 · answered Jul 29 '12 at 16:13

0

second one will not work also since "Right" is a reserved keyword for mysql http://drupal.org/node/141051 it will also not work if you want to work that query you have to use ` for right

answered Jul 29 '12 at 16:13

Rakesh Shetty

4,548
7
40
79

MySQL INSERT INTO syntax

2 Answers2