MySQL数据删除 #

DELETE FROM table_name
WHERE condition;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    status TINYINT DEFAULT 1
);

INSERT INTO users (name, age, email, status) VALUES
    ('John', 25, 'john@example.com', 1),
    ('Jane', 30, 'jane@example.com', 1),
    ('Bob', 28, 'bob@example.com', 0),
    ('Alice', 22, 'alice@example.com', 0);

-- 根据ID删除
DELETE FROM users WHERE id = 1;

-- 根据名称删除
DELETE FROM users WHERE name = 'John';

-- 多条件删除
DELETE FROM users WHERE age > 30 AND status = 0;

-- 危险操作！删除所有数据
DELETE FROM users;

-- 建议先查询确认
SELECT COUNT(*) FROM users;

-- 使用IN条件
DELETE FROM users WHERE id IN (1, 2, 3);

-- 使用BETWEEN条件
DELETE FROM users WHERE age BETWEEN 20 AND 25;

-- 使用LIKE条件
DELETE FROM users WHERE email LIKE '%@old.com';

-- 使用IS NULL条件
DELETE FROM users WHERE email IS NULL;

-- 删除满足子查询条件的记录
DELETE FROM users 
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE status = 'cancelled'
);

-- 使用NOT IN
DELETE FROM users 
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

-- 使用EXISTS
DELETE FROM users u
WHERE EXISTS (
    SELECT 1 FROM logs l 
    WHERE l.user_id = u.id AND l.action = 'spam'
);

-- 删除没有订单的用户
DELETE u FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 根据关联表条件删除
DELETE u FROM users u
JOIN user_status s ON u.id = s.user_id
WHERE s.status = 'banned';

-- 只删除前10条
DELETE FROM logs 
WHERE level = 'debug' 
LIMIT 10;

-- 删除最早的100条日志
DELETE FROM logs 
ORDER BY created_at ASC 
LIMIT 100;

-- 删除最后10条
DELETE FROM logs 
ORDER BY created_at DESC 
LIMIT 10;

TRUNCATE TABLE table_name;

-- 示例
TRUNCATE TABLE logs;

-- 使用DELETE：需要条件删除或回滚
DELETE FROM users WHERE status = 0;

-- 使用TRUNCATE：清空整个表
TRUNCATE TABLE logs;
TRUNCATE TABLE temp_data;

-- 查看安全模式状态
SHOW VARIABLES LIKE 'sql_safe_updates';

-- 开启安全模式
SET sql_safe_updates = 1;

-- 关闭安全模式
SET sql_safe_updates = 0;

-- 安全模式下，必须使用键列作为条件
-- 错误示例：
DELETE FROM users;
-- ERROR 1175 (HY000): You are using safe update mode

-- 正确示例：
DELETE FROM users WHERE id = 1;

-- 删除前先查询
SELECT * FROM users WHERE status = 0;

-- 确认数量
SELECT COUNT(*) FROM users WHERE status = 0;

-- 使用事务
START TRANSACTION;
DELETE FROM users WHERE status = 0;
-- 确认结果
SELECT COUNT(*) FROM users WHERE status = 0;
-- 确认无误后提交
COMMIT;
-- 如有问题回滚
ROLLBACK;

-- 不真正删除，而是标记为已删除
-- 添加删除标记字段
ALTER TABLE users ADD COLUMN is_deleted TINYINT DEFAULT 0;
ALTER TABLE users ADD COLUMN deleted_at DATETIME;

-- 软删除操作
UPDATE users 
SET is_deleted = 1, deleted_at = NOW() 
WHERE id = 1;

-- 查询时排除已删除记录
SELECT * FROM users WHERE is_deleted = 0;

-- 恢复删除
UPDATE users 
SET is_deleted = 0, deleted_at = NULL 
WHERE id = 1;

-- 定期清理超过30天的软删除数据
DELETE FROM users 
WHERE is_deleted = 1 
AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 创建带级联删除的表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) 
    REFERENCES departments(id)
    ON DELETE CASCADE
);

-- 删除部门时，自动删除该部门的所有员工
DELETE FROM departments WHERE id = 1;

-- 先删除从表记录
DELETE FROM employees WHERE department_id = 1;

-- 再删除主表记录
DELETE FROM departments WHERE id = 1;

-- 确保WHERE条件使用索引
-- 查看执行计划
EXPLAIN DELETE FROM users WHERE email = 'john@example.com';

-- 如果email有索引，删除更快

-- 大批量删除分批进行
-- 每次删除1000条
DELETE FROM logs 
WHERE created_at < '2023-01-01' 
LIMIT 1000;

-- 循环执行直到影响行数为0

-- 大批量删除时临时禁用索引
ALTER TABLE logs DISABLE KEYS;

DELETE FROM logs WHERE created_at < '2023-01-01';

ALTER TABLE logs ENABLE KEYS;

-- 错误信息
DELETE FROM departments WHERE id = 1;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

-- 解决方案：先删除关联记录
DELETE FROM employees WHERE department_id = 1;
DELETE FROM departments WHERE id = 1;

-- 或使用级联删除

-- 错误信息
DELETE FROM users;
-- ERROR 1142 (42000): DELETE command denied to user 'user'@'localhost' for table 'users'

-- 解决方案：使用root用户授权
GRANT DELETE ON mydb.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

-- 错误信息
DELETE FROM non_existent_table;
-- ERROR 1146 (42S02): Table 'mydb.non_existent_table' doesn't exist

-- 解决方案：检查表名
SHOW TABLES LIKE 'users';

START TRANSACTION;
DELETE FROM users WHERE id = 1;
-- 发现错误，回滚
ROLLBACK;

# 从备份恢复单表
mysql -u root -p mydb < users_backup.sql

# 从全量备份恢复
mysql -u root -p < full_backup.sql

# 查看binlog
mysqlbinlog /var/lib/mysql/mysql-bin.000001

# 恢复到指定时间点
mysqlbinlog --stop-datetime="2024-01-15 10:00:00" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

DELETE FROM users WHERE status = 0;
-- Query OK, 3 rows affected

-- 在应用程序中获取
-- PHP: $affected = $pdo->rowCount();
// Java: int affected = statement.executeUpdate();

-- 删除前统计
SELECT COUNT(*) AS before_count FROM users WHERE status = 0;

-- 执行删除
DELETE FROM users WHERE status = 0;

-- 删除后统计
SELECT COUNT(*) AS after_count FROM users WHERE status = 0;