WordPress插件SQL查询使用LIKE丢失无匹配数据问题求助

解决方案 #

你遇到的是LEFT JOIN加过滤条件的常见错误：把关联表的过滤条件放在WHERE子句会将LEFT JOIN转为隐式的INNER JOIN，过滤掉匹配不到关联表的行，只要把meta_key的过滤条件移到LEFT JOIN的ON子句中即可解决，同时优化分组逻辑避免非法分组问题，修改后的查询如下：

SELECT 
  SUBSTRING(l.license_key,1,15) as license_key_prefix,
  l.user_id,
  p.post_author,
  MAX(pm.meta_value) as mac_address
FROM W551je5v_lmfwc_licenses l
LEFT JOIN W551je5v_users u ON l.user_id = u.ID 
LEFT JOIN W551je5v_posts p ON l.user_id = p.post_author
LEFT JOIN W551je5v_postmeta pm 
  ON pm.post_id = p.ID 
  AND pm.meta_key = 'mac' -- mac过滤条件放在JOIN条件中
WHERE l.user_id IS NOT NULL 
  AND u.ID IS NOT NULL 
GROUP BY 
  l.license_key,
  l.user_id,
  p.post_author
ORDER BY l.user_id;

改动说明 #

• 给表加了别名简化语句可读性
• meta_key = 'mac' 放到postmeta的关联条件中，匹配不到的行mac_address自动为NULL，不会丢失许可证记录
• 用MAX()聚合meta_value，不需要GROUP_CONCAT也能避免同一个许可证对应多条post记录时出现重复行，如果你需要保留同一个许可证对应的多个mac地址，可以替换为GROUP_CONCAT(DISTINCT pm.meta_value SEPARATOR ',')
• 显式声明所有GROUP BY的字段，适配MySQL默认开启的ONLY_FULL_GROUP_BY模式，避免查询报错

可选补充：会话级修改GROUP_CONCAT长度 #

如果后续需要用到GROUP_CONCAT且不想申请全局权限，可以在执行查询前先执行一次会话级配置，仅对当前数据库连接生效，不需要超级管理员权限：

SET SESSION group_concat_max_len = 1000000;

在WordPress中可以通过$wpdb->query( 'SET SESSION group_concat_max_len = 1000000;' )调用执行即可。

内容的提问来源于stack exchange，提问作者Karim Crozes