TRUNCATE TABLE
Syntax
1058
RENAME TABLE old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;
As long as two databases are on the same file system, you can use
RENAME TABLE
to move a table
from one database to another:
RENAME TABLE
current_db.tbl_name
TO
other_db.tbl_name;
Beginning with MySQL 5.0.2, if there are any triggers associated with a table which is moved to a
different database using
RENAME TABLE
, then the statement fails with the error
Trigger in wrong
schema
.
As of MySQL 5.0.14,
RENAME TABLE
also works for views, as long as you do not try to rename a view
into a different database.
Any privileges granted specifically for the renamed table or view are not migrated to the new name.
They must be changed manually.
When you execute
RENAME
, you cannot have any locked tables or active transactions. You must
also have the
ALTER
[576]
and
DROP
[577]
privileges on the original table, and the
CREATE
[576]
and
INSERT
[577]
privileges on the new table.
If MySQL encounters any errors in a multiple-table rename, it does a reverse rename for all renamed
tables to return everything to its original state.
You cannot use
RENAME
to rename a
TEMPORARY
table. However, you can use
ALTER TABLE
instead:
mysql>
ALTER TABLE orig_name RENAME new_name;
13.1.21.
TRUNCATE TABLE
Syntax
TRUNCATE [TABLE]
tbl_name
TRUNCATE TABLE
empties a table completely. Logically, this is equivalent to a
DELETE
statement that
deletes all rows, but there are practical differences under some circumstances.
For an
InnoDB
table before version 5.0.3,
InnoDB
processes
TRUNCATE TABLE
by deleting rows one
by one. As of MySQL 5.0.3, row by row deletion is used only if there are any
FOREIGN KEY
constraints
that reference the table. If there are no
FOREIGN KEY
constraints,
InnoDB
performs fast truncation
by dropping the original table and creating an empty one with the same definition, which is much faster
than deleting rows one by one. (When fast truncation is used, it resets any
AUTO_INCREMENT
counter
to zero. From MySQL 5.0.13 on, the
AUTO_INCREMENT
counter is reset to zero by
TRUNCATE TABLE
,
regardless of whether there is a foreign key constraint.)
In the case that
FOREIGN KEY
constraints reference the table,
InnoDB
deletes rows one by one and
processes the constraints on each one. If the
FOREIGN KEY
constraint specifies
DELETE CASCADE
,
rows from the child (referenced) table are deleted, and the truncated table becomes empty. If the
FOREIGN KEY
constraint does not specify
CASCADE
, the
TRUNCATE TABLE
statement deletes rows
one by one and stops if it encounters a parent row that is referenced by the child, returning this error:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign
key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1`
FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
This is the same as a
DELETE
statement with no
WHERE
clause.
The count of rows affected by
TRUNCATE TABLE
is accurate only when it is mapped to a
DELETE
statement.
For other storage engines,
TRUNCATE TABLE
differs from
DELETE
in the following ways in MySQL 5.0:
• Truncate operations drop and re-create the table, which is much faster than deleting rows one by
one, particularly for large tables.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
