mysqli_stmt :: bind_param()-指定除"s"之外的另一种数据类型.对于每个参数 [英] mysqli_stmt::bind_param() - specify another data type than "s" for each parameter

问题描述

mysqli_stmt 没有 query_params() 函数，我必须编写自己的函数.参数arry通过 bind_param() 绑定到该语句. .我需要动态指定变量类型.我可以用类似的方法做到这一点:

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(...);
  }
}

但是事实证明，mysql/mariadb将发送布尔值，整数和浮点数作为字符串，数据库服务器将在其中高兴地将它们强制转换为列的相应数据类型.看来我可以跳过此步骤，默认情况下将每个参数作为字符串发送.

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.

对于每个参数，是否有任何理由指定不同于"s"的另一种数据类型?

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

我刚刚发现此SO 该主题显示了如何使用"b"类型和 当二进制数据包超过 max_allowed_packet 设置.我还了解到，与使用bin2hex()转换为将字节字符串作为文本发送的解决方案相比，它将提高性能.

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.

推荐答案

我唯一发现使用整数参数的重要时间是在LIMIT子句中.

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

SELECT
...
LIMIT ?, ?

MySQL在这种情况下不接受带引号的字符串文字，也不接受带有字符串类型的参数.您必须使用整数.

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

请参见参数化的PDO查询和`LIMIT`子句-无法对此进行测试.那是关于PDO的问题，我没有测试mysqli，但我认为在这种情况下使用整数参数是服务器端MySQL的要求.因此，它也应适用于mysqli.

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.

在所有其他情况下(AFAIK)，MySQL都可以通过读取字符串中的前导数字并忽略后面的任何字符来将字符串转换为整数.

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在下面的注释中引用了MySQL对ORDER BY中整数的支持:

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

SELECT
...
ORDER BY ?

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.

另一方面，不建议使用序号按ORDER BY或GROUP BY中该位置的列排序，并且我们不应该依赖于SQL的用法.

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.

这篇关于mysqli_stmt :: bind_param()-指定除"s"之外的另一种数据类型.对于每个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！