mysql - 仅在 MySQL 中从保存的用户代理中检测移动设备

Question

假设我们将用户代理保存在这样一个表的 MySQL 字段中

表:访问

id (int)

useragent (varchar 255)

假设我们保存了 100k 条记录，我们必须找出其中有多少是移动设备，多少是计算机，MySQL 中是否有查询该信息的方法？我知道如何在 PHP 中做到这一点并且可以很容易地做到这一点，但在这种情况下，我们需要通过查询从存储的数据中弄清楚这一点，而不是使用任何编程语言来解析返回的数据。纯 MySQL

例如在 PHP 中你可以使用类似的东西

function detectMobile() 
{
 
$mobile_browser = '0';
 
if (preg_match('/(up.browser|up.link|mmp|symbian|smartphone|midp|wap|phone|android)/i', strtolower($_SERVER['HTTP_USER_AGENT']))) {
    $mobile_browser++;
}
 
if ((strpos(strtolower($_SERVER['HTTP_ACCEPT']),'application/vnd.wap.xhtml+xml') > 0) or ((isset($_SERVER['HTTP_X_WAP_PROFILE']) or isset($_SERVER['HTTP_PROFILE'])))) {
    $mobile_browser++;
}    
 
$mobile_ua = strtolower(substr($_SERVER['HTTP_USER_AGENT'], 0, 4));
$mobile_agents = array(
    'w3c ','acs-','alav','alca','amoi','audi','avan','benq','bird','blac',
    'blaz','brew','cell','cldc','cmd-','dang','doco','eric','hipt','inno',
    'ipaq','java','jigs','kddi','keji','leno','lg-c','lg-d','lg-g','lge-',
    'maui','maxo','midp','mits','mmef','mobi','mot-','moto','mwbp','nec-',
    'newt','noki','oper','palm','pana','pant','phil','play','port','prox',
    'qwap','sage','sams','sany','sch-','sec-','send','seri','sgh-','shar',
    'sie-','siem','smal','smar','sony','sph-','symb','t-mo','teli','tim-',
    'tosh','tsm-','upg1','upsi','vk-v','voda','wap-','wapa','wapi','wapp',
    'wapr','webc','winw','winw','xda ','xda-');
 
if (in_array($mobile_ua,$mobile_agents)) {
    $mobile_browser++;
}
 
if (strpos(strtolower($_SERVER['ALL_HTTP']),'OperaMini') > 0) {
    $mobile_browser++;
}
 
if (strpos(strtolower($_SERVER['HTTP_USER_AGENT']),'windows') > 0) {
    $mobile_browser = 0;
}

if(strpos(strtolower($_SERVER['HTTP_USER_AGENT']),'ipad') > 0)
{
    $mobile_browser++;
}



 
if ($mobile_browser > 0) {
   return true;
}
else {
   return false;
}   
 
}

Answer 1

我使用下面的 sql 来计算平板电脑、手机和其他设备的数量。使用 Like 查询比使用正则表达式更优化。

SELECT 
COUNT( * ) total, 
COUNT( IF( useragent LIKE '%tablet%' or useragent LIKE '%ipad%', 1, NULL ) ) AS tablets,
COUNT( IF( useragent LIKE '%mobile%' AND useragent NOT LIKE '%tablet%' AND useragent NOT LIKE '%ipad%', 1, NULL ) ) AS mobile,
COUNT( IF( useragent  NOT LIKE '%tablet%' AND useragent  NOT LIKE '%ipad%' AND useragent  NOT LIKE '%mobile%', 1, NULL ) ) AS other
FROM visit