在 MySQL 中，修改（更新）数据通常使用 UPDATE 语句。通过 UPDATE 语句，可以修改表中已存在的数据。下面是关于 MySQL 中常用的修改数据的方法的汇总，包括各种用法和实际案例。
1. 基础的 UPDATE 语法
UPDATE 语句的基本语法如下：
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
table_name：要更新的表的名称。column1, column2, ...：要更新的列名。value1, value2, ...：更新后的新值。WHERE condition：指定更新的条件，只有符合条件的记录会被更新。
假设有一个名为 userTable 的表，结构如下：
CREATE TABLE userTable (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
sex ENUM('male', 'female', 'other'),
age INT,
phone VARCHAR(20),
email VARCHAR(100),
birthday DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
1.1 修改单条记录
假设我们要修改 id 为 1 的用户的 name 和 phone。
UPDATE userTable SET name = 'John Updated', phone = '1112223333' WHERE id = 1;
-
name = 'John Updated'：更新name字段的值为'John Updated'。 -
phone = '1112223333'：更新phone字段的值为'1112223333'。 -
WHERE id = 1：只更新id为 1 的记录。
1.2 修改多条记录
如果你要修改多个记录的字段，可以在 WHERE 子句中使用 IN 或其他条件。
UPDATE userTable SET name = 'Updated Name', phone = '5559876543' WHERE id IN (2, 3);
-
id IN (2, 3)：选择id为 2 或 3 的记录。 -
name = 'Updated Name'和phone = '5559876543'：更新这两条记录的name和phone字段。
1.3 修改所有记录
如果省略 WHERE 子句，UPDATE 会修改所有记录的指定字段。
UPDATE userTable SET phone = '0000000000';
-
phone = '0000000000'：将userTable表中所有记录的phone字段更新为'0000000000'。 -
注意：这种方式会影响所有记录，请小心使用。
2. UPDATE 与条件组合
通过 UPDATE 语句，你可以使用不同的条件来限制更新的记录。常见的条件包括：
-
比较运算符（
=,>,<,>=,<=,<>） -
逻辑运算符（
AND,OR,NOT） -
模糊匹配（
LIKE） -
范围查询（
BETWEEN） -
空值检查（
IS NULL,IS NOT NULL）
2.1 使用比较运算符
UPDATE userTable SET phone = '8887776666' WHERE age > 30;
age > 30：将 age 大于 30 的用户的 phone 字段更新为 '8887776666'。
2.2 使用逻辑运算符
UPDATE userTable SET phone = '9998887777' WHERE gender = 'female' AND age < 25;
gender = 'female' AND age < 25：更新 gender 为 'female' 且 age 小于 25 的记录。
2.3 使用 LIKE 模糊匹配
UPDATE userTable SET email = 'updated@example.com' WHERE email LIKE '%example.com';
LIKE '%example.com' ：将所有 email 以 example.com 结尾的用户的 email 字段更新为 'updated@example.com'。
2.4 使用 BETWEEN 查询范围
UPDATE userTable SET phone = '3334445555' WHERE age BETWEEN 20 AND 30;
age BETWEEN 20 AND 30：更新 age 在 20 到 30 之间的用户的 phone 字段。
2.5 使用 IS NULL 和 IS NOT NULL
UPDATE userTable SET email = 'default@example.com' WHERE email IS NULL;
email IS NULL：将所有 email 字段为空的用户的 email 字段更新为 'default@example.com'。
3. 批量更新
你可以通过 CASE 语句来实现条件不同的批量更新。
示例：根据 id 修改不同的字段值
UPDATE userTable
SET phone = CASE
WHEN id = 1 THEN '1234567890'
WHEN id = 2 THEN '9876543210'
ELSE phone
END
WHERE id IN (1, 2);
这里，id = 1 的用户将更新为 '1234567890'，id = 2 的用户将更新为 '9876543210'，其他用户的 phone 不变。
4. 更新与返回自增字段
如果你想在更新时返回自动生成的字段（如 id），你可以使用 RETURNING（MySQL 8.0 及以上版本）。
UPDATE userTable SET name = 'Alice Updated' WHERE id = 1 RETURNING id, name;
- 这将更新
id = 1的记录，并返回更新后的id和name字段。
注意：如果你使用的是 MySQL 5.x 或更早版本，RETURNING 不被支持，你需要在应用层获取更新后的数据。
5. 使用 JOIN 更新关联表中的数据
在某些情况下，你可能需要基于另一个表的内容更新当前表的记录。这时可以使用 JOIN 语句。
示例：根据另一个表的数据更新当前表
UPDATE userTable u
JOIN userDetails d ON u.id = d.user_id
SET u.phone = d.new_phone
WHERE d.updated = 1;
这里，将更新 userTable 中的 phone 字段，更新的值来自于 userDetails 表，前提是 userDetails 中的 updated 字段为 1。
6. UPDATE 与 LIMIT 限制更新数量
如果你只想更新一定数量的记录，可以使用 LIMIT 关键字。
示例：限制更新最多 3 条记录
UPDATE userTable
SET phone = '6667778888'
WHERE age > 30
LIMIT 3;
这将只更新最多 3 条 age > 30 的记录。
7. UPDATE 与 NOW() 更新时间字段
如果需要将时间字段更新为当前时间，可以使用 NOW() 函数。
示例：将 updated_at 字段更新为当前时间
UPDATE userTable
SET updated_at = NOW()
WHERE id = 1;
这里，updated_at 字段将被更新为当前时间戳。
8. 使用 UPDATE 设置多个表的字段
如果你的数据库设计中涉及到多个表，并且你需要同时更新多个表中的数据，可以使用 JOIN 来连接多个表。
示例：同时更新 userTable 和 userDetails 表中的数据
UPDATE userTable u
JOIN userDetails d ON u.id = d.user_id
SET u.phone = d.new_phone, d.last_updated = NOW()
WHERE u.id = 1;
这会在更新 userTable 表的 phone 字段时，同时更新 userDetails 表的 last_updated 字段。
9. 总结
| 方法 | 说明 | 示例 |
|---|---|---|
| UPDATE | 标准更新方法 | UPDATE userTable SET name = 'John' WHERE id = 1; |
UPDATE 与逻辑运算符（AND/OR） | 组合多个条件进行更新 | UPDATE userTable SET name = 'John' WHERE age > 30; |
UPDATE 与 LIKE 模糊匹配 | 使用 LIKE 进行模糊匹配进行条件查询 | UPDATE userTable SET email = 'new@example.com' WHERE email LIKE '%example.com'; |
使用 CASE 语句批量更新 | 条件不同的情况下进行不同的更新 | UPDATE userTable SET phone = CASE WHEN id = 1 THEN '123' ELSE '456' END WHERE id IN (1, 2); |
UPDATE 与 JOIN 联合更新 | 使用 JOIN 联接表并更新多个表的数据 | UPDATE userTable u JOIN userDetails d ON u.id = d.user_id SET u.phone = d.new_phone WHERE u.id = 1; |
UPDATE 与 LIMIT 限制更新 | 限制更新的记录数量 | UPDATE userTable SET phone = '123' WHERE age > 25 LIMIT 2; |
UPDATE 与 NOW() 更新时间 | 使用 NOW() 函数更新时间字段 | UPDATE userTable SET updated_at = NOW() WHERE id = 1; |
