MariaDB禁用CTE和*时按PilotID分组更新PlaneID序号的方案咨询

A、语法报错及禁用CTE、*写法的原因 #

• 你原本的SQL语法报错是因为UPDATE ... FROM ...是SQL Server专属语法，MariaDB不支持该写法，同时窗口函数的别名赋值、方括号包裹标识符的写法也是SQL Server的语法，不符合MariaDB的语法规范。
• 禁止使用*写法是行业通用的SQL编码规范：SELECT *会查询所有不需要的字段，增加不必要的IO开销，且表结构发生变更时容易引发后续业务逻辑异常，要求显式列出所需字段更易维护、性能更可控。
• 禁止使用CTE通常有两个原因：一是你使用的MariaDB版本低于10.2.2，该版本才首次支持CTE语法，为了兼容低版本要求禁用；二是部分业务规范要求避免CTE减少复杂查询的性能不可控风险。

B、符合要求的实现方案 #

无需CTE、无需*写法，直接使用MariaDB支持的多表UPDATE关联子查询即可实现需求，如果你使用的MariaDB版本≥10.2支持窗口函数，使用以下SQL：

UPDATE flight f
INNER JOIN (
  SELECT 
    flight_id,
    PilotID,
    ROW_NUMBER() OVER (PARTITION BY PilotID ORDER BY flight_id) AS NewPlaneID
  FROM flight
) AS t ON f.flight_id = t.flight_id
SET f.PlaneID = t.NewPlaneID;

如果你使用的MariaDB版本过低不支持窗口函数，可以使用用户变量实现：

UPDATE flight f
INNER JOIN (
  SELECT 
    flight_id,
    PilotID,
    @seq := IF(@current_pilot = PilotID, @seq + 1, 1) AS NewPlaneID,
    @current_pilot := PilotID
  FROM flight
  CROSS JOIN (SELECT @seq := 1, @current_pilot := NULL) AS init
  ORDER BY PilotID, flight_id
) AS t ON f.flight_id = t.flight_id
SET f.PlaneID = t.NewPlaneID;

两种方案执行后都会得到你预期的结果：按PilotID分组，每组内按主键flight_id升序生成从1开始的连续序号，写入PlaneID字段。

内容的提问来源于stack exchange，提问作者Dr. No