MySQL查询浏览器版本

Question

我的 MySQL 数据库中有一个存储登录数据的表，我存储了用户代理 header 信息，例如:

{"userAgent":"Mozilla\/5.0 (Windows NT 6.3; WOW64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/54.0.2840.71 Safari\/537.36"}

我正在尝试查询人们访问该站点所使用的浏览器和版本。这是我到目前为止的查询:

SELECT
Browser,
COUNT(Browser) AS Count
FROM
(
    SELECT
    CASE
        WHEN userAgent LIKE '%Firefox%' THEN 'Firefox'
        WHEN userAgent LIKE '%Chrome%' THEN 'Chrome'
        WHEN userAgent LIKE '%MSIE %' THEN 'IE'
        WHEN userAgent LIKE '%MSIE+%' THEN 'IE'
        ELSE 'Unknown'
    END AS Browser
    FROM user_log
)
AS Browsers
GROUP BY Browser

我的问题是如何将浏览器版本添加到此查询？

Answer 1

我设法弄明白了，我希望这对以后的人有用:

SELECT
Browser,
Version,
COUNT(Browser) AS Count
FROM
(
    SELECT
    CASE
        WHEN userAgent LIKE '%Firefox%' THEN 'Firefox'
        WHEN userAgent LIKE '%Chrome%' THEN 'Chrome'
        WHEN userAgent LIKE '%MSIE %' THEN 'IE'
        ELSE 'Unknown'
    END AS Browser,
    CASE
        WHEN userAgent LIKE '%Firefox%' THEN SUBSTRING(userAgent, LOCATE('Firefox', userAgent) + 9, POSITION('.' IN SUBSTRING(userAgent, LOCATE('Firefox', userAgent) + 9)) + 1)
        WHEN userAgent LIKE '%Chrome%' THEN SUBSTRING(userAgent, LOCATE('Chrome', userAgent) + 8, POSITION('.' IN SUBSTRING(userAgent, LOCATE('Chrome', userAgent) + 8)) + 1)
        WHEN userAgent LIKE '%MSIE %' THEN SUBSTRING(userAgent, LOCATE('MSIE ', userAgent) + 5, POSITION('.' IN SUBSTRING(userAgent, LOCATE('MSIE ', userAgent) + 5)) + 1)
    ELSE 'Unknown'
    END AS Version
    FROM user_log
)
AS Browsers
GROUP BY Browser, Version