mysql insert errors... not sure what's wrong

Question

i can't figure out why this isn't working. using mysql version 5.0.91-log. and phpMyAdmin

INSERT INTO 'sequences'(
'key', 'author', 'tlength', 'progress', 'time')
VALUES (
NULL , '00000', 10, 1, NULL
)

gets the following error.

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 ''sequences'( 'key', 'author', 'tlength', 'progress', 'time') VALUES ( NULL , ' at line 1

here's the table (name, type, attributes, null, default, extra)

key        int(11)     UNSIGNED   No                        auto_increment
author     char(20)               No        
tlength    tinyint(3)  UNSIGNED   No        
progress   tinyint(4)  UNSIGNED   No    0   
time       timestamp              Yes   CURRENT_TIMESTAMP

i've tried many combinations of the above query with/without quotes and other variations. any ideas?

Tim Fountain · Accepted Answer · 2011-03-29 09:21:47Z

4

Two things:

Don't quote the table name or fields
'Key' is a reserved word in MySQL so you need to enclose that field name with backticks

this will give you:

INSERT INTO sequences (
`key`, author, tlength, progress, time)
VALUES (
NULL , '00000', 10, 1, NULL
)

it's often easier just to quote all field names and table names with backticks just in case, but here's a full list of all the reserved words which can catch you out: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

answered Mar 29, 2011 at 9:21

Tim Fountain

33.2k5 gold badges44 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eisberg · Accepted Answer · 2011-03-29 09:20:26Z

3

By default you should not put table names in quotes. Try:

INSERT INTO sequences (
    'key', 'author', 'tlength', 'progress', 'time')
VALUES (
    NULL , '00000', 10, 1, NULL
)

answered Mar 29, 2011 at 9:20

eisberg

3,7812 gold badges29 silver badges38 bronze badges

3 Comments

Quassnoi Over a year ago

There should be no quotes in column names too

Victor Over a year ago

as i said... i've tried with and without quotes. i'm still getting an error, with differences in the code excerpt and line number.

eisberg Over a year ago

@Victor can you give us the error message when you try it without quotes?

Quassnoi · Accepted Answer · 2011-03-29 09:21:46Z

3

Replace the quotes with the backticks for the table and column names:

INSERT
INTO    `sequences` (`key`, `author`, `tlength`, `progress`, `time`)
VALUES  (NULL, '00000', 10, 1, NULL)

answered Mar 29, 2011 at 9:21

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Comments

Álvaro González · Accepted Answer · 2011-03-29 09:29:34Z

0

As in most other languages, quotes are used as string delimiters, so the interpreter can distinguish a keyword from a literal. Just run this query to see the difference:

SELECT 'CURRENT_TIMESTAMP', CURRENT_TIMESTAMP

If you quote table and column names, you are telling MySQL that they are not database objects.

Also, you have a column called key. That's a reserved word in MySQL you need to escape it. The syntax MySQL uses for such cases is the back quote:

INSERT INTO sequences (`key`, author, tlength, progress, time)
VALUES (NULL , '00000', 10, 1, NULL)

Last but not least, you cannot insert NULL in the key column if you've defined it as NOT NULL. That's what NOT NULL means.

answered Mar 29, 2011 at 9:29

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Collectives™ on Stack Overflow

mysql insert errors... not sure what's wrong

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related