如何通过strtotime转换日期批量更新MySQL表中符合条件的行

Got it, let's sort out this batch update logic the right way. Your original code has a common pitfall—comparing a raw timestamp integer directly to a date string, which won't work as expected. Here's how to fix it properly using date/timestamp comparisons:

Option 1: Let the Database Handle Date Comparisons (Most Efficient) #

If your d_pay_date column is stored as a DATE/DATETIME type (the recommended approach), you can use MySQL's built-in date functions to compare directly without converting anything in PHP:

// Update all records where the payment date is today or earlier
$sql = "UPDATE ca_dreams SET d_matured_status = 1 WHERE d_pay_date <= CURDATE()";

If d_pay_date is stored as a string (e.g., 'YYYY-MM-DD'), you can still let MySQL parse it safely with STR_TO_DATE:

// Explicitly convert the string date to a date type for comparison
$sql = "UPDATE ca_dreams SET d_matured_status = 1 WHERE STR_TO_DATE(d_pay_date, '%Y-%m-%d') <= CURDATE()";

Option 2: Compare Timestamps (Using strtotime Equivalent in SQL) #

If you prefer working with timestamps, use MySQL's UNIX_TIMESTAMP() function to convert the stored date to a timestamp (this is the database-side equivalent of PHP's strtotime()), then compare it to PHP's time():

$current_timestamp = time();
// Compare timestamps directly in the query
$sql = "UPDATE ca_dreams SET d_matured_status = 1 WHERE UNIX_TIMESTAMP(d_pay_date) <= $current_timestamp";

Critical Note: Avoid SQL Injection! #

Never directly concatenate variables into your SQL string (like your original example). Use prepared statements instead to keep your code secure:

// Using PDO (recommended for security)
$pdo = new PDO('mysql:host=your_host;dbname=your_db', 'username', 'password');
$stmt = $pdo->prepare("UPDATE ca_dreams SET d_matured_status = 1 WHERE UNIX_TIMESTAMP(d_pay_date) <= ?");
$stmt->execute([time()]);

// Using mysqli
$mysqli = new mysqli('your_host', 'username', 'password', 'your_db');
$stmt = $mysqli->prepare("UPDATE ca_dreams SET d_matured_status = 1 WHERE UNIX_TIMESTAMP(d_pay_date) <= ?");
$stmt->bind_param("i", time());
$stmt->execute();

Why Your Original Code Failed #

Your original query compares time() (a numeric timestamp like 1716182400) to d_pay_date (a date string like '2024-05-20'). This is an invalid string-to-number comparison that won't return the correct records—MySQL will try to convert the date string to a number (resulting in 2024), which doesn't match your timestamp.

内容的提问来源于stack exchange，提问作者Emil K