调试 PDO mySql 将 NULL 插入数据库而不是空

Debug PDO mySql insert NULL into database instead of empty(调试 PDO mySql 将 NULL 插入数据库而不是空)

问题描述

我正在尝试使用 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 ;

$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))
    );      
}

反过来，试图引用/转义值 基于相关参数的数据类型(如 $data_type 参数所示).bindvalue.php" rel="nofollow">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);

字符串类型的值被 调用相关数据库驱动的quoter() 方法，无论它们是否为 null:对于 PDO_MySQL，即为 mysql_handle_quoter()，它(最终)将值传递给 mysqlnd_cset_escape_quotes() 或 mysql_cset_escape_slashes()，取决于在服务器的 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 值进行特定类型的处理，在这种情况下，字符串大小写(在上面的第 3 步中)在继续调用驱动程序 quoter() 方法.

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 插入数据库而不是空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！