调试PDO mySql将NULL插入数据库而不是空 [英] Debug PDO mySql insert NULL into database instead of empty
问题描述
我正在尝试使用PDO将'NULL'动态插入数据库中.
I am trying to dynamically insert 'NULL' into the database using PDO.
表结构:
CREATE TABLE IF NOT EXISTS `Fixes` (
`Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'PK',
`CurrencyId` int(11) NOT NULL COMMENT 'FK',
`MetalId` int(11) NOT NULL COMMENT 'FK',
`FixAM` decimal(10,5) NOT NULL,
`FixPM` decimal(10,5) DEFAULT NULL,
`TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Id`),
KEY `CurrencyId` (`CurrencyId`),
KEY `MetalId` (`MetalId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=13 ;
PHP/PDO查询:
PHP / PDO QUERY:
$sql = 'UPDATE
Fixes
SET
FixAM = :fixAM,
FixPM = :fixPM
WHERE
MetalId IN (SELECT Id FROM Metals WHERE Name = :metal) AND
CurrencyId IN (SELECT Id FROM Currencies Where Id = :currency)';
$stmt = $db->prepare($sql);
for ($i = 0; $i<3; $i++) {
$stmt->execute(array(
':metal' => 'Silver',
':fixAM' => $fix['FixAM'][$i],
':fixPM' => $fix['FixPM'][$i],
':currency' => ($i+1))
);
}
例如有时，$fix['FixPM'][$i]的值为有时'NULL'.如何将此插入数据库?当我运行查询然后查看数据库中的数据时，此记录显示0.0000，而不是null.
e.g. sometimes, the value for $fix['FixPM'][$i] is sometimes 'NULL'. How do I insert this into the database? When I run the query and then view the data in the database, this record shows 0.0000, and not null.
如何使用PDO插入NULL值?提供了一些解决方案.
How do I insert NULL values using PDO? provides a few solutions.
- 我不认为我可以按照示例使用
$stmt->execute(array( ':v1' => null, ':v2' => ... ))，因为有时该项为null，有时不是.因此，我需要引用我创建的变量$fix['FixPM'][$i]，并在需要时使该变量为 null
- I dont think I can use
$stmt->execute(array( ':v1' => null, ':v2' => ... ))as per example because sometimes the item is null, and sometimes not. As such, I need to refer to the variable I have created$fix['FixPM'][$i]and make that null as and when needed
先谢谢了.
推荐答案
在我看来，这是PDO准备好的语句仿真中的(n个未报告?)错误:
This appears to me to be a(n unreported?) bug in PDO's prepared statement emulation:
-
实现
PDOStatement::execute()最终调用pdo_parse_params();
the implementation of
PDOStatement::execute()eventually invokespdo_parse_params();
，而试图基于相关参数的数据类型的引号/转义值(如PDOStatement::bindValue() 和 PDOStatement::bindParam() —作为$input_parameters提供给 PDOStatement::execute() 的所有参数都被视为PDO::PARAM_STR ，如该函数的文档中所述)；
that, in turn, attempts to quote/escape values based on the relevant parameter's data type (as indicated by the $data_type arguments to PDOStatement::bindValue() and PDOStatement::bindParam()—all parameters provided as $input_parameters to PDOStatement::execute() are treated as PDO::PARAM_STR, as stated in the documentation of that function);
字符串类型的值由调用相关数据库驱动程序的 null:对于PDO_MySQL，这就是 NO_BACKSLASH_ESCAPES SQL模式；
string-typed values are escaped/quoted by calling the relevant database driver's quoter() method irrespective of whether they are null: in the case of PDO_MySQL, that's mysql_handle_quoter(), which (eventually) passes the value to either mysqlnd_cset_escape_quotes() or mysql_cset_escape_slashes(), depending on the server's NO_BACKSLASH_ESCAPES SQL mode;
给定一个null参数，这两个函数都返回一个空字符串.
given a null argument, both of those functions return an empty string.
我的观点是，在之前，参数的类型(在上面的步骤2中)，如果值是null，则pdo_parse_params()应该将类型设置为PDO::PARAM_NULL.但是，有些人可能会争辩说，这将阻止在适当的情况下对null值进行类型特定的处理，在这种情况下，
My opinion is that, prior to switching on the parameter's type (in step 2 above), pdo_parse_params() should set the type to PDO::PARAM_NULL if the value is null. However, some might argue that this would prevent type-specific handling of null values where appropriate, in which case the string case (in step 3 above) should definitely handle null values before proceeding with a call to the driver's quoter() method.
作为一种临时解决方法，无论如何，禁用准备好的语句仿真通常是最好的选择:
As an interim workaround, disabling prepared statement emulation is usually for the best anyway:
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
这篇关于调试PDO mySql将NULL插入数据库而不是空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
