Rechercher dans le manuel MySQL
13.1.37 TRUNCATE TABLE Syntax
TRUNCATE TABLE
empties a table
completely. It requires the DROP
privilege. Logically, TRUNCATE
TABLE
is similar to a
DELETE
statement that deletes all
rows, or a sequence of DROP TABLE
and CREATE TABLE
statements.
To achieve high performance, TRUNCATE
TABLE
bypasses the DML method of deleting data. Thus, it
does not cause ON DELETE
triggers to fire, it
cannot be performed for InnoDB
tables with
parent-child foreign key relationships, and it cannot be rolled
back like a DML operation. However, TRUNCATE
TABLE
operations on tables that use an atomic
DDL-supported storage engine are either fully committed or rolled
back if the server halts during their operation. For more
information, see Section 13.1.1, “Atomic Data Definition Statement Support”.
Although TRUNCATE TABLE
is similar
to DELETE
, it is classified as a
DDL statement rather than a DML statement. It differs from
DELETE
in the following ways:
Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
Truncate operations cause an implicit commit, and so cannot be rolled back. See Section 13.3.3, “Statements That Cause an Implicit Commit”.
Truncation operations cannot be performed if the session holds an active table lock.
TRUNCATE TABLE
fails for anInnoDB
table orNDB
table if there are anyFOREIGN KEY
constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is “0 rows affected,” which should be interpreted as “no information.”
As long as the table definition is valid, the table can be re-created as an empty table with
TRUNCATE TABLE
, even if the data or index files have become corrupted.Any
AUTO_INCREMENT
value is reset to its start value. This is true even forMyISAM
andInnoDB
, which normally do not reuse sequence values.When used with partitioned tables,
TRUNCATE TABLE
preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions are unaffected.The
TRUNCATE TABLE
statement does not invokeON DELETE
triggers.Truncating a corrupted
InnoDB
table is supported.
TRUNCATE TABLE
for a table closes
all handlers for the table that were opened with
HANDLER OPEN
.
TRUNCATE TABLE
is treated for
purposes of binary logging and replication as
DROP TABLE
followed by
CREATE TABLE
—that is, as DDL
rather than DML. This is due to the fact that, when using
InnoDB
and other transactional
storage engines where the transaction isolation level does not
permit statement-based logging (READ
COMMITTED
or READ
UNCOMMITTED
), the statement was not logged and
replicated when using STATEMENT
or
MIXED
logging mode. (Bug #36763) However, it is
still applied on replication slaves using
InnoDB
in the manner described
previously.
In MySQL 5.7 and earlier, on a system with a large buffer pool and
innodb_adaptive_hash_index
enabled, a TRUNCATE TABLE
operation could cause
a temporary drop in system performance due to an LRU scan that
occurred when removing the table's adaptive hash index entries
(Bug #68184). The remapping of TRUNCATE
TABLE
to DROP TABLE
and
CREATE TABLE
in MySQL 8.0 avoids
the problematic LRU scan.
TRUNCATE TABLE
can be used with
Performance Schema summary tables, but the effect is to reset the
summary columns to 0 or NULL
, not to remove
rows. See Section 26.12.17, “Performance Schema Summary Tables”.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-truncate-table.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.