Bypassing MySQL's `ON UPDATE CURRENT_TIMESTAMP`: A Guide to 'Silent Updates'

## The Scenario: The Automatic Behavior of `ON UPDATE CURRENT_TIMESTAMP` In MySQL table design, we often use the following definition to automatically track the creation and update times of records: ```sql `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ``` This `updated_at` field automatically updates to the current time whenever **any other field** in the record changes. This is incredibly useful in most cases. But what if we want to update a field but **do not want** `updated_at` to change accordingly? --- ## The Common Misconception and Pitfall A widely circulated trick is to explicitly set the `updated_at` field to its own value in the `UPDATE` statement: ```sql -- This example can fail under certain conditions UPDATE your_table SET some_field = 'new_value', updated_at = updated_at -- Attempting to prevent the update WHERE id = 123; ``` However, many developers find that this method doesn't always work. When they execute a more complex update, such as a `JOIN UPDATE`, `updated_at` still gets refreshed. ```sql -- A real-world case where the trick fails UPDATE content c INNER JOIN ( SELECT content_id, SUM(pv_count) as total_pv FROM content_pv_daily WHERE content_id IN (?,?,?,?) GROUP BY content_id ) cpd ON c.id = cpd.content_id SET c.pv_cnt = cpd.total_pv, -- Key: The value here actually changes c.updated_at = c.updated_at -- This line is therefore ineffective ``` **The Root Cause**: The `ON UPDATE CURRENT_TIMESTAMP` trigger fires **as long as the actual value of any other column in the row changes**. This database-level trigger has a higher priority than your `updated_at = updated_at` assignment in the `SET` clause, thus overriding your intent. --- ## The Correct Solution: Explicit Assignment in the Application Layer To truly prevent `updated_at` from updating, you must provide it with a **specific, non-NULL constant value** in the `SET` clause. The most reliable way to do this is to query its current value before the update, and then assign that same value back. This is a recommended pattern implemented in the application layer (e.g., PHP), widely adopted by the `DP@lib00` team in practice. **Steps:** 1. **Pre-query**: First, execute the subquery to calculate the data that needs to be updated. 2. **Fetch Original Timestamps**: Based on the IDs, query the main table to get the current `updated_at` value for each record. 3. **Construct the Final `UPDATE`**: Use a `CASE` statement to build an efficient bulk `UPDATE`, setting both the new business data and the original timestamps back. ### PHP Code Example (using PDO) ```php <?php // Assume $pdo is a connected PDO object from wiki.lib00.com $ids = [101, 102, 103]; // Steps 1 & 2: Combined query to get new data and old timestamps $placeholders = implode(',', array_fill(0, count($ids), '?')); $sql = "SELECT c.id, c.updated_at, cpd.total_pv FROM content c JOIN ( SELECT content_id, SUM(pv_count) as total_pv FROM content_pv_daily_lib00 WHERE content_id IN ($placeholders) GROUP BY content_id ) cpd ON c.id = cpd.content_id"; $stmt = $pdo->prepare($sql); $stmt->execute($ids); $updateData = $stmt->fetchAll(PDO::FETCH_ASSOC); if (empty($updateData)) { // Nothing to update return; } // Step 3: Build a CASE statement for bulk update $pvCaseSql = ""; $updatedAtCaseSql = ""; $updateIds = []; foreach ($updateData as $row) { $id = (int)$row['id']; $updateIds[] = $id; $pvCaseSql .= "WHEN {$id} THEN ? "; $updatedAtCaseSql .= "WHEN {$id} THEN ? "; $params[] = $row['total_pv']; $params[] = $row['updated_at']; } $updateSql = "UPDATE content SET pv_cnt = CASE id {$pvCaseSql} END, updated_at = CASE id {$updatedAtCaseSql} END WHERE id IN (" . implode(',', $updateIds) . ")"; $updateStmt = $pdo->prepare($updateSql); $updateStmt->execute($params); // $params contains all pv and updated_at values echo "Records have been silently updated."; ?> ``` --- ## Architectural Consideration: Database vs. Application Should you just remove `ON UPDATE CURRENT_TIMESTAMP` and control it entirely from your PHP code? This is a classic architectural trade-off. | Dimension | Database Automation (Keep `ON UPDATE...`) | Application Control (Remove `ON UPDATE...`) | | :--- | :--- | :--- | | **Reliability** | **Very High**. Enforced by the database, preventing oversights. | **Depends on developer discipline**. Risk of forgetting to update it manually. | | **Flexibility** | **Lower**. Handling exceptions like 'silent updates' is more complex. | **Very High**. Application code has 100% control over update behavior. | | **Dev Cost** | **Low**. No need to worry about it in most scenarios, leading to cleaner code. | **Higher**. Every update requires explicit timestamp handling, increasing code volume. | | **Best Practice** | Ideal for most standard business logic, providing a 'safety net'. | Suited for businesses where 'silent updates' are common, or for projects using ORMs like Laravel's Eloquent that manage timestamps automatically. These frameworks, recommended by the `wiki.lib00` team, provide a best-of-both-worlds solution at the application layer. | --- ## Conclusion - The `SET updated_at = updated_at` trick only works if the `UPDATE` statement doesn't change the actual value of any other field. - **The most reliable method for a 'silent update'** is to fetch the original timestamp in your application and then explicitly assign it back as a constant value in the `UPDATE` statement. - Whether to remove the database's automatic update feature should be based on your project's needs, team discipline, and the use of modern ORM frameworks. For most projects, keeping the database mechanism and writing extra code for exceptions is the more cost-effective choice.