Oracle 5.0, Справочное руководство

Страница: 241 / 3232

Using
AUTO_INCREMENT
221
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
No value was specified for the
AUTO_INCREMENT
column, so MySQL assigned sequence numbers
automatically. You can also explicitly assign
NULL
or 0 to the column to generate sequence numbers.
You can retrieve the most recent
AUTO_INCREMENT
value with the
LAST_INSERT_ID()
[961] SQL
function or the
mysql_insert_id()
C API function. These functions are connection-specific, so their
return values are not affected by another connection which is also performing inserts.
Use a large enough integer data type for the
AUTO_INCREMENT
column to hold the maximum
sequence value you will need. When the column reaches the upper limit of the data type, the next
attempt to generate a sequence number fails. For example, if you use
TINYINT
, the maximum
permissible sequence number is 127. For
TINYINT UNSIGNED
, the maximum is 255.
Note
For a multiple-row insert,
LAST_INSERT_ID()
[961] and
mysql_insert_id()
actually return the
AUTO_INCREMENT
key from the
first of the inserted rows. This enables multiple-row inserts to be reproduced
correctly on other servers in a replication setup.
For
MyISAM
and
BDB
tables you can specify
AUTO_INCREMENT
on a secondary column in a multiple-
column index. In this case, the generated value for the
AUTO_INCREMENT
column is calculated as
MAX(auto_increment_column) + 1 WHERE prefix=given-prefix
[971] . This is useful
when you want to put data into ordered groups.
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;
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;
Which returns:
+--------+----+---------+
| grp | id | name |