为什么在预准备语句中计算'like'子句需要括号？

Question

I thought that I'd add a clause similar to the first line of the following to a prepared statement in PHP to run in MySQL:

$sql .= " where ? like '%'+name+'%' ";

if ($stmt = mysqli_prepare($con, $sql)) {    
//irrelevant code omitted here.
} else {
    echo("Error description: " . mysqli_error($con));
}

where name is a column in the table and ? is a parameter to be bound.

I get this error:

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 '+name+'%' at line 1

If I replace ? with 'fred' and run it in MySQL workbench it will run. Similarly it works if I just remove the concatenation so that it starts $sql .= " where ? like name ";

However if I just change it like so:

? like ('%'+name+'%')

then it runs. Anyone know why please? I ask because I feel that there's something to understand here and I'm not getting it. In particular I'd like to know if there are other scenarios where I'll have to add arbitrary parentheses when using prepared statements?

Answer 1

Use CONCAT(str1,str2,...) to concatenate:

$sql .= " where ? like CONCAT('%', name, '%')";

+ - is addition operator for numbers in MySQL, see: http://dev.mysql.com/doc/refman/5.7/en/arithmetic-functions.html#operator_plus