mysql5.0中auto_increment列在用insert时为什么不自增?

Question

use test;
create table userinfo(
id int primary key auto_increment,
username varchar(20) not null,
password varchar(20) not null,
phone varchar(20),
email varchar(40)
);
insert into userinfo values('','林冲','lichong','13600000001','[email protected]');
insert into userinfo values('','鲁智深','luda','13600000002','[email protected]');
insert into userinfo values('','宋江','songjiang','13600000003','[email protected]');
insert into userinfo values('','扈三娘','husanniang','13600000004','[email protected]');
insert into userinfo values('','李逵','likui','13600000005','[email protected]');
insert into userinfo values('','史进','shijin','13600000006','[email protected]');
insert into userinfo values('','阮小二','ruanxiaoer','13600000007','[email protected]');
insert into userinfo values('','卢俊义','lujunyi','13600000008','[email protected]');
insert into userinfo values('','武松','wusong','13600000009','[email protected]');
insert into userinfo values('','阮小五','ruanxiaowu','13600000010','[email protected]');
insert into userinfo values('','燕青','yanqing','13600000011','[email protected]');
insert into userinfo values('','孙二娘','sunerniang','13600000012','[email protected]');
在用mysql<user.sql -u root -p时出现问题说第一个字段id无法adjust,也就是说不行?明明是auto_increment的,为什么不能这么用???

Answer 1

5.0.x现在好像不能这样用，
把
insert into userinfo values('','林冲','lichong','13600000001','[email protected]');
改成
insert into userinfo values(null,'林冲','lichong','13600000001','[email protected]');
就可以了，原因你可以通过
mysql> desc userinfo;
+----------+---------------------------+------+-----+---------+----------------+| Field    | Type                      | Null | Key | Default | Extra          |+----------+---------------------------+------+-----+---------+----------------+| id       | int(10) unsigned zerofill | NO   | PRI | NULL    | auto_increment || username | varchar(20)               | NO   |     |         |                || password | varchar(20)               | NO   |     |         |                || phone    | varchar(20)               | YES  |     | NULL    |                || email    | varchar(40)               | YES  |     | NULL    |                |+----------+---------------------------+------+-----+---------+----------------+5 rows in set (0.01 sec)它上面默认值是null而不是''.

Answer 2

可以这么用。use test;
create table userinfo(
id int(11) NOT NULL auto_increment,
username varchar(20) not null,
password varchar(20) not null,
phone varchar(20),
email varchar(40),
PRIMARY KEY  (id) 
);