如何从User Agent字符串提取版本号并按最低版本筛选数据库条目

固定位置SUBSTRING截取的容错性极低，只要App名称长度、UA前缀结构发生变化，提取结果就会出错，推荐两类更可靠的方案：

• 正则匹配提取（优先选择，适配性最高）
  绝大多数主流数据库都支持正则提取函数，直接匹配App Name/后到第一个空格前的数字+点号组合即可，不同数据库写法参考：
  • MySQL：REGEXP_SUBSTR(user_agent, 'App Name/([0-9.]+)', 1, 1, 'c', 1)
  • PostgreSQL：SUBSTRING(user_agent FROM 'App Name/([0-9.]+)')
  • SQL Server：SUBSTRING(user_agent, PATINDEX('%App Name/[0-9.]%', user_agent) + 9, PATINDEX('%[ (]%', SUBSTRING(user_agent, PATINDEX('%App Name/[0-9.]%', user_agent) + 9, 100)) - 1)
• 字符位置动态截取（适配不支持正则的老旧数据库）
  先定位/和后续第一个空格的位置，再做截取，以MySQL为例：

  TRIM(SUBSTRING(
      user_agent,
      LOCATE('/', user_agent) + 1,
      LOCATE(' ', user_agent) - LOCATE('/', user_agent) - 1
  ))
  

版本号为点分三段结构，直接字符串比较会出现4.14.3 < 4.2.0的错误结果，通用解决方案有两种：

• 拆分版本号分段比较（兼容性最好，无版本段长度限制）
  把版本号拆为主版本、次版本、修订号三个数值字段后再做判断，示例SQL：

  SELECT * FROM (
      SELECT 
          *,
          CAST(SUBSTRING_INDEX(app_version, '.', 1) AS UNSIGNED) AS major,
          CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(app_version, '.', 2), '.', -1) AS UNSIGNED) AS minor,
          CAST(SUBSTRING_INDEX(app_version, '.', -1) AS UNSIGNED) AS patch
      FROM (
          SELECT 
              *,
              REGEXP_SUBSTR(user_agent, 'App Name/([0-9.]+)', 1, 1, 'c', 1) AS app_version
          FROM your_table
      ) t1
  ) t2
  -- 筛选≥4.0.0的版本
  WHERE (major > 4) 
     OR (major = 4 AND minor > 0) 
     OR (major = 4 AND minor = 0 AND patch >= 0)
  -- 版本号从高到低排序
  ORDER BY major DESC, minor DESC, patch DESC
  

• 转数值后比较（写法更简洁，适合版本段长度≤3位的场景）
  把三个版本段分别补零到固定长度后拼接为整数，直接比较整数大小即可，筛选≥4.0.0的示例条件：

  WHERE CAST(
      CONCAT(
          LPAD(SUBSTRING_INDEX(app_version, '.', 1), 3, '0'),
          LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(app_version, '.', 2), '.', -1), 3, '0'),
          LPAD(SUBSTRING_INDEX(app_version, '.', -1), 3, '0')
      ) AS UNSIGNED
  ) >= 4000000
  

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