DELETE and TRUNCATE TABLE.DELETE statement is standard SQL syntax used to remove specified records from a table.DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count];
WHERE where_condition: (Optional) Specifies the deletion condition, deleting only records that meet the condition.ORDER BY: (Optional) Specifies the deletion order.LIMIT row_count: (Optional) Limits the number of rows to delete in a single operation.CREATE TABLE sbtest1 (id INT PRIMARY KEY, v1 INT, v2 INT, v3 INT);
DELETE FROM sbtest1 WHERE v1 = 100;
DELETE FROM sbtest1 WHERE v2 < 1000 LIMIT 1000;
DELETE FROM sbtest1 ORDER BY id LIMIT 500;
DELETE FROM sbtest1;
TRUNCATE TABLE is used to quickly remove all data from a table.TRUNCATE TABLE tbl_name;
-- Create a test table.CREATE TABLE sbtest1 (id INT PRIMARY KEY,v1 INT,v2 INT,v3 INT);-- Quickly clear all data in the table.TRUNCATE TABLE sbtest1;
-- Create a RANGE partitioned tableCREATE TABLE sbtest2 (id INT,create_time DATE,v1 INT,PRIMARY KEY (id, create_time))PARTITION BY RANGE (TO_DAYS(create_time)) (PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),PARTITION pmax VALUES LESS THAN MAXVALUE);-- Quickly clean up data for January 2024.ALTER TABLE sbtest2TRUNCATE PARTITION p202401;
フィードバック