MySQL Forums :: Newbie :: How MySQL MYISAM manage the auto_increment column


Advanced Search

How MySQL MYISAM manage the auto_increment column
Posted by: scarlettxu xu ()
Date: November 18, 2011 01:58AM

With MYSQL MYISAM, we can define composite primary keys with two columns, and one column is auto_increment.

when defined like below:
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)
) ENGINE=MyISAM;

the id is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+


but when defined as
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)
) ENGINE=MyISAM;
the id will be generated by sequence.
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 2 | dog |
| mammal | 3 | cat |
| mammal | 4 | whale |
| bird | 5 | penguin |
| bird | 6 | ostrich |
+--------+----+---------+

there is one description in the manual http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html
---------------quote----------------------
If the AUTO_INCREMENT column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.
------------------quote---------------------------

But I am not quite understand of above words.

the manual describe the first create table situation as "the AUTO_INCREMENT column is part of a multiple-column index".
then for the other create table, it should be "the AUTO_INCREMENT column is part of multiple indexes".
what is the difference of the two descriptions(part of a multiple-column index) and (part of multiple indexes)?

Options: ReplyQuote


Subject Written By Posted
How MySQL MYISAM manage the auto_increment column scarlettxu xu 11/18/2011 01:58AM
Re: How MySQL MYISAM manage the auto_increment column Rick James 11/19/2011 09:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.