MySQL - DELETE 查询

MySQL - DELETE 语句

本教程将解释如何使用 `DELETE` 语句从 MySQL 表中删除记录。我们将涵盖删除单行和多行数据、`WHERE` 子句的关键重要性，以及诸如使用事务（transactions）等安全删除实践。您还将学习从客户端应用程序执行删除操作的现代、安全方法。

理解 MySQL DELETE 语句

DELETE 语句是一种强大的 DML（数据操纵语言，Data Manipulation Language）命令，用于从表中删除一行或多行。因为它会永久性地删除数据，所以必须极其谨慎地使用。

语法

DELETE FROM table_name
WHERE [condition];

WHERE 子句指定应删除哪些行。如果您省略 WHERE 子句，表中的所有行都将被删除！ 这很少是预期的操作，可能导致灾难性的数据丢失。

安全删除的最佳实践

• 始终使用 WHERE 子句： 除非您打算清空整个表，否则绝不要在没有 WHERE 子句的情况下运行 DELETE 语句。
• 使用 SELECT 测试： 在运行 DELETE 查询之前，先使用相同的 WHERE 子句运行 SELECT 查询，以验证您是否正在定位正确的行。例如：SELECT * FROM table_name WHERE condition;
• 使用事务： 对于关键操作，请将您的 DELETE 语句封装在事务中。这允许您预览更改并在不正确时回滚。START TRANSACTION; DELETE FROM ...; SELECT * FROM ...; -- 检查结果。如果错误，输入 ROLLBACK;。如果正确，输入 COMMIT;
• 拥有备份： 在执行主要数据修改任务之前，请务必确保您有最新的、经过测试的数据库备份。

先决条件：示例表设置

让我们为示例创建一个并填充 CUSTOMERS 表。

CREATE TABLE CUSTOMERS(
    ID INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(25),
    SALARY DECIMAL(18, 2),
    PRIMARY KEY(ID)
);

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

从表中删除数据

示例：删除单行

让我们删除 ID = 1 的客户。

DELETE FROM CUSTOMERS WHERE ID = 1;

输出

Query OK, 1 row affected (0.01 sec)

验证

一个简单的 SELECT 查询即可确认该行已被删除。

SELECT * FROM CUSTOMERS;

该表现在以 ID 2 开始。

示例：删除多行

您可以通过提供匹配多条记录的 WHERE 子句来删除多行。让我们删除所有工资低于 2000.00 的客户。

DELETE FROM CUSTOMERS WHERE SALARY < 2000.00;

输出

Query OK, 1 row affected (0.01 sec) -- Assuming Ramesh was already deleted

或者，您可以使用 IN 运算符删除具有特定 ID 的行。

DELETE FROM CUSTOMERS WHERE ID IN (6, 7);

示例：删除所有行

要从表中删除所有记录，请省略 WHERE 子句。请极其谨慎地使用此操作。

DELETE FROM CUSTOMERS;

清空整个表的一种更高效的方法是使用 TRUNCATE TABLE。它更快，因为它不记录单行删除操作，并且会重置任何 AUTO_INCREMENT 计数器。然而，TRUNCATE 是一个 DDL（数据定义语言）命令，不能在事务中轻易回滚。

TRUNCATE TABLE CUSTOMERS;

从客户端程序执行 DELETE 语句

从应用程序执行 DELETE 查询时，尤其是在 WHERE 子句依赖用户输入的情况下，使用预处理语句至关重要。这可以防止 SQL 注入攻击。

Node.js（使用 mysql2/promise）

require('dotenv').config();
const mysql = require('mysql2/promise');

async function deleteCustomer(customerId) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || 'password',
      database: process.env.DB_NAME || 'your_database'
    });

    // 使用带有占位符 (?) 的预处理语句
    const [result] = await connection.execute('DELETE FROM CUSTOMERS WHERE ID = ?', [customerId]);

    if (result.affectedRows > 0) {
      console.log(`Successfully deleted customer with ID: ${customerId}`);
    } else {
      console.log(`No customer found with ID: ${customerId}`);
    }

  } catch (error) {
    console.error(`Failed to delete customer: ${error.message}`);
  } finally {
    if (connection) await connection.end();
  }
}

// Example usage:
deleteCustomer(5);

Python（使用 mysql-connector-python）

import mysql.connector
import os

def delete_customer(customer_id):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', 'password'),
            database=os.getenv('DB_NAME', 'your_database')
        )
        cursor = connection.cursor()

        # 此连接器的预处理语句风格
        delete_query = "DELETE FROM CUSTOMERS WHERE ID = %s"
        cursor.execute(delete_query, (customer_id,))
        connection.commit()

        if cursor.rowcount > 0:
            print(f"Successfully deleted customer with ID: {customer_id}")
        else:
            print(f"No customer found with ID: {customer_id}")

    except mysql.connector.Error as err:
        print(f"Database error: {err}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

# Example usage:
delete_customer(4)

PHP（使用 PDO）

<?php
function deleteCustomer(int $customerId): void
{
    $host = $_ENV['DB_HOST'] ?? 'localhost';
    $db   = $_ENV['DB_NAME'] ?? 'your_database';
    $user = $_ENV['DB_USER'] ?? 'root';
    $pass = $_ENV['DB_PASSWORD'] ?? 'password';
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    try {
        $pdo = new PDO($dsn, $user, $pass, $options);

        // 使用带有命名占位符的预处理语句
        $stmt = $pdo->prepare('DELETE FROM CUSTOMERS WHERE ID = :id');
        $stmt->execute(['id' => $customerId]);

        if ($stmt->rowCount() > 0) {
            echo "Successfully deleted customer with ID: $customerId\n";
        } else {
            echo "No customer found with ID: $customerId\n";
        }

    } catch (\PDOException $e) {
        // 在生产环境中，应记录此错误而不是直接输出
        error_log($e->getMessage());
        // 您可能希望重新抛出或以不同方式处理它
        throw new \RuntimeException("Database operation failed.");
    }
}

// Example usage:
deleteCustomer(3);