如何在PDOStatement :: bindValue()中定义变量类型? [英] How define the variable type in PDOStatement::bindValue()?

问题描述

PDOStatement :: bindValue()方法提供了一种方法来指定变量界限:

The PDOStatement::bindValue() method offers a way to specify the type of the variable bound:

PDOStatement :: bindValue($ parameter，$ value [， $ data_type = PDO :: PARAM_STR ])

我想知道，指定数据类型的目的是什么，而当保留为默认值(PARAM_STR)时，最终数据库将在使用它之前将其强制转换为正确的类型吗?

I'm wondering, what's the purpose of specifying the data type, whereas when leaved as default (PARAM_STR) eventually the database will anyway cast the value to the proper type before using it?

例如，如果您对INTEGER字段有以下查询:

For example, if you have these queries over an INTEGER field:

INSERT INTO table (integerField) VALUES (?) ;
SELECT * FROM table WHERE integerField = ?  ;

然后在PHP中绑定一个整数，默认情况下，PDO会将其绑定为一个字符串，等效于:

And you bind an integer in PHP, PDO will by default bind it as a string, which is equivalent as:

INSERT INTO table (integerField) VALUES ("1") ;
SELECT * FROM table WHERE integerField = "1"  ;

这将完美地起作用，因为SQL数据库(至少是MySQL，我并不真正了解在其他RDBMS上的工作方式)知道如何在使用字符串之前将其转换回整数.

That will work flawlessly, because the SQL database (at least MySQL, I'm not really aware of how that would work on other RDBMS) knows how to convert the string back to an integer before using it.

在什么情况下，绑定类型参数和字符串会有所不同?

What are the use cases where it would make a difference to bound typed parameters vs strings?

推荐答案

我不是PDO专家，但是我可以想到一些场景，其中data_type参数既有用又需要.

I'm no PDO-expert, but I can think of a few scenarioes where the data_type parameter is both useful and even needed.

输出参数

定义输出或输入/输出参数时，必须同时提供预期输出参数的类型和长度.

When you define output or input/output parameters, you must provide both type and length of the expected output parameter.

参考: http://www.php.net/manual /en/pdo.prepared-statements.php

示例4

$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 

示例5

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

没有隐式强制转换的DBM

对此问题的另一个回答...

Explained in another answer to this question...

当参数未绑定到可投射数据时

即使具有转换能力的数据库也无法始终正确地转换变量.

Even databases with casting abilities will not always be able to cast you variable correctly.

引用:是在PDO中强类型化参数的原因吗?

$limit = 1;

$dbh->prepare("SELECT * FROM items LIMIT :limit");
$dbh->bindParam(":limit", $limit, PDO::PARAM_STR); 
// Will throw "You have an error in your SQL syntax..."

这篇关于如何在PDOStatement :: bindValue()中定义变量类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！