mysql PDO如何绑定LIKE [英] mysql PDO how to bind LIKE

问题描述

在此查询中

 选择wrlen from tablename WHERE wrd LIKE'$ partial％'
  

我试图用PDO绑定变量'$ partial％'不知道这是如何与％结束。

这将是

 从tablename中选择wrd WHERE wrd LIKE' ：partial％'
  

其中：partial 绑定到 $ partial =somet

或

 从tablename选择wrd WHERE wrd LIKE'：partial'
  

$ b b

其中：partial 绑定到 $ partial =somet％

$ b $

解决方案

+1 karim的答案涵盖了它。您还可以说：

 SELECT wrd FROM tablename WHERE wrd LIKE CONCAT（：partial，'％'）
 

在MySQL端加入字符串，而不是在这种情况下有任何特殊的原因。

如果你正在寻找的部分 wrd 本身包含百分号或下划线字符，事情会变得更棘手对于LIKE运算符具有特殊含义）或反斜杠（根据ANSI SQL标准，MySQL在LIKE运算符中用作另一层转义 - 不正确）。

希望这不会影响你，但如果你确实需要得到这种情况下，这里是一个凌乱的解决方案：

  $ stmt = $ db-> prepare（SELECT wrd FROM tablename WHERE wrd LIKE：term ESCAPE'+'）; 
 $ escaped = str_replace（array（'+'，'％'，'_'），array（'++'，'+％'，'+ _'），$ var）; 
 $ stmt-> bindParam（'：term'，$ escaped）; 
  

In this query

select wrd from tablename WHERE wrd LIKE '$partial%'

I'm trying to bind the variable '$partial%' with PDO. Not sure how this works with the % at the end.

Would it be

select wrd from tablename WHERE wrd LIKE ':partial%'

where :partial is bound to $partial="somet"

or would it be

select wrd from tablename WHERE wrd LIKE ':partial'

where :partial is bound to $partial="somet%"

or would it be something entirely different?

解决方案

+1 karim's answer covers it. You could also say:

"SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%')"

to do the string joining at the MySQL end, not that there's any particular reason to in this case.

Things get a bit more tricky if the partial wrd you are looking for can itself contain a percent or underscore character (since those have special meaning for the LIKE operator) or a backslash (which MySQL uses as another layer of escaping in the LIKE operator — incorrectly, according to the ANSI SQL standard).

Hopefully that doesn't affect you, but if you do need to get that case right, here's the messy solution:

$stmt= $db->prepare("SELECT wrd FROM tablename WHERE wrd LIKE :term ESCAPE '+'");
$escaped= str_replace(array('+', '%', '_'), array('++', '+%', '+_'), $var);
$stmt->bindParam(':term', $escaped);

这篇关于mysql PDO如何绑定LIKE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！