:meta-keywords: truncate statement
:meta-description: You can delete all records in the specified table by using the TRUNCATE statement.
********
TRUNCATE
********
**TRUNCATE** 문은 명시된 테이블의 모든 레코드들을 삭제한다.
**WHERE** 조건이 없는 **DELETE FROM** *[schema_name.]table_name* 문을 사용하는 것에 비해 일반적으로 다음의 이점을 지닌다.
* 인덱스와 제약사항 사전 처리, 레코드들 일괄 삭제로 성능면에서 훨씬 뛰어나다.
* 레코드들을 개별 삭제하는 것이 아니기 때문에 각 레코드에 대한 VACUUM 비용이 없다.
* 로그양이 훨씬 적기 때문에 HA 복제, 복구 그리고 롤백(rollback) 등에서 더 좋은 성능을 보인다.
.. note::
* **TRUNCATE** 문을 사용해서 삭제하면 **DELETE** 트리거가 활성화되지 않는다.
* **TRUNCATE** 문은 해당 테이블의 **AUTO INCREMENT** 칼럼을 초기화하여, 다시 데이터가 입력되면 **AUTO INCREMENT** 칼럼의 초기값부터 생성된다.
* **TRUNCATE** 문을 수행하려면 해당 테이블에 **ALTER**, **INDEX**, **DELETE** 권한이 필요하다. 권한을 부여하는 방법은 :ref:`granting-authorization` 를 참고한다.
* 테이블이 **DONT_REUSE_OID** 옵션(:ref:`dont-reuse-oid`)으로 생성된 경우 다른 테이블의 컬럼이 해당 테이블을 참조할 수 있다. 이렇게 외부 참조가 있을 경우 일괄 삭제가 불가능하며 내부적으로 DELETE FROM 연산을 통해 TRUNCATE가 수행된다.
::
TRUNCATE [ TABLE ] [schema_name.]table_name [ CASCADE ]
* *schema_name*: 스키마 이름을 지정한다. 생략하면 현재 세션의 스키마 이름을 사용한다.
* *table_name* : 삭제할 데이터가 포함되어 있는 테이블의 이름을 지정한다.
* **CASCADE** : 외래 키 (Foreign Key) 참조 관계에 있는 모든 테이블들의 데이터를 삭제한다. 대상 테이블에 **PRIMARY KEY** 제약 조건이 정의되어 있고, 이 **PRIMARY KEY** 를 하나 이상의 외래 키가 참조하고 있어야 한다. 이 때 외래 키의 **ON DELETE** 동작은 **CASCADE** 로 정의되어 있어야 한다. 외래 키 참조가 있는 경우 **CASCADE** 옵션을 사용하지 않으면, **TRUNCATE** 는 실패한다. 또한, 여러 테이블이 외래 키 참조로 연결되어 있고, 하나라도 **ON DELETE** 동작이 **CASCADE** 가 아닌 경우 역시 실패한다. 외래 키 제약 조건에 대한 자세한 내용은 :ref:`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
다음은 **CASCADE** 옵션을 사용하는 예이다. 일부 레코드의 외래 키 컬럼이 NULL로 어떠한 레코드를 가르키지 않더라도 CASCADE로 전파된 TRUNCATE는 모든 레코드를 제거한다.
.. 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 cortge 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 coatyoung 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 0018b049.com.cn
www.chanbi.com.cn
sjzhe.com.cn
shjjhs.com.cn
www.jiazhilan.com.cn
nbgy003.com.cn
www.sylton.com.cn
www.ynlw.net.cn
www.gnij.com.cn
xiaogang6167.com.cn