今天看着MySQL手册学习时,看见一个关于AUTO_INCREMENT用法的例子,就在本地自己试着运行了一下,结果报错了。手册例子如下:
CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id)); INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich'); SELECT * FROM animals ORDER BY grp,id;将返回:+--------+----+---------+| grp | id | name |+--------+----+---------+| fish | 1 | lax || mammal | 1 | dog || mammal | 2 | cat || mammal | 3 | whale || bird | 1 | penguin || bird | 2 | ostrich |+--------+----+---------+[/b]结果我自己复制下来运行时,结果报错了,如下:
[Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key哪位高手来解释一下怎么回事啊?
CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id)); INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich'); SELECT * FROM animals ORDER BY grp,id;将返回:+--------+----+---------+| grp | id | name |+--------+----+---------+| fish | 1 | lax || mammal | 1 | dog || mammal | 2 | cat || mammal | 3 | whale || bird | 1 | penguin || bird | 2 | ostrich |+--------+----+---------+[/b]结果我自己复制下来运行时,结果报错了,如下:
[Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key哪位高手来解释一下怎么回事啊?
上面的语句确实是错的,得下面这样才行
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id,grp)
);
或者
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');SELECT * FROM animals ORDER BY grp,id;多了个ENGINE=MyISAM,试了下确实没有错了,也能实现手册例子中的检索结果。只是不明白是为什么。