使用 mysql、php 和 pdo 选择不区分大小写 [英] Select case insensitive using mysql, php and pdo
问题描述
我正在尝试从 mysql 表中选择一些数据,但我无法将 Where 比较设为不区分大小写,我尝试使用 LOWER:
I'm trying to select some data from a mysql table but I cannot get the Where comparison to be case insensitive, I tried using LOWER:
$wildcard = $_GET['q'];
$query = "SELECT id, name, departamento FROM gestionDoc_cargos WHERE (LOWER(name) LIKE '%' LOWER(:wildcard) '%' OR LOWER(departamento) LIKE '%' LOWER(:wildcard) '%')";
try{
$result = DB::getInstance()->prepare($query);
$result->bindParam(':wildcard', $wildcard, PDO::PARAM_STR);
$result->execute();
$result = $result->fetchAll(PDO::FETCH_ASSOC);
}catch(PDOException $e){
die($e->getMessage());
}
print_r($result);
echo json_encode(array_values($result));
但我收到以下错误:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOWER('C') '%' OR LOWER(departamento) LIKE '%' LOWER('C') '%')' at line 1
如果我从查询中删除 LOWER,我会得到一个区分大小写的选择.
and if I remove the LOWER from the query I get a case sensitive select.
推荐答案
这个
...snip... ) LIKE '%' LOWER(:wildcard) '%' OR ...snip
不正确.您有一个字符串 ('%'
) 后跟一个函数调用 (LOWER()
) 后跟另一个字符串,它们只是坐在那里 - 没有连接逻辑,没有串联,等等等等.
is incorrect. You've got a string ('%'
) followed by a function call (LOWER()
) followed by another string, and they're just sitting there - no connecting logic, no concatenation, blah blah blah .
应该是
... LIKE CONCAT('%', LOWER(:wildcard), '%') OR ...
默认情况下,mysql 比较不区分大小写,除非您强制进行 binary
比较,或者您在 db/table 上使用区分大小写的排序规则.
And by default, mysql comparisons ARE case insensitive, unless you force a binary
comparison, or you're using a case sensitive collation on your db/table.
这篇关于使用 mysql、php 和 pdo 选择不区分大小写的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!