MySQL中DML和DQL常见错误及解决方法

以下内容从DML(数据操作语言)DQL(数据查询语言)两个维度出发,罗列常见的错误场景,结合代码示例ASCII 图解详细说明,帮助你快速发现问题并给出对应的解决方法。


目录

  1. 概述
  2. DML 常见错误及解决方法

    1. 忘记 WHERE 导致全表更新/删除
    2. 主键冲突(Duplicate Key)
    3. 插入数据列与列类型不匹配
    4. 事务与锁:死锁及长事务
    5. 外键约束(FOREIGN KEY)错误
    6. NULL 与默认值误处理
  3. DQL 常见错误及解决方法

    1. 缺少 JOIN 条件导致笛卡尔积
    2. 索引失效:在索引列上使用函数
    3. GROUP BY 使用不当导致非预期结果
    4. LIMIT 与 ORDER BY 搭配错误
    5. 子查询返回多行导致错误
    6. 数据类型不匹配导致无法查询
  4. 小结

1. 概述

在日常开发中,DML(INSERT、UPDATE、DELETE)与 DQL(SELECT)是使用最频繁的两类 SQL 操作。然而,一点小小的疏忽往往会导致数据损坏性能问题,甚至产生死锁全表扫描。本文将聚焦以下几类常见错误:

  • 对写操作(DML)而言:容易遗漏 WHERE、主键冲突、插入类型或列匹配错误、事务与锁冲突、外键约束问题、NULL/默认值误用等。
  • 对查询操作(DQL)而言:常见缺少 JOIN 条件导致笛卡尔积、索引失效、GROUP BY 使用不当、LIMIT 与 ORDER BY 混用错误、子查询返回多行、数据类型不匹配等。

对于每种错误,先展示导致问题的“错误示例”,再给出“修正方案”,并用ASCII 图解辅助理解。希望通过这些实战案例,帮助你在编写或维护 SQL 时“心中有数”,及时发现并改正问题。


2. DML 常见错误及解决方法

2.1 忘记 WHERE 导致全表更新/删除

错误示例:UPDATE 忘记 WHERE

-- 错误:原本只想更新 user_id=5 的邮箱,结果忘记加 WHERE,整个表全部更新!
UPDATE users
SET email = 'new_email@example.com';

-- 会话 A 执行后:
SELECT user_id, username, email FROM users LIMIT 5;
+---------+----------+------------------------+
| user_id | username | email                  |
+---------+----------+------------------------+
|       1 | alice    | new_email@example.com  |
|       2 | bob      | new_email@example.com  |
|       3 | carol    | new_email@example.com  |
|       4 | dave     | new_email@example.com  |
|       5 | eve      | new_email@example.com  |
+---------+----------+------------------------+
  • 原因UPDATE 语句中漏写了 WHERE user_id = 5,导致对 users 表中的所有行生效。
  • 后果:大量数据被误改,难以回滚(若无备份或 binlog)。

修正方案

  1. 始终在 UPDATE/DELETE 中加 WHERE 过滤,并在执行前先 SELECT 确认受影响行数是否符合预期:

    -- 一步验证:先查询
    SELECT * FROM users WHERE user_id = 5;
    
    -- 再更新
    UPDATE users
    SET email = 'new_email@example.com'
    WHERE user_id = 5;
  2. 开启 MySQL 安全模式(在客户端或会话级别)阻止无 WHERE 的 DML 操作:

    SET sql_safe_updates = 1;
    -- 此时,若不带 WHERE 或 LIMIT 的 UPDATE/DELETE 会报错:
    -- ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
    • 注意:只适用于交互式客户端,生产脚本中要手动检查。
  3. 使用事务做“审查”:将更新放在事务中,先 SELECT,确认再 COMMIT,否则 ROLLBACK:

    START TRANSACTION;
      -- 先预览即将更新的行
      SELECT * FROM users WHERE user_id = 5 FOR UPDATE;
    
      UPDATE users
      SET email = 'new_email@example.com'
      WHERE user_id = 5;
    
    -- 确认后
    COMMIT;
    -- 如发现错误可 ROLLBACK

2.2 主键冲突(Duplicate Key)

错误示例:INSERT 导致 Duplicate Key

-- 建表并插入一条数据
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name       VARCHAR(50)
) ENGINE=InnoDB;

INSERT INTO products (product_id, name) VALUES (1, '电脑');

-- 若再次插入 product_id = 1,将报错
INSERT INTO products (product_id, name) VALUES (1, '手机');
-- 错误:
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  • 原因product_id=1 已存在,再次插入时与主键冲突。

修正方案

  1. 使用 INSERT … ON DUPLICATE KEY UPDATE

    -- 若插入时冲突,则转为 UPDATE 操作
    INSERT INTO products (product_id, name)
    VALUES (1, '手机')
    ON DUPLICATE KEY UPDATE
      name = VALUES(name);
    -- 此时已将产品名称更新为“手机”。
  2. 先 SELECT 再 INSERT(“先校验”):

    SELECT 1 FROM products WHERE product_id = 1;
    -- 若存在则 UPDATE,否则 INSERT
    -- 应用代码示例(伪代码):
    -- if (exists) { UPDATE products SET name='手机' WHERE product_id=1; }
    -- else         { INSERT INTO products (...) VALUES (...); }
  3. 使用 REPLACE INTO (MySQL 特有):

    -- 如果 PK 冲突,先删除旧行再插入一行
    REPLACE INTO products (product_id, name) VALUES (1, '手机');
    • 注意REPLACE 会先做 DELETE,再做 INSERT,会触发删除与插入触发器,且如果表有自增主键,会重置计数。

2.3 插入数据列与列类型不匹配

错误示例:数据类型不匹配

CREATE TABLE orders (
  order_id   INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NOT NULL,
  order_date DATE NOT NULL,
  total_amt  DECIMAL(10,2)
) ENGINE=InnoDB;

-- 错误:将字符串赋给 DATE 列
INSERT INTO orders (user_id, order_date, total_amt)
VALUES (5, '2023-13-01', 100.00);
-- 错误:
-- ERROR 1292 (22007): Incorrect date value: '2023-13-01' for column 'order_date' at row 1
  • 原因'2023-13-01' 不是合法日期(月份 13 无效)。

错误示例:列数不对

-- 注意:orders 有 4 列(order_id 自增可省略),但插入时给了 4 个值
INSERT INTO orders VALUES (NULL, 5, '2023-10-01', 150.00, 'extra');
-- 错误:
-- ERROR 1136 (21S01): Column count doesn't match value count at row 1
  • 原因INSERT INTO table VALUES(...) 时,值的个数必须与列的个数完全一样。

修正方案

  1. 严格按照列定义插入

    -- 显式指定列,与值个数对应
    INSERT INTO orders (user_id, order_date, total_amt)
    VALUES (5, '2023-10-01', 150.00);
  2. 确保数据格式正确

    • 对于 DATEDATETIME,传入合法日期字符串;
    • 对于 DECIMAL(10,2),保证小数点后不超过两位;
  3. 编程时使用参数化预编译,让 JDBC/ORM 驱动自动做类型校验与转化:

    // Java 示例,使用 PreparedStatement
    String sql = "INSERT INTO orders (user_id, order_date, total_amt) VALUES (?, ?, ?)";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setInt(1, 5);
    ps.setDate(2, java.sql.Date.valueOf("2023-10-01"));
    ps.setBigDecimal(3, new BigDecimal("150.00"));
    ps.executeUpdate();

2.4 事务与锁:死锁及长事务

示例场景:简单死锁

逻辑:要在两个事务中分别对同两条记录交叉更新,容易产生死锁。
CREATE TABLE accounts (
  acc_id  INT PRIMARY KEY,
  balance DECIMAL(10,2)
) ENGINE=InnoDB;

INSERT INTO accounts VALUES (1, 1000.00), (2, 1000.00);
  • 会话 A

    START TRANSACTION;
      -- 锁定 acc_id=1
      SELECT * FROM accounts WHERE acc_id = 1 FOR UPDATE;
      -- 此时仅锁定 acc_id=1
    
      -- 模拟业务延迟
      -- 例如:调用远程接口、复杂计算等
      -- SLEEP(5);
    
      -- 再锁定 acc_id=2
      UPDATE accounts SET balance = balance - 100 WHERE acc_id = 2;
    COMMIT;
  • 会话 B(与 A 几乎同时启动):

    START TRANSACTION;
      -- 锁定 acc_id=2
      SELECT * FROM accounts WHERE acc_id = 2 FOR UPDATE;
      -- 再锁定 acc_id=1
      UPDATE accounts SET balance = balance - 200 WHERE acc_id = 1;
    COMMIT;

此时 A 锁定了记录 1,B 锁定了记录 2;接着 A 等待锁 2,B 等待锁 1,形成死锁。

   会话 A                会话 B
   -------               -------
   SELECT ... FOR UPDATE → 锁定 acc_id=1
                            SELECT ... FOR UPDATE → 锁定 acc_id=2
   UPDATE accounts SET ... acc_id=2 ← 等待会话 B 释放 acc_id=2
   (死锁)                                 UPDATE ... acc_id=1 ← 等待会话 A 释放 acc_id=1

ASCII 图解:死锁环路

+-----------------+         +-----------------+
|     会话 A      |         |     会话 B      |
|-----------------|         |-----------------|
| 锁定 acc_id = 1 |         | 锁定 acc_id = 2 |
| 等待 acc_id = 2 ←─────────┤                 |
|                 |         | 等待 acc_id = 1 | ←─────────┘
+-----------------+         +-----------------+

解决方法

  1. 统一加锁顺序

    • 保证所有事务对多行加锁时,按相同的顺序进行,例如都先锁 acc_id=1,再锁 acc_id=2
    -- 会话 A 和 B 都先 SELECT ... FOR UPDATE acc_id=1,再 SELECT ... FOR UPDATE acc_id=2
  2. 缩短事务持锁时间

    • 将耗时操作(如外部 API 调用、耗时计算)移到事务外,只在真正要更新时才开启事务并快速提交。
    -- 改进示例:先读取并计算
    SELECT balance FROM accounts WHERE acc_id=1;      -- 只读
    
    -- 业务逻辑耗时操作
    -- 计算等
    
    -- 进入事务,只做必要更新
    START TRANSACTION;
      SELECT balance FROM accounts WHERE acc_id = 1 FOR UPDATE;
      UPDATE accounts SET balance = balance - 100 WHERE acc_id = 1;
      UPDATE accounts SET balance = balance + 100 WHERE acc_id = 2;
    COMMIT;
    • 如此持锁时间极短,能大幅降低死锁概率。
  3. 事务隔离级别调整

    • 对于写多读少场景,可考虑将隔离级别降为 READ COMMITTED,减少临键锁争用;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    • 但要评估业务对幻读的容忍度。
  4. 应用层重试死锁事务

    • 在代码层捕获 MySQL 错误 1213: Deadlock found when trying to get lock; try restarting transaction,进行指数退避后重试:
    max_try = 3
    for attempt in 1..max_try:
      START TRANSACTION
      try:
        -- 执行业务 DML
        COMMIT
        break
      except DeadlockError:
        ROLLBACK
        if attempt == max_try:
          throw
        sleep(random small delay)
      except OtherError:
        ROLLBACK
        throw

2.5 外键约束(FOREIGN KEY)错误

错误示例:插入/删除时违反外键约束

CREATE TABLE parent (
  id INT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE child (
  id        INT PRIMARY KEY,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES parent(id)
) ENGINE=InnoDB;

-- parent 中没有 id=10,以下插入会报外键错误
INSERT INTO child (id, parent_id) VALUES (1, 10);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

-- 如果 parent 中已有 id=5,但在 parent 删除时,还存在 child 引用同 id
DELETE FROM parent WHERE id = 5;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

修正方案

  1. 插入子表前,确保父表已存在对应记录

    INSERT INTO parent (id) VALUES (10);
    INSERT INTO child  (id, parent_id) VALUES (1, 10);
  2. 删除父表前,先删除或更新子表引用

    DELETE FROM child WHERE parent_id = 5;
    DELETE FROM parent WHERE id = 5;
  3. 使用 ON DELETE CASCADEON UPDATE CASCADE 简化级联操作:

    CREATE TABLE child (
      id        INT PRIMARY KEY,
      parent_id INT,
      FOREIGN KEY (parent_id) REFERENCES parent(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    ) ENGINE=InnoDB;
    -- 当删除 parent.id=5 时,所有 child.parent_id=5 的行会自动删除
  4. 临时关闭外键检查(慎用),批量导入或批量清理时:

    SET FOREIGN_KEY_CHECKS = 0;
    -- 执行大批量插入/删除操作
    SET FOREIGN_KEY_CHECKS = 1;
    • 关闭后可能会导致参照完整性破坏,需要保证在打开检查后数据依然合法,或手动校验。

2.6 NULL 与默认值误处理

错误示例:插入时忽略 NOT NULL 列

CREATE TABLE employees (
  emp_id   INT AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(50) NOT NULL,
  dept_id  INT NOT NULL,
  salary   DECIMAL(10,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB;

-- 错误:未指定 name、dept_id,导致插入失败
INSERT INTO employees VALUES (NULL, NULL, NULL, NULL);
-- ERROR 1048 (23000): Column 'name' cannot be null
  • 原因namedept_id 都定义为 NOT NULL,却尝试插入 NULL

错误示例:默认值误用

CREATE TABLE logs (
  log_id    INT AUTO_INCREMENT PRIMARY KEY,
  message   VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 如果希望插入当前时间,但显式插入了 NULL,导致插入失败
INSERT INTO logs (message, created_at) VALUES ('测试日志', NULL);
-- ERROR 1048 (23000): Column 'created_at' cannot be null

修正方案

  1. 严格匹配 NOT NULL 列

    INSERT INTO employees (name, dept_id, salary)
    VALUES ('张三', 10, 5000.00);
  2. 利用默认值

    -- 不指定 created_at,则自动使用 CURRENT_TIMESTAMP
    INSERT INTO logs (message) VALUES ('测试日志');
  3. 根据需求允许 NULL 或设置默认值

    ALTER TABLE employees 
      MODIFY dept_id INT NULL;  -- 若允许 NULL,需明确业务含义

3. DQL 常见错误及解决方法

3.1 缺少 JOIN 条件导致笛卡尔积

错误示例:缺失 ON 条件

CREATE TABLE users (
  user_id   INT PRIMARY KEY,
  username  VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE orders (
  order_id  INT PRIMARY KEY,
  user_id   INT,
  total_amt DECIMAL(10,2)
) ENGINE=InnoDB;

INSERT INTO users VALUES (1, 'alice'), (2, 'bob');
INSERT INTO orders VALUES (10, 1, 100.00), (11, 2, 200.00);

-- 忘记指定 ON:user_id=users.user_id,导致笛卡尔积
SELECT u.user_id, u.username, o.order_id, o.total_amt
FROM users u, orders o;
  • 执行结果

    +---------+----------+----------+-----------+
    | user_id | username | order_id | total_amt |
    +---------+----------+----------+-----------+
    |       1 | alice    |       10 |    100.00 |
    |       1 | alice    |       11 |    200.00 |
    |       2 | bob      |       10 |    100.00 |
    |       2 | bob      |       11 |    200.00 |
    +---------+----------+----------+-----------+

    这显然不是我们想要的“每个订单对应其用户名”,而是 2×2 = 4 条“笛卡尔积”结果。

ASCII 图解:笛卡尔积

users:   2 行   ×  orders: 2 行 = 4 行结果
+------+  ×  +------+
| 1    |     | 10   |
| 2    |     | 11   |
+------+     +------+

组合 → (1,10),(1,11),(2,10),(2,11)

修正方案

  1. 显式写 JOIN 并指定 ON 条件

    -- 正确:指定连接条件
    SELECT u.user_id, u.username, o.order_id, o.total_amt
    FROM users u
    JOIN orders o ON u.user_id = o.user_id;

    结果:

    +---------+----------+----------+-----------+
    | user_id | username | order_id | total_amt |
    +---------+----------+----------+-----------+
    |       1 | alice    |       10 |    100.00 |
    |       2 | bob      |       11 |    200.00 |
    +---------+----------+----------+-----------+
  2. 使用 WHERE 语法指定连接条件(不推荐旧式写法,但可修复):

    SELECT u.user_id, u.username, o.order_id, o.total_amt
    FROM users u, orders o
    WHERE u.user_id = o.user_id;
  3. 在复杂查询中注意所有 JOIN 都要有合适的 ON,避免多表之间的隐式 CROSS JOIN。

3.2 索引失效:在索引列上使用函数

错误示例:对索引列使用函数

CREATE TABLE users (
  user_id    INT PRIMARY KEY,
  username   VARCHAR(50),
  created_at DATETIME,
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- 想查询 2023 年 10 月份注册的用户
SELECT * FROM users
WHERE YEAR(created_at) = 2023 AND MONTH(created_at) = 10;
  • 问题:虽然 created_at 有索引,但因为在 WHERE 中对它做了 YEAR()MONTH() 函数运算,MySQL 无法利用索引,只能全表扫描

ASCII 图解:索引失效示意

-- idx_created_at 索引原理示意:
B+Tree 叶子节点:
[2023-09-30 23:59:59] → row1
[2023-10-01 00:00:00] → row2
[2023-10-15 12:34:56] → row3
[2023-11-01 00:00:00] → row4
-- 如果执行 WHERE YEAR(created_at)=2023,MySQL 必须对每行计算 YEAR(...),无法直接使用索引范围

修正方案

  1. 改为范围查询,让索引可用:

    SELECT * FROM users
    WHERE created_at >= '2023-10-01 00:00:00'
      AND created_at <  '2023-11-01 00:00:00';
    • 这样 MySQL 可以在索引 idx_created_at 上直接定位范围并回表。
  2. 为表达式建虚拟列并索引(MySQL 5.7+ 支持):

    -- 创建一个虚拟列存储 YEAR(created_at)
    ALTER TABLE users
    ADD COLUMN created_year INT GENERATED ALWAYS AS (YEAR(created_at)) VIRTUAL,
    ADD INDEX idx_created_year (created_year);
    
    -- 然后可以直接查询
    SELECT * FROM users WHERE created_year = 2023;
    • 但要额外存储或计算开销,需权衡是否值得。

3.3 GROUP BY 使用不当导致非预期结果

错误示例:非聚合列未在 GROUP BY

CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  user_id    INT,
  total_amt  DECIMAL(10,2),
  order_date DATE,
  INDEX idx_user_date(user_id, order_date)
) ENGINE=InnoDB;

-- 统计每个用户的订单数量与最后一次下单时间
SELECT user_id, COUNT(*) AS cnt, order_date
FROM orders
GROUP BY user_id;
  • 问题order_date 既不是聚合函数,也未出现在 GROUP BY 中。MySQL 在非严格模式下会执行,但 order_date 值不确定(随机取某一行的值),容易导致错误理解结果。

ASCII 图解:非确定性来源

orders:
+----------+---------+-----------+
| order_id | user_id | order_date|
+----------+---------+-----------+
|       10 |       1 | 2023-10-01|
|       11 |       1 | 2023-10-05|
+----------+---------+-----------+

-- 当 GROUP BY user_id 时:
用户 1 有两行,MySQL 给予 order_date 可能是 2023-10-01 或 2023-10-05,结果不确定。

修正方案

  1. 使 order\_date 出现在 GROUP BY 或使用聚合

    -- 如果想要“最后一次下单时间”,需要 MAX(order_date)
    SELECT 
      user_id,
      COUNT(*) AS cnt,
      MAX(order_date) AS last_date
    FROM orders
    GROUP BY user_id;
  2. 开启严格 SQL 模式,强制检查:

    -- 在 my.cnf 或会话中开启ONLY_FULL_GROUP_BY
    SET sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,...';
    -- 再执行上述错误示例会报错,提示“order_date”不是 GROUP BY 或聚合列

3.4 LIMIT 与 ORDER BY 搭配错误

错误示例:未指定 ORDER BY 的 LIMIT

CREATE TABLE messages (
  msg_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  content VARCHAR(255),
  created_at DATETIME,
  INDEX idx_user_date(user_id, created_at)
) ENGINE=InnoDB;

-- 希望获取最近 5 条消息,但未加 ORDER BY:
SELECT * FROM messages WHERE user_id = 5 LIMIT 5;
  • 问题LIMIT 5 并不保证“最近 5 条”,而是任意 5 条,因为没有排序条件。

修正方案

  1. 加上 ORDER BY created\_at DESC

    SELECT * FROM messages
    WHERE user_id = 5
    ORDER BY created_at DESC
    LIMIT 5;
    • 这样才能确保结果按照时间倒序取前 5 条。
  2. 分页查询时,必须带 ORDER BY,否则下一页的数据顺序会不可预期。

3.5 子查询返回多行导致错误

错误示例:标量子查询返回多行

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  dept_id INT,
  salary DECIMAL(10,2)
) ENGINE=InnoDB;

INSERT INTO employees VALUES
(1, 10, 5000.00),
(2, 10, 6000.00),
(3, 20, 5500.00);

-- 错误:希望“获取部门 10 的薪资最高值”,但子查询返回多行
SELECT *
FROM employees
WHERE salary = (
  SELECT salary
  FROM employees
  WHERE dept_id = 10
);
-- 如果部门 10 有多个人,并列最高,子查询仍然返回多行
-- 错误:
-- ERROR 1242 (21000): Subquery returns more than 1 row

修正方案

  1. 将子查询改为聚合 或加 LIMIT:

    -- 方法一:使用 MAX 聚合
    SELECT *
    FROM employees
    WHERE salary = (
      SELECT MAX(salary)
      FROM employees
      WHERE dept_id = 10
    );
    
    -- 方法二:加 LIMIT(不推荐,若并列最高会遗漏其他人)
    SELECT *
    FROM employees
    WHERE salary = (
      SELECT salary
      FROM employees
      WHERE dept_id = 10
      ORDER BY salary DESC
      LIMIT 1
    );
  2. 关联子查询改为 JOIN

    -- 用 JOIN 获得所有并列最高的员工
    SELECT e.*
    FROM employees e
    JOIN (
      SELECT dept_id, MAX(salary) AS max_sal
      FROM employees
      WHERE dept_id = 10
      GROUP BY dept_id
    ) tmp ON e.dept_id = tmp.dept_id AND e.salary = tmp.max_sal;

3.6 数据类型不匹配导致无法查询

错误示例:字符与数字类型混用

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  sku        VARCHAR(20)
) ENGINE=InnoDB;

INSERT INTO products VALUES (1, '1001'), (2, '1002');

-- 错误:尝试用整数比较 SKU,导致类型转换或索引失效
SELECT * FROM products WHERE sku = 1001;
-- 可能返回结果,也可能因为严格模式下类型不匹配报错

-- 当 SKU 字段上有索引时,"sku = 1001" 会隐式转换成 "sku = '1001'" 才能匹配
-- 但若字符串前后有空格或不同字符集,匹配会失败。

修正方案

  1. 保持类型一致

    SELECT * FROM products WHERE sku = '1001';
  2. 对于数字比较,保证字段类型为数字

    ALTER TABLE products MODIFY sku INT;
    -- 这样直接用 sku = 1001 不会有类型隐式转换
  3. 对于日期等类型,也要确保格式一致

    -- 错误:日期字符串格式不对
    SELECT * FROM orders WHERE order_date = '2023-10-5';
    -- MySQL 对 '2023-10-5' 能隐式转换为 '2023-10-05',但不建议依赖
    -- 正确:
    SELECT * FROM orders WHERE order_date = '2023-10-05';

4. 小结

  • DML 常见错误

    1. 忘记 WHERE:导致全表更新/删除;可通过 sql_safe_updates 或事务加审核来避免。
    2. 主键冲突:常触发 Duplicate entry;可以用 ON DUPLICATE KEY UPDATE 或先校验再插入。
    3. 类型/列数不匹配:要严格对应表结构;可用参数化接口自动校验。
    4. 事务与锁:死锁、高长事务会影响性能;需缩短事务、统一加锁顺序、捕获重试。
    5. 外键约束:插入/删除时必须满足父子表约束,可用 ON DELETE CASCADE 简化,或临时关闭检查。
    6. NULL 与默认值:插入时要注意 NOT NULL 列,合理设置默认值。
  • DQL 常见错误

    1. 缺少 JOIN 条件:导致笛卡尔积;必须显式用 ONWHERE 指定连接条件。
    2. 索引失效:在索引列上使用函数(如 YEAR(col))会迫使全表扫描;应改为范围查询或用虚拟列。
    3. GROUP BY 不当:非聚合列未出现在 GROUP BY 中或未使用聚合函数;必须改写为 MAX(x)MIN(x) 等。
    4. LIMIT 与 ORDER BY 搭配错误:若缺少 ORDER BYLIMIT 无法保证返回顺序,导致分页或排序结果不一致。
    5. 子查询返回多行:标量子查询若返回多行会报错,需要改为聚合或加 LIMIT,或改写为 JOIN。
    6. 数据类型不匹配:如用整数去匹配字符列、日期格式不正确等,导致索引失效或无法匹配。

通过本文的代码示例ASCII 图解,你应能快速定位并修复 DML 与 DQL 中常见的各种错误场景。在实际开发中,编写语句前先做“干跑”(先用 SELECT 确认影响行数),审慎设计索引与数据类型,并在关键环节使用事务与安全模式,就能大幅减少误操作与性能隐患。

最后修改于:2025年06月07日 16:56

评论已关闭

推荐阅读

DDPG 模型解析,附Pytorch完整代码
2024年11月24日
DQN 模型解析,附Pytorch完整代码
2024年11月24日
AIGC实战——Transformer模型
2024年12月01日
Socket TCP 和 UDP 编程基础(Python)
2024年11月30日
python , tcp , udp
如何使用 ChatGPT 进行学术润色?你需要这些指令
2024年12月01日
AI
最新 Python 调用 OpenAi 详细教程实现问答、图像合成、图像理解、语音合成、语音识别(详细教程)
2024年11月24日
ChatGPT 和 DALL·E 2 配合生成故事绘本
2024年12月01日
omegaconf,一个超强的 Python 库!
2024年11月24日
【视觉AIGC识别】误差特征、人脸伪造检测、其他类型假图检测
2024年12月01日
[超级详细]如何在深度学习训练模型过程中使用 GPU 加速
2024年11月29日
Python 物理引擎pymunk最完整教程
2024年11月27日
MediaPipe 人体姿态与手指关键点检测教程
2024年11月27日
深入了解 Taipy:Python 打造 Web 应用的全面教程
2024年11月26日
基于Transformer的时间序列预测模型
2024年11月25日
Python在金融大数据分析中的AI应用(股价分析、量化交易)实战
2024年11月25日
AIGC Gradio系列学习教程之Components
2024年12月01日
Python3 `asyncio` — 异步 I/O,事件循环和并发工具
2024年11月30日
llama-factory SFT系列教程:大模型在自定义数据集 LoRA 训练与部署
2024年12月01日
Python 多线程和多进程用法
2024年11月24日
Python socket详解,全网最全教程
2024年11月27日