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.)
Search WWH ::




Custom Search