:meta-keywords: truncate statement
:meta-description: You can delete all records in the specified table by using the TRUNCATE statement.
********
TRUNCATE
********
You can delete all records in the specified table by using the **TRUNCATE** statement.
It has the following advantages over using the **DELETE FROM** *[schema_name.]table_name* statement without a **WHERE** clause.
* It's way faster due to deleting all indexes and constraints in advance and deleting records at once.
* There is no vacuum cost.
* It generates way lesser log records so that it is better in terms of HA replication, Recovery, and Rollback.
.. note::
* The **DELETE** trigger is disabled while deleting records with the **TRUNCATE** statement.
* The **TRUNCATE** statement initializes the **AUTO INCREMENT** column of the table. Therefore, if data is inserted, the **AUTO INCREMENT** column value increases from the initial value.
* To execute the **TRUNCATE** statement, the authorization of **ALTER**, **INDEX**, and **DELETE** is required on the table. For granting authorization, see :ref:`granting-authorization`.
* If a table is created with **DONT_REUSE_OID** option (:ref:`dont-reuse-oid`), other tables can refer to it as a column. When the specified table is referred to like this, it is impossible to truncate the table and it's done with **DELETE FROM** statement internally.
::
TRUNCATE [ TABLE ] [schema_name.]table_name [ CASCADE ]
* *schema_name*: Specifies the schema name. If omitted, the schema name of the current session is used.
* *table_name* : Specifies the name of the table that contains the data to be deleted.
* **CASCADE** : Deletes all records in all tables referring to the specified table with a foreign key. This is propagated to all tables in the foreign key relationship. A **PRIMARY KEY** constraint has to be defined in the table and this has to be referred to by one or more **FOREIGN KEY**, and the foreign key action has to be **ON DELETE**. It fails without this option when a foreign key referring to the specified table is defined. It also fails when even one **ON DELETE** action is not **CASCADE** in all foreign key relationships. See :ref:`foreign-key-constraint` for more information about the foreign key constraint.
.. code-block:: sql
CREATE TABLE a_tbl(A INT AUTO_INCREMENT(3,10) PRIMARY KEY);
INSERT INTO a_tbl VALUES (NULL),(NULL),(NULL);
SELECT * FROM a_tbl;
::
a
=============
3
13
23
.. code-block:: sql
--AUTO_INCREMENT column value increases from the initial value after truncating the table
TRUNCATE TABLE a_tbl;
INSERT INTO a_tbl VALUES (NULL);
SELECT * FROM a_tbl;
::
a
=============
3
The following example uses the **CASCADE** option. Note that even a record refers to nothing, but the propagated **TRUNCATE** deletes all records.
.. code-block:: sql
-- a_tbl <- b_tbl <- c_tbl
-- <- d_tbl
CREATE TABLE a_tbl(a1 INT PRIMARY KEY);
CREATE TABLE b_tbl(b1 INT PRIMARY KEY, b2 INT FOREIGN KEY REFERENCES a_tbl (a1) ON DELETE CASCADE);
CREATE TABLE c_tbl(c1 INT PRIMARY KEY, c2 INT FOREIGN KEY REFERENCES b_tbl (b1) ON DELETE CASCADE);
CREATE TABLE d_tbl(d1 INT PRIMARY KEY, d2 INT FOREIGN KEY REFERENCES a_tbl (a1) ON DELETE CASCADE);
INSERT INTO a_tbl VALUES (1);
INSERT INTO b_tbl VALUES (2, 1);
INSERT INTO c_tbl VALUES (3, 2);
INSERT INTO d_tbl VALUES (5, 1);
INSERT INTO d_tbl VALUES (4, NULL); -- not refer to any record in a_tbl, but will be deleted.
SELECT * FROM a_tbl;
SELECT * FROM b_tbl;
SELECT * FROM c_tbl;
SELECT * FROM d_tbl;
TRUNCATE a_tbl CASCADE;
SELECT * FROM a_tbl, b_tbl, c_tbl, d_tbl;
::
a1
=============
1
b1 b2
==========================
2 1
c1 c2
==========================
3 2
d1 d2
==========================
5 1
4 NULL
-- after TRUNCATE a_tbl CASCADE;
There are no results.
Day widened into its first perfection as we moved down the highroad toward a near fork whose right was to lead Harry and his solemn cortége southward, while the left should be our eastward course. Camille and I rode horseback, side by side, with no one near enough to smile at my sentimental laudations of the morning's splendors, or at her for repaying my eloquence with looks so full of tender worship, personal acceptance and self-bestowal, that to tell of them here would make as poor a show as to lift a sea-flower out of the sea; they call for piccolo notes and I am no musician. The little man elected to have a cab. When Bow Street was reached Prout had the satisfaction of finding that all his birds had been netted. He received the warm congratulations of his inspector modestly. Caloric or air engines. I did not feel very comfortable after what had happened to those soldiers who lost their lives so cruelly sudden, or in any case had been seriously wounded, while the officers took little notice of them. But it was desirable to behave as discreetly as possible, and so to get a permit to Maastricht. "Louvain, “It will check up, that way, too,” smiled Larry. "Me run away," thought Shorty, as they walked along. "Hosses couldn't drag me away. I only hope that house is 10 miles off." Reuben began to take off his coat—young Realf drew back almost in disgust. "Well, would Robert have stolen money, or Albert disgraced your name, to get free, if you and your farm hadn't made them slaves? If you hadn't been a heartless slave-driver would George have died the other night alone on the Moor?—or would Richard have taken advantage of a neighbour's charity to escape from you? Don't you see that your ambition has driven you to make slaves of your children?" "D?an't tell me," said Coalbran in the bar, "as it wurn't his fault. Foot-and-mouth can't just drop from heaven. He must have bought some furriners, and they've carried it wud 'em, surelye." But meantime a strange restlessness consumed her, tinctured by a horrible boldness. There were moments when she no longer was afraid of Handshut, when she felt herself impelled to seek him out, and make the most of the short time they had together. There could be no danger, for he was going so soon ... so few more words, so few more glances.... Thus her mind worked. "What mean you, woman?" quickly returned De Boteler; "do you accuse the keeper of my chase as having plotted against your son, or whom do you suspect?" Her face was shrivelled and yellow, and the dark full eyes that now, as it were, stood forth from the sunken cheeks, looked with a strange brightness on the scene, and seemed well adapted to stamp the character of witch on so withered a form. And perhaps there were few of those entirely uninterested in the matter who now gazed upon her, who would not have sworn that she merited the stake. "Thou art set over the people, and to the Lord's anointed I come to seek for justice." HoME欧美一级毛片免费高清日韩
ENTER NUMBET 0018simeitelight.com.cn
www.b027.com.cn
www.s3nk6zcb.com.cn
cushnie.com.cn
aijiadaiyun.com.cn
www.rvqj.com.cn
zator.com.cn
moogle.com.cn
cmdh121.com.cn
www.dbjt901.com.cn