如果我有这样的表格:
CREATE TABLE IF NOT EXISTS users (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
username varchar(255) COLLATE utf8_unicode_ci NOT NULL,
password varchar(255) COLLATE utf8_unicode_ci NOT NULL,
email varchar(255) COLLATE utf8_unicode_ci NOT NULL,
phone varchar(255) COLLATE utf8_unicode_ci NOT NULL,
name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
我尝试使用 phpmyadmin 在其中插入一行:
INSERT INTO users ( 'john', 'johndoe', 'johndoe@gmail.com' , '123456', 'John', 2013-06-07 08:13:28, 2013-06-07 08:13:28)
那为什么我会收到错误:
#1064 - 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 ''john', 'johndoe', 'johndoe@gmail.com' , '123456', 'John', 2013-06-07 08:13:28, ' at line 1
日期也需要引用,您忘记了VALUES
关键字。
此外,由于您没有为每列插入值,因此您需要指定要插入的列。所以你的陈述会变成:
INSERT INTO users(
username,
password,
email,
phone,
name,
created_at,
updated_at)
VALUES (
'john',
'johndoe',
'johndoe@gmail.com' ,
'123456',
'John',
'2013-06-07 08:13:28',
'2013-06-07 08:13:28')
证据就在这个小提琴里:http://sqlfiddle.com/#!2/c35d9/1
您需要指定要插入的列:尝试
INSERT INTO users
(
username,
password,
email,
phone,
name,
created_at_timestamp,
updated_at_timestamp)
VALUES
(
'john',
'johndoe',
'johndoe@gmail.com' ,
'123456',
'John',
'2013-06-07 08:13:28',
'2013-06-07 08:13:28'
)
日期值应包含引号:
INSERT INTO users (username,password,email,phone,name,created_at,updated_at)
VALUES ('john',
'johndoe',
'johndoe@gmail.com' ,
'123456',
'John',
'2013-06-07 08:13:28',
'2013-06-07 08:13:28');
更新
还需要为auto_increment列提供虚拟值,或指定列名。