开发者

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

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 M开发者_开发技巧ySQL >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?


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


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
) 


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) 


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜