How to Properly Assign NULL Values to Datetime Fields in MySQL?

How to Handle NULL Values in MySQL Datetime Fields

MySQL accepts NULL values in datetime fields, despite the common misconception. To assign a NULL value to a datetime field, simply leave the variable empty or send the value NULL explicitly.

Insert NULL Values Using MySQL

CREATE TABLE datetimetest (testcolumn DATETIME NULL DEFAULT NULL);
INSERT INTO datetimetest (testcolumn) VALUES (NULL);

Insert NULL Values Using PHP Prepared Statements

When using prepared statements, bind variables as usual. PHP variables containing NULL values will be stored as NULL in MySQL.

$stmt = $conn->prepare("UPDATE users SET bill_date = ? WHERE user_id = ?");

if (isset($bill_date)) {
    $stmt->bind_param("si", $bill_date, $user_id);
} else {
    $stmt->bind_param("si", NULL, $user_id);
}

Note on PHP Empty Variables

Ensure that PHP variables contain NULL, not an empty string. An empty string will result in an error:

Incorrect datetime value: '' for column 'bill_date' at row 1

Therefore, send NULL explicitly or leave the variable empty to avoid this issue.

The above is the detailed content of How to Properly Assign NULL Values to Datetime Fields in MySQL?. For more information, please follow other related articles on the PHP Chinese website!