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.
精彩评论