使用Sprintf向MySQL插入空日期值报错，求技术解决方案

Hey there! Let's break down why you're hitting this date value error and how to fix it once and for all.

The Root Cause #

The error message spells out the core issue clearly:

Cannot execute SQL statement: Incorrect date value: '' for column 'signedupdate' at row 1

Even though your signedupdate column allows NULL values, you're passing an empty string ('') instead of NULL in your SQL statements. MySQL 5.7 enables strict mode (STRICT_TRANS_TABLES) by default, which rejects invalid date values like empty strings outright—instead of silently converting them to NULL like older versions might have. That's exactly what's triggering the failure.

Solution: Pass NULL Instead of Empty Strings #

You need to check if $rowData['signedupdate'] is empty, and swap out the empty string for NULL when building your queries. Here's how to adjust your code:

Modified Insert Statement

$lastInsertId = $this->GetConnection()->GetLastInsertId();
// Handle empty signedupdate value safely
$signedUpdateValue = empty($rowData['signedupdate']) 
    ? 'NULL' 
    : "'" . $this->GetConnection()->escape_string($rowData['signedupdate']) . "'";
// Escape plan_type to avoid SQL injection risks
$planType = $this->GetConnection()->escape_string($rowData['plan_type']);

$sql = sprintf(
    "INSERT INTO tbl_lead (client_id, signedupdate, plan_type) VALUES(%d, %s, '%s');",
    $lastInsertId,
    $signedUpdateValue,
    $planType
);
$this->GetConnection()->ExecSQL($sql);

Modified Update Statement

// Handle empty signedupdate value safely
$signedUpdateValue = empty($rowData['signedupdate']) 
    ? 'NULL' 
    : "'" . $this->GetConnection()->escape_string($rowData['signedupdate']) . "'";
// Escape plan_type to avoid SQL injection risks
$planType = $this->GetConnection()->escape_string($rowData['plan_type']);
// Ensure you have the client_id value (it was missing in your original snippet!)
$clientId = $rowData['client_id']; // Adjust this to match your actual data source

$sql = sprintf(
    "UPDATE tbl_lead SET signedupdate = %s, plan_type = '%s' WHERE client_id = %d;",
    $signedUpdateValue,
    $planType,
    $clientId
);
$this->GetConnection()->ExecSQL($sql);

A Safer Long-Term Fix: Parameterized Queries #

While the above works, manually escaping values and building strings is error-prone and risky for SQL injection. If your database connection supports it (like PDO or MySQLi prepared statements), use parameterized queries instead. They automatically handle NULL values and eliminate injection risks entirely:

Example with PDO (adjust based on your connection type):

// Insert example
$stmt = $pdo->prepare("INSERT INTO tbl_lead (client_id, signedupdate, plan_type) VALUES(?, ?, ?)");
$stmt->execute([
    $lastInsertId,
    empty($rowData['signedupdate']) ? null : $rowData['signedupdate'],
    $rowData['plan_type']
]);

// Update example
$stmt = $pdo->prepare("UPDATE tbl_lead SET signedupdate = ?, plan_type = ? WHERE client_id = ?");
$stmt->execute([
    empty($rowData['signedupdate']) ? null : $rowData['signedupdate'],
    $rowData['plan_type'],
    $clientId
]);

Quick Final Checks #

• Double-check your MySQL sql_mode doesn't include NO_ZERO_DATE or NO_ZERO_IN_DATE (though strict mode is the main culprit here)
• Confirm your frontend sends a clear "empty" signal (like null or an empty string) that your backend converts to NULL before querying

内容的提问来源于stack exchange，提问作者Gary Brett