Database Reference
In-Depth Information
If a table uses an engine that differs in value-reuse behavior from the behavior you
require, use
ALTER
TABLE
to change the table to a more appropriate engine. For example,
to change a table to use InnoDB (to prevent sequence values from being reused after
rows are deleted), do this:
ALTER
TABLE
tbl_name
ENGINE
=
InnoDB
;
If you don't know what engine a table uses, consult
INFORMATION_SCHEMA
or use
SHOW
TABLE
STATUS
or
SHOW
CREATE
TABLE
to find out. For example, the following statement
indicates that
insect
is an InnoDB table:
mysql>
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES
->
WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'insect';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
To empty a table and reset the sequence counter (even for engines that normally do not
reuse values), use
TRUNCATE
TABLE
:
TRUNCATE
TABLE
tbl_name
;
13.4. Retrieving Sequence Values
Problem
After creating a row that includes a new sequence number, you want to know what that
number is.
Solution
Invoke the
LAST_INSERT_ID()
function. If you're writing a program, your MySQL API
may provide a way to get the value directly without issuing an SQL statement.
Discussion
It's common for applications to need to know the
AUTO_INCREMENT
value of a newly
created row. For example, if you write a web-based frontend for entering rows into
Junior's
insect
table, you might have the application display each new row nicely for‐
matted in a new page immediately after you hit the Submit button. To do this, you must
know the new
id
value so that you can retrieve the proper row. Another situation in
which the
AUTO_INCREMENT
value is needed occurs when you use multiple tables: after
inserting a row in a master table, you need its ID to create rows in other related tables
that refer to the master row. (
Recipe 13.11
shows how to do this.)