Database Reference
In-Depth Information
values that make each row easy to refer to. An
AUTO_INCREMENT
column is good for this
purpose, so a better
insect
table has a structure like this:
CREATE
TABLE
insect
(
id
INT
UNSIGNED
NOT
NULL
AUTO_INCREMENT
,
PRIMARY
KEY
(
id
),
name
VARCHAR
(
30
)
NOT
NULL
,
#
type
of
insect
date
DATE
NOT
NULL
,
#
date
collected
origin
VARCHAR
(
30
)
NOT
NULL
#
where
collected
);
Go ahead and create the
insect
table using this second
CREATE
TABLE
statement.
(
Recipe 13.2
discusses the particulars of the
id
column definition.)
Now that you have an
AUTO_INCREMENT
column, use it to generate new sequence values.
One of the useful properties of an
AUTO_INCREMENT
column is that you need not assign
its values yourself: MySQL does so for you. There are two ways to generate new
AU
TO_INCREMENT
values in the
id
column. One is to explicitly set the
id
column to
NULL
.
The following statement inserts the first four of Junior's specimens into the
insect
table
that way:
mysql>
INSERT INTO insect (id,name,date,origin) VALUES
->
(NULL,'housefly','2014-09-10','kitchen'),
->
(NULL,'millipede','2014-09-10','driveway'),
->
(NULL,'grasshopper','2014-09-10','front yard'),
->
(NULL,'stink bug','2014-09-10','front yard');
Alternatively, omit the
id
column from the
INSERT
statement entirely. MySQL permits
creating rows without explicitly specifying values for columns that have a default value.
MySQL assigns each missing column its default value, and the default for an
AUTO_IN
CREMENT
column is its next sequence number. Thus, this statement adds Junior's other
four specimens to the
insect
table and generates sequence values without naming the
id
column at all:
mysql>
INSERT INTO insect (name,date,origin) VALUES
->
('cabbage butterfly','2014-09-10','garden'),
->
('ant','2014-09-10','back yard'),
->
('ant','2014-09-10','back yard'),
->
('termite','2014-09-10','kitchen woodwork');
Whichever method you use, MySQL determines the sequence number for each row and
assigns it to the
id
column, as you can verify:
mysql>
SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 3 | grasshopper | 2014-09-10 | front yard |
