mysqli_stmt::bind_param() - specify another data type than "s" for each parameter

A mysqli_stmt does not have a query_params() function, I had to write my own. The parameter arry is bound to the statement with bind_param(). I need to specify the variable types dynamically. I could do that with something like:

$sType = '';
foreach ($aParameters as $iIndex => $mParameter) {
  if     (is_string($mParameter)) {$sType .= 's';}
  elseif (   is_int($mParameter)) {$sType .= 'i';}
  elseif ( is_float($mParameter)) {$sType .= 'd';}
  elseif (  is_null($mParameter)) {$sType .= 's';}
  elseif (  is_bool($mParameter)) {
    $sType .= 'i';
    $aParameters[$iIndex] = boolval($mParameter);}
  else {
    // trow new Exception(...);
  }
}

But as it turns out, mysql/mariadb will send booleans, integers and floats fine as strings, where the database server will happily cast them to the corresponding data type of the column. It seems like I could just skip this step and send every parameter as a string by default.

Are there any reaons to specify another data type than "s" for each parameter?

EDIT: I just found this SO topic which shows how to use the "b" type and mysqli_stmt::send_long_data when the binary packet would exceed the max_allowed_packet setting. I also have read that it will improve performance over solutions that employ bin2hex() to turn send a byte string as text.

Solution

The only time I have found it's important to use an integer parameter is in a LIMIT clause.

SELECT
...
LIMIT ?, ?

MySQL does not accept quoted string literals in this context, and does not accept parameters with string type. You have to use an integer.

See Parametrized PDO query and `LIMIT` clause - not working for my tests on this. That was a question about PDO, and I didn't test mysqli, but I believe it's a server-side MySQL requirement to use integer parameters in this case. So it should apply to mysqli too.

In all other cases (AFAIK), MySQL is able to convert strings into integers by reading the leading digits in the string, and ignoring any following characters.

@Dharman in a comment below makes reference to MySQL's support for integers in ORDER BY:

SELECT
...
ORDER BY ?

An integer in ORDER BY means to sort by the column in that position, not by the constant value of the number:

SELECT
...
ORDER BY 1 -- sorts by the 1st column

But an equivalent string value containing that number doesn't act the same. It sorts by the constant value of the string, which means every row is tied, and the sort order will be arbitrary.

SELECT
...
ORDER BY '1' -- sorts by a constant value, so all rows are tied

Therefore this is another case where the data type for a query parameter is important.

On the other hand, using ordinal numbers to sort by the column in that position in ORDER BY or GROUP BY is deprecated, and we shouldn't rely on that usage of SQL.