求助：MySQL创建/更新表及批量修改users表phone字段值

Got it, let's work through this problem step by step. You previously ran an UPDATE to set specific phone numbers to empty strings, and now you need to assign distinct values ('teste', 't', 'teste3') to those empty-phone records. The main challenge here is distinguishing which empty-phone row maps to which original phone number, since the phone field itself is now empty.

Solution 1: Target Rows Using Unique Identifiers #

If you can identify the original rows (that once had '91111', '9222', '9333') via other unique fields in the users table (like id, email, or a combination of name + city), you can update each row individually.

Example Queries:

• If you know the id of each original record:

  -- Update the row that originally had '91111' to 'teste'
  UPDATE users SET phone = 'teste' WHERE id = [your_target_id_1];
  
  -- Update the row that originally had '9222' to 't'
  UPDATE users SET phone = 't' WHERE id = [your_target_id_2];
  
  -- Update the row that originally had '9333' to 'teste3'
  UPDATE users SET phone = 'teste3' WHERE id = [your_target_id_3];
  

• If you don't have the id but have other unique identifiers (e.g., email):

  UPDATE users SET phone = 'teste' WHERE email = 'user1@example.com';
  UPDATE users SET phone = 't' WHERE email = 'user2@example.com';
  UPDATE users SET phone = 'teste3' WHERE email = 'user3@example.com';
  

Solution 2: Use a Backup to Map Original Values #

If you have a backup of the users table from before you ran the initial UPDATE, you can join it with your current table to map original phone values to new targets in one go:

UPDATE users u
JOIN users_backup ub ON u.id = ub.id
SET u.phone = CASE
    WHEN ub.phone = '91111' THEN 'teste'
    WHEN ub.phone = '9222' THEN 't'
    WHEN ub.phone = '9333' THEN 'teste3'
END
WHERE ub.phone IN ('91111', '9222', '9333');

Critical Precaution: Verify Before Updating #

Always run a SELECT first to confirm you're targeting the correct rows. This avoids accidental updates to unintended records:

-- Check all rows with empty phone values
SELECT id, name, email, city FROM users WHERE phone = '' OR phone IS NULL;

Quick Note on Your Initial Update (Context) #

If your first UPDATE didn't work as expected, common culprits include:

• Typos in the phone values in the WHERE clause (so no rows matched)
• The phone column has a NOT NULL constraint (though you said you set it to empty, so this might not apply here)

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