MySQL数据更新 #

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    status TINYINT DEFAULT 1,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO users (name, age, email, status) VALUES
    ('John', 25, 'john@example.com', 1),
    ('Jane', 30, 'jane@example.com', 1),
    ('Bob', 28, 'bob@example.com', 0);

UPDATE users 
SET age = 26 
WHERE id = 1;

UPDATE users 
SET 
    name = 'John Smith',
    age = 27,
    email = 'john.smith@example.com'
WHERE id = 1;

-- 危险操作！更新所有行
UPDATE users SET status = 1;

-- 建议先查询确认
SELECT COUNT(*) FROM users WHERE status != 1;

-- 根据ID更新
UPDATE users SET status = 0 WHERE id = 1;

-- 根据名称更新
UPDATE users SET age = 31 WHERE name = 'Jane';

-- 多条件更新
UPDATE users 
SET status = 1 
WHERE age > 25 AND status = 0;

-- 使用IN条件
UPDATE users 
SET status = 0 
WHERE id IN (1, 2, 3);

-- 使用BETWEEN条件
UPDATE users 
SET status = 1 
WHERE age BETWEEN 20 AND 30;

-- 更新满足子查询条件的记录
UPDATE users 
SET status = 0 
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE total_amount > 10000
);

-- 使用EXISTS
UPDATE users u
SET status = 1
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.status = 'completed'
);

-- 年龄加1
UPDATE users SET age = age + 1 WHERE id = 1;

-- 年龄减1
UPDATE users SET age = age - 1 WHERE id = 1;

-- 价格打折
UPDATE products SET price = price * 0.9 WHERE category = 'electronics';

-- 字符串拼接
UPDATE users SET name = CONCAT(name, ' Jr') WHERE age < 20;

-- 字符串替换
UPDATE users 
SET email = REPLACE(email, 'old.com', 'new.com');

-- 大小写转换
UPDATE users SET name = UPPER(name) WHERE id = 1;
UPDATE users SET name = LOWER(name) WHERE id = 2;

-- 更新为当前时间
UPDATE users SET updated_at = NOW() WHERE id = 1;

-- 日期加减
UPDATE subscriptions 
SET expire_date = DATE_ADD(expire_date, INTERVAL 30 DAY)
WHERE user_id = 1;

-- 设置为当前日期
UPDATE users SET last_login = CURDATE() WHERE id = 1;

-- 根据关联表更新
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.status = 1
WHERE o.total_amount > 1000;

-- LEFT JOIN更新
UPDATE users u
LEFT JOIN profiles p ON u.id = p.user_id
SET u.status = 0
WHERE p.id IS NULL;

UPDATE orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
SET o.status = 'completed'
WHERE u.status = 1 AND p.stock > 0;

-- 只更新前10条
UPDATE users 
SET status = 0 
WHERE status = 1 
LIMIT 10;

-- 按创建时间更新最早的10条
UPDATE users 
SET status = 1 
WHERE status = 0 
ORDER BY created_at ASC 
LIMIT 10;

-- 使用CASE WHEN
UPDATE users 
SET status = CASE
    WHEN age < 18 THEN 0
    WHEN age >= 18 AND age < 60 THEN 1
    ELSE 2
END;

-- 使用IF函数
UPDATE users 
SET status = IF(age >= 18, 1, 0);

-- 更新NULL值
UPDATE users SET age = 0 WHERE age IS NULL;

-- 使用IFNULL
UPDATE users 
SET name = IFNULL(name, 'Unknown');

-- 使用COALESCE
UPDATE users 
SET email = COALESCE(email, 'no-email@example.com');

-- 更新JSON字段
UPDATE users 
SET profile = JSON_SET(profile, '$.age', 25)
WHERE id = 1;

-- 更新JSON数组
UPDATE users 
SET tags = JSON_ARRAY_APPEND(tags, '$', 'new_tag')
WHERE id = 1;

-- 删除JSON字段
UPDATE users 
SET profile = JSON_REMOVE(profile, '$.temp_field')
WHERE id = 1;

-- 查看安全模式状态
SHOW VARIABLES LIKE 'sql_safe_updates';

-- 开启安全模式
SET sql_safe_updates = 1;

-- 关闭安全模式
SET sql_safe_updates = 0;

-- 安全模式下，必须使用键列作为条件
-- 错误示例：
UPDATE users SET status = 0;
-- ERROR 1175 (HY000): You are using safe update mode

-- 正确示例：
UPDATE users SET status = 0 WHERE id = 1;

-- 更新前先查询
SELECT * FROM users WHERE status = 0;

-- 确认数量
SELECT COUNT(*) FROM users WHERE status = 0;

-- 使用事务
START TRANSACTION;
UPDATE users SET status = 1 WHERE status = 0;
-- 确认结果
SELECT * FROM users WHERE status = 0;
-- 确认无误后提交
COMMIT;
-- 如有问题回滚
ROLLBACK;

-- 确保WHERE条件使用索引
-- 查看执行计划
EXPLAIN UPDATE users SET status = 1 WHERE email = 'john@example.com';

-- 如果email有索引，更新更快

-- 大批量更新分批进行
-- 每次更新1000条
UPDATE users 
SET status = 1 
WHERE status = 0 
LIMIT 1000;

-- 循环执行直到影响行数为0

-- 不推荐：无索引条件的更新
UPDATE users SET status = 1 WHERE name LIKE '%John%';

-- 推荐：使用索引条件
UPDATE users SET status = 1 WHERE id IN (
    SELECT id FROM users WHERE name LIKE '%John%'
);

-- 错误信息
UPDATE users SET email = 'jane@example.com' WHERE id = 1;
-- ERROR 1062 (23000): Duplicate entry 'jane@example.com' for key 'uk_email'

-- 解决方案：检查唯一值
SELECT id FROM users WHERE email = 'jane@example.com';

-- 错误信息
UPDATE orders SET user_id = 999 WHERE id = 1;
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

-- 解决方案：确保外键值存在
SELECT id FROM users WHERE id = 999;

-- 错误信息
UPDATE users SET age = 'abc' WHERE id = 1;
-- ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'age'

-- 解决方案：提供正确类型
UPDATE users SET age = 25 WHERE id = 1;

UPDATE users SET status = 1 WHERE id = 1;
-- Query OK, 1 row affected

-- 在应用程序中获取
-- PHP: $affected = $pdo->rowCount();
// Java: int affected = statement.executeUpdate();

UPDATE users SET age = 200 WHERE id = 1;
SHOW WARNINGS;