DELETE 和 TRUNCATE TABLE 两种语句的使用方法、区别及注意事项。DELETE 语句是标准 SQL 语法，用于从表中移除指定记录。DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count];
WHERE where_condition：（可选）指定删除条件，仅删除满足条件的记录。ORDER BY：（可选）指定删除顺序。LIMIT row_count：（可选）限制单次删除的行数。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 用于快速清空整张表的数据。TRUNCATE TABLE tbl_name;
-- 创建测试表CREATE TABLE sbtest1 (id INT PRIMARY KEY,v1 INT,v2 INT,v3 INT);-- 快速清空表中所有数据TRUNCATE TABLE sbtest1;
-- 创建 RANGE 分区表CREATE 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);-- 快速清理 2024 年 1 月的数据ALTER TABLE sbtest2TRUNCATE PARTITION p202401;
文档反馈