MySQL更新命令未生效：匹配行无变更问题求助

Let's break down why your update isn't having any effect and walk through the fixes to get the date format you want.

Why Your Update Isn't Working #

You're seeing "0 row(s) affected" for a few key reasons:

• Wrong format specifier in DATE_FORMAT
  You're using %Y/%m/%d which outputs dates like 2017/05/30, but your goal is 20170530. Even worse, if your date column is a DATE or DATETIME type, MySQL will automatically convert that slash-separated string back to the standard YYYY-MM-DD storage format—so the value doesn't actually change.
• Mismatched WHERE clause or pre-existing values
  The WHERE counter = 1 condition might not be targeting the rows you think it is, or those rows already have a value that MySQL considers identical to what you're trying to set (thanks to the automatic type conversion mentioned above).
• DATE columns store fixed formats
  If your date column is a native DATE type, MySQL stores it in an internal format (not the human-readable YYYY-MM-DD you see) and only formats it as YYYY-MM-DD when querying. You can't modify the storage format directly—you can only control how it's displayed when you fetch the data.

Fixes to Resolve the Issue #

Choose the solution that fits your actual need:

Option 1: Just format dates when querying (no table changes needed) #

If you don't need to change the stored data, just use DATE_FORMAT in your SELECT queries to get the YYYYMMDD format:

SELECT DATE_FORMAT(date, '%Y%m%d') AS formatted_date, counter, ... 
FROM stations.attenuation_smoothed;

Option 2: Change the column type and update the values #

If you must store the date as a string in YYYYMMDD format:

1. First disable safe updates (to avoid errors with the ALTER and UPDATE commands):

   SET SQL_SAFE_UPDATES = 0;
   

2. Modify the date column to a string type (like VARCHAR(8) since YYYYMMDD is 8 characters):

   ALTER TABLE stations.attenuation_smoothed MODIFY COLUMN date VARCHAR(8);
   

3. Update the values to the desired format:

   UPDATE stations.attenuation_smoothed 
   SET date = DATE_FORMAT(STR_TO_DATE(date, '%Y-%m-%d'), '%Y%m%d') 
   WHERE counter = 1;
   

   (The STR_TO_DATE ensures we correctly parse the existing YYYY-MM-DD value before reformatting it.)
4. Re-enable safe updates:

   SET SQL_SAFE_UPDATES = 1;
   

Option 3: Verify your WHERE clause is correct #

Double-check that you're targeting the right rows by running a SELECT first:

SELECT * FROM stations.attenuation_smoothed WHERE counter = 1;

This will show you if there are rows matching the condition, and what their current date values are.

内容的提问来源于stack exchange，提问作者Muhammad Ahsan Gillani Gillani