DELETE语句中能使用表的别名？

202040621

在数据库管理和开发中，MySQL 是一个不可或缺的工具，凭借其高效、可靠和功能丰富的特点，广泛应用于各种规模的应用程序中。MySQL 的不断演进和版本更新也为用户带来了诸多新特性和改进。然而，对于使用 MySQL 5.* 版本的开发者来说，可能会遇到一个不太显眼但重要的限制：在 DELETE 语句的表名后不能使用别名。这一限制在某些情况下会给开发工作带来不便，特别是当开发者习惯于在 SELECT 和 UPDATE 语句中频繁使用别名时，这种不一致的行为容易引发困惑和错误。

? 幸运的是，随着 MySQL 8.0 的发布，这一限制得到了修复。在最新版本中，开发者可以在 DELETE 语句中使用表别名，使得编写和维护 SQL 语句更加方便和直观。本文将详细探讨 MySQL 5.* 版本中 DELETE 语句别名使用的限制，以及 MySQL 8.0 版本中对此问题的修复。通过对比不同版本的行为，读者将能更好地理解这一变化的影响，并在实际开发中充分利用 MySQL 8.0 的改进特性。

开发经历：

最近开发过程中遇到一个MySQL delete语句表别名的问题。

在 MySQL 中，DELETE 语句确实不允许在表名后面使用别名。也就是说，不能给表起一个别名然后使用该别名来引用这个表进行删除操作。这与 SELECT 语句或 UPDATE 语句中的行为不同。

一、背景

发现问题：

现象是

delete删除语句中的表名 取了别名导致语法错误。

DELETE FROM aaaa as n  WHERE n.id= 2;

错误信息：

Last error message

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as n WHERE n.id= 2' at line 1

二、分析

排查问题：

查看当前MySQL版本，5.7.17

# select version();

# 5.7.17

删除语句

5.7 单表删除格式 （原因）

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

8.0单表语句

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

DELETE 语句从 tblname 中删除行并返回已删除的行数。要检查已删除的行数，请调用第 14.15 节“信息函数”中所述的 ROWCOUNT() 函数。

可选 WHERE 子句中的条件标识要删除的行。如果没有 WHERE 子句，则所有行都将被删除。 where_condition 是一个表达式，对于要删除的每一行，其计算结果为 true

如果指定了 ORDER BY 子句，则按指定的顺序删除行。 LIMIT 子句对可以删除的行数设置限制。这些子句适用于单表删除，但不适用于多表删除。

5.7 和 8.0 多表语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

对于第一个多表语法，仅删除 FROM 子句之前列出的表中的匹配行。

对于第二个多表语法，仅删除 FROM 子句（在 USING 子句之前）中列出的表中的匹配行。

为什么对删除语句这么模糊呢？因为大部分的业务是不允许物理删除的，都是伪删除，绝大部分都是增加字段标识 如is_deleted，都是update更新操作。所以当有业务突然要直接删除数据库数据的时候还是挺意外的。pass这一环节，继续下面的验证环节~

按照语法下了个demo，

DELETE  a, t 
FROM aaaa as a, aaaa_test AS t     
WHERE a.id = t.id and a.id = 11;

DELETE  
FROM a, t 
USING aaaa as a, aaaa_test AS t     
WHERE a.id = t.id and a.id = 11;

多表 DELETE 中的表别名应仅在语句的 table_references 部分中声明。在其他地方，允许别名引用，但不允许别名声明。

正确示例:

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

错误示例:

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;

DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;

mysql官方bugfix [MySQL 8.0 Release Notes] 中：Changes in MySQL 8.0.16 (2019-04-25, General Availability)

并且在 8.0.16 开始，单表删除已经支持使用别名了。

• For consistency with the SQL standard and other RDBMS, table aliases are now supported in single-table as well as multi-table DELETE statements. (Bug #27455809)

为了与 SQL 标准和其他 RDBMS 保持一致，单表和多表 DELETE 语句现在支持表别名

扩展一下

前面的示例使用 INNER JOIN，但多表 DELETE 语句可以使用 SELECT 语句中允许的其他类型的联接，例如 LEFT JOIN。例如，要删除 t1 中存在且与 t2 中不匹配的行，请使用 LEFT JOIN：

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

三、小结

• MySQL 5.*版本 使用单表删除语句时不支持使用表别名，多表删除支持使用表别名。
• MySQL 8.0.16 官网bugfix后，开始支持单表多表都可以使用表别名。

结尾：

? 通过本文对 MySQL DELETE 语句中表别名使用限制的探讨，我们可以清晰地看到 MySQL 在不同版本中的设计和实现差异。MySQL 5.* 版本中对 DELETE 语句使用表别名的限制，虽然在一定程度上保证了操作的明确性和安全性，但也限制了开发者在编写复杂 SQL 语句时的灵活性。这一限制在开发过程中常常引发困惑，尤其是在处理多表操作或复杂查询时。

? 随着 MySQL 8.0 的发布，删除语句中表别名使用的限制得到了修复，使得开发者可以在 DELETE 语句中灵活使用别名。这一改进不仅提升了 SQL 语句的可读性和可维护性，也为复杂数据操作提供了更大的自由度。通过允许在 DELETE 语句中使用表别名，MySQL 8.0 为开发者提供了更加一致和直观的 SQL 语法体验。

? 总的来说，MySQL 8.0 中对 DELETE 语句别名使用的修复，标志着 MySQL 在用户体验和功能灵活性方面的重要进步。希望本文能够为读者提供有价值的洞见和指导，帮助他们在数据库管理和开发中更好地利用 MySQL 的强大功能。