DELETE FROM table_name
[WHERE condition]
[ORDER BY column_name]
[LIMIT row_count];

-- 刪除特定ID的記錄
DELETE FROM employees 
WHERE employee_id = 205;

-- 刪除過期日志
DELETE FROM system_logs 
WHERE created_at < '2022-01-01';

-- 清空表數(shù)據(jù)（保留結(jié)構(gòu)）
DELETE FROM temporary_data;

-- 等價但更快的TRUNCATE操作
TRUNCATE TABLE temporary_data;  -- 不可回滾且重置自增值

-- 刪除30天無活動的用戶
DELETE FROM users 
WHERE user_id IN (
    SELECT user_id 
    FROM user_activity 
    GROUP BY user_id
    HAVING MAX(last_activity) < DATE_SUB(NOW(), INTERVAL 30 DAY)
);

-- 使用JOIN刪除（MySQL特有語法）
DELETE u FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL 
AND u.register_date < '2020-01-01';

-- 大表分批刪除（避免鎖表）
DELETE FROM large_table
WHERE condition = true
LIMIT 1000;  -- 每次刪除1000條

-- 刪除最老的100條日志
DELETE FROM access_log
ORDER BY access_time ASC
LIMIT 100;

START TRANSACTION;
DELETE FROM sensitive_data WHERE id = 100;
-- 驗證后再提交
SELECT * FROM backup_log WHERE operation = 'delete';
COMMIT;

-- 創(chuàng)建專用刪除賬號
CREATE USER 'data_cleaner'@'localhost' IDENTIFIED BY 'secure_pwd';
GRANT DELETE ON db.specific_table TO 'data_cleaner'@'localhost';

-- 分批刪除示例
WHILE (SELECT COUNT(*) FROM big_table WHERE condition) > 0 DO
    DELETE FROM big_table WHERE condition LIMIT 10000;
    COMMIT;
    SLEEP 1;  -- 減輕服務(wù)器負(fù)載
END WHILE;

-- 低效（全表掃描）：
DELETE FROM logs WHERE content LIKE '%error%';

-- 高效（使用索引列）：
DELETE FROM logs WHERE created_at < '2023-01-01' AND log_level = 'ERROR';

mysql -u app_user -p

mysql> USE inventory;
Database changed

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> -- 先備份要刪除的數(shù)據(jù)
mysql> CREATE TABLE deleted_products_archive AS
    -> SELECT * FROM products WHERE discontinued = 1;
Query OK, 42 rows affected (0.15 sec)

mysql> -- 執(zhí)行刪除
mysql> DELETE FROM products WHERE discontinued = 1;
Query OK, 42 rows affected (0.03 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

<?php
$conn = new mysqli("localhost", "user", "password", "crm");

// 開啟事務(wù)
$conn->begin_transaction();

try {
    // 先記錄刪除操作
    $stmt = $conn->prepare("
        INSERT INTO audit_log (action, table_name, record_id, user_id)
        VALUES ('delete', 'customers', ?, ?)
    ");
    $customer_id = 1005;
    $user_id = $_SESSION['user_id'];
    $stmt->bind_param("ii", $customer_id, $user_id);
    $stmt->execute();
    
    // 執(zhí)行刪除
    $delete_stmt = $conn->prepare("
        DELETE FROM customers 
        WHERE customer_id = ? 
        AND status = 'inactive'
    ");
    $delete_stmt->bind_param("i", $customer_id);
    $delete_stmt->execute();
    
    if ($delete_stmt->affected_rows === 0) {
        throw new Exception("未找到匹配的客戶記錄或狀態(tài)不符");
    }
    
    $conn->commit();
    echo "客戶記錄已安全刪除";
} catch (Exception $e) {
    $conn->rollback();
    echo "刪除失敗: " . $e->getMessage();
}

$conn->close();
?>

-- 方案1：級聯(lián)刪除
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

-- 方案2：先刪除子表記錄
DELETE FROM order_items WHERE order_id = 1001;
DELETE FROM orders WHERE order_id = 1001;

-- 比DELETE更快但不可回滾的方式
TRUNCATE TABLE session_data;

-- 重建表（InnoDB）
ALTER TABLE huge_data ENGINE=InnoDB;