MySQL分组序号查询WorkBench正常但NodeJS中seq恒为0如何解决

原因分析 #

• 核心原因是自定义会话变量未初始化：MySQL的@开头的自定义变量是会话级别的，仅在当前数据库连接生命周期内有效。你在MySQL WorkBench中运行查询前，大概率已经执行过SET @seq = 0, @saveUserId = NULL;这类初始化语句，变量残留了正确的初始值；而NodeJS、phpMyAdmin每次执行查询都会创建新的数据库会话，两个变量默认值为NULL，IF(@saveUserId = user2, @seq + 1, 0)中NULL与任何值比较结果都为非真，永远走返回0的分支，所以seq全为0。
• 次要原因是变量执行顺序无保证：MySQL优化器可能在多表JOIN场景下打乱变量比较、赋值的执行顺序，即使初始化了变量也可能出现结果异常，且MySQL 8.0+版本已经明确不推荐使用自定义变量实现分组序号功能，优化器可能直接优化掉变量操作导致结果不符合预期。
• 多余变量干扰：子查询中定义的@user2 := userId完全冗余，额外增加了变量求值的不确定性。

解决方法 #

方案1：兼容MySQL 5.x版本，补全变量初始化 #

通过交叉连接的方式内置变量初始化逻辑，不需要单独执行SET语句，保证任何新会话执行都能得到正确结果，同时去掉冗余变量，固定执行顺序：

SELECT 
    users.userId,
    users.firstName,
    users.middleName,
    users.lastName,
    users.address,
    users.address2,
    users.city,
    users.stateProvince,
    users.postalCode,
    users.country,
    users.email,
    users.mobilePhone,
    users.altPhone,
    users.ataId,
    users.pitaId,
    users.nssaId,
    users.nscaId,
    users.active,
    @seq := IF(@saveUserId = permissionsPlus.userId, @seq + 1, 0) as seq,
    permissionsPlus.entityId,
    permissionsPlus.entityName,
    permissionsPlus.entityTypeId,
    permissionsPlus.entityTypeName,
    permissionsPlus.permissionSettings,
    permissionsPlus.roleId,
    permissionsPlus.roleName,
    permissionsPlus.userPermissionId,
    @saveUserId := permissionsPlus.userId as user2
FROM sosclays.users
LEFT JOIN   
 (SELECT userPermissions.*, 
  entityTypes.name AS entityTypeName, 
  IFNULL(sanctioningBodies.name, IFNULL(associations.name, IFNULL(clubs.name, IFNULL(shoots.name, 'App')))) AS entityName, 
  roles.name AS roleName
  FROM userPermissions 
  LEFT OUTER JOIN sanctioningBodies ON(entityTypeId = 1 AND userPermissions.entityId = sanctioningBodies.sanctioningBodyId) 
  LEFT OUTER JOIN associations ON(entityTypeId = 2 AND userPermissions.entityId = associations.associationId) 
  LEFT OUTER JOIN clubs ON(entityTypeId = 3 AND userPermissions.entityId = clubs.clubId) 
  LEFT OUTER JOIN shoots ON(entityTypeId = 4 AND userPermissions.entityId = shoots.shootId) 
  LEFT OUTER JOIN entityTypes ON(userPermissions.entityTypeId = entityTypes.entityTypeId) 
  LEFT OUTER JOIN roles ON(userPermissions.roleId = roles.roleId)
  ORDER BY userPermissions.userId ASC) permissionsPlus on(users.userId = permissionsPlus.userId)
-- 内置变量初始化逻辑，每次执行都会重置变量
CROSS JOIN (SELECT @seq := 0, @saveUserId := NULL) AS init
ORDER BY users.userId ASC, seq ASC

方案2：MySQL 8.0+推荐，使用窗口函数替代自定义变量 #

使用官方支持的ROW_NUMBER()窗口函数实现分组序号，完全规避会话变量的各类问题，代码更简洁稳定，序号从0开始只需要将窗口函数结果减1即可：

SELECT 
    users.userId,
    users.firstName,
    users.middleName,
    users.lastName,
    users.address,
    users.address2,
    users.city,
    users.stateProvince,
    users.postalCode,
    users.country,
    users.email,
    users.mobilePhone,
    users.altPhone,
    users.ataId,
    users.pitaId,
    users.nssaId,
    users.nscaId,
    users.active,
    -- 按用户ID分组，组内按权限ID排序，序号从0开始所以减1
    ROW_NUMBER() OVER (PARTITION BY users.userId ORDER BY permissionsPlus.userPermissionId) - 1 as seq,
    permissionsPlus.entityId,
    permissionsPlus.entityName,
    permissionsPlus.entityTypeId,
    permissionsPlus.entityTypeName,
    permissionsPlus.permissionSettings,
    permissionsPlus.roleId,
    permissionsPlus.roleName,
    permissionsPlus.userPermissionId,
    permissionsPlus.userId as user2
FROM sosclays.users
LEFT JOIN   
 (SELECT userPermissions.*, 
  entityTypes.name AS entityTypeName, 
  IFNULL(sanctioningBodies.name, IFNULL(associations.name, IFNULL(clubs.name, IFNULL(shoots.name, 'App')))) AS entityName, 
  roles.name AS roleName
  FROM userPermissions 
  LEFT OUTER JOIN sanctioningBodies ON(entityTypeId = 1 AND userPermissions.entityId = sanctioningBodies.sanctioningBodyId) 
  LEFT OUTER JOIN associations ON(entityTypeId = 2 AND userPermissions.entityId = associations.associationId) 
  LEFT OUTER JOIN clubs ON(entityTypeId = 3 AND userPermissions.entityId = clubs.clubId) 
  LEFT OUTER JOIN shoots ON(entityTypeId = 4 AND userPermissions.entityId = shoots.shootId) 
  LEFT OUTER JOIN entityTypes ON(userPermissions.entityTypeId = entityTypes.entityTypeId) 
  LEFT OUTER JOIN roles ON(userPermissions.roleId = roles.roleId)
  ORDER BY userPermissions.userId ASC) permissionsPlus on(users.userId = permissionsPlus.userId)
ORDER BY users.userId ASC, seq ASC

内容的提问来源于stack exchange，提问作者Steve Geggie