MariaDB数据更新 #

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

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    age INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    balance DECIMAL(10,2) DEFAULT 0.00,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO users (name, email, age, status, balance) VALUES
    ('John', 'john@example.com', 25, 1, 1000.00),
    ('Jane', 'jane@example.com', 30, 1, 2000.00),
    ('Bob', 'bob@example.com', 28, 0, 500.00),
    ('Alice', 'alice@example.com', 22, 1, 1500.00);

-- 更新单个字段
UPDATE users SET age = 26 WHERE id = 1;

-- 更新后查看
SELECT * FROM users WHERE id = 1;
+----+------+-------------------+------+--------+---------+---------------------+
| id | name | email             | age  | status | balance | updated_at          |
+----+------+-------------------+------+--------+---------+---------------------+
|  1 | John | john@example.com  |   26 |      1 | 1000.00 | 2024-01-15 10:30:00 |
+----+------+-------------------+------+--------+---------+---------------------+

-- 更新多个字段
UPDATE users 
SET age = 27, 
    email = 'john.new@example.com'
WHERE id = 1;

-- 使用表达式
UPDATE users 
SET balance = balance + 100,
    updated_at = NOW()
WHERE id = 1;

-- 使用WHERE条件
UPDATE users SET status = 0 WHERE age > 30;

-- 使用多个条件
UPDATE users 
SET status = 1 
WHERE age >= 18 AND age <= 60;

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

-- 使用BETWEEN
UPDATE users 
SET balance = balance * 1.1 
WHERE age BETWEEN 25 AND 35;

-- 使用LIKE
UPDATE users 
SET status = 0 
WHERE email LIKE '%@test.com';

-- 危险：没有WHERE条件会更新所有行
UPDATE users SET status = 0;  -- 更新所有行！

-- 安全做法：始终使用WHERE条件
UPDATE users SET status = 0 WHERE id = 1;

-- 启用安全更新模式
SET sql_safe_updates = 1;

-- 安全模式下，必须使用KEY或LIMIT
UPDATE users SET status = 0 WHERE id = 1;  -- OK
UPDATE users SET status = 0 WHERE name = 'John';  -- 错误
UPDATE users SET status = 0 LIMIT 1;  -- OK

-- 限制更新的行数
UPDATE users 
SET status = 0 
WHERE status = 1 
LIMIT 10;

-- 分批更新（避免锁表时间过长）
-- 每次更新1000行
UPDATE users 
SET status = 0 
WHERE status = 1 
LIMIT 1000;

-- 按顺序更新
UPDATE users 
SET status = 0 
ORDER BY created_at DESC 
LIMIT 10;

-- 更新最旧的记录
UPDATE users 
SET status = 1 
ORDER BY created_at ASC 
LIMIT 5;

-- 增加余额
UPDATE users SET balance = balance + 100 WHERE id = 1;

-- 减少余额
UPDATE users SET balance = balance - 50 WHERE id = 1;

-- 乘法
UPDATE users SET balance = balance * 1.1 WHERE status = 1;

-- 除法
UPDATE users SET balance = balance / 2 WHERE status = 0;

-- 字符串连接
UPDATE users 
SET email = CONCAT(name, '@company.com')
WHERE email IS NULL;

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

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

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

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

-- 使用NULLIF
UPDATE users 
SET email = NULLIF(email, '');

-- 更新日期
UPDATE users 
SET updated_at = NOW()
WHERE id = 1;

-- 日期计算
UPDATE users 
SET expiry_date = DATE_ADD(expiry_date, INTERVAL 30 DAY)
WHERE status = 1;

-- 使用当前日期
UPDATE users 
SET last_login = CURRENT_TIMESTAMP
WHERE id = 1;

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    status VARCHAR(20)
);

-- 使用JOIN更新
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.balance = u.balance + o.amount
WHERE o.status = 'completed';

-- 使用LEFT JOIN
UPDATE users u
LEFT JOIN orders o ON u.id = o.user_id
SET u.status = 0
WHERE o.id IS NULL;

-- 更新多个表
UPDATE users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
SET u.balance = u.balance - o.amount,
    o.status = 'paid'
WHERE o.id = 1;

-- 使用子查询更新
UPDATE users 
SET balance = (
    SELECT SUM(amount) 
    FROM orders 
    WHERE user_id = users.id AND status = 'completed'
)
WHERE id = 1;

-- 使用子查询作为条件
UPDATE users 
SET status = 0 
WHERE id IN (
    SELECT user_id FROM orders WHERE status = 'cancelled'
);

-- 使用子查询计算值
UPDATE users 
SET balance = (
    SELECT COALESCE(SUM(amount), 0) 
    FROM orders 
    WHERE user_id = users.id
);

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

-- 更新每个用户的订单数
UPDATE users u
SET order_count = (
    SELECT COUNT(*) 
    FROM orders o 
    WHERE o.user_id = u.id
);

-- 更新用户的最新订单日期
UPDATE users u
SET last_order_date = (
    SELECT MAX(created_at) 
    FROM orders o 
    WHERE o.user_id = u.id
);

-- 创建包含JSON的表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES
    ('Laptop', '{"brand": "Dell", "ram": "16GB", "price": 1000}');

-- 更新整个JSON
UPDATE products 
SET attributes = '{"brand": "HP", "ram": "32GB", "price": 1200}'
WHERE id = 1;

-- 使用JSON函数更新
UPDATE products 
SET attributes = JSON_SET(attributes, '$.price', 1100)
WHERE id = 1;

-- 添加新属性
UPDATE products 
SET attributes = JSON_SET(attributes, '$.color', 'black')
WHERE id = 1;

-- 删除属性
UPDATE products 
SET attributes = JSON_REMOVE(attributes, '$.ram')
WHERE id = 1;

-- 更新数组元素
UPDATE products 
SET attributes = JSON_SET(attributes, '$.tags[0]', 'new_tag')
WHERE id = 1;

-- 追加数组元素
UPDATE products 
SET attributes = JSON_ARRAY_APPEND(attributes, '$.tags', 'tag3')
WHERE id = 1;

-- 插入数组元素
UPDATE products 
SET attributes = JSON_ARRAY_INSERT(attributes, '$.tags[0]', 'first_tag')
WHERE id = 1;

-- MariaDB 10.5+ 支持RETURNING
UPDATE users 
SET balance = balance + 100 
WHERE id = 1
RETURNING id, name, balance;

+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | John | 1100.00 |
+----+------+---------+

-- 低优先级更新
UPDATE LOW_PRIORITY users 
SET status = 0 
WHERE id = 1;

-- 等待所有读取完成后执行

-- 忽略错误继续更新
UPDATE IGNORE users 
SET email = 'duplicate@example.com'
WHERE id = 1;

-- 查看警告
SHOW WARNINGS;

-- 开始事务
START TRANSACTION;

-- 执行更新
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 或回滚
ROLLBACK;

-- 使用版本号实现乐观锁
UPDATE users 
SET balance = balance + 100,
    version = version + 1
WHERE id = 1 AND version = 5;

-- 检查是否更新成功
SELECT ROW_COUNT();
-- 返回1表示成功，0表示失败（版本不匹配）

-- 确保WHERE条件使用索引
-- 创建索引
CREATE INDEX idx_users_status ON users(status);

-- 使用索引的更新
UPDATE users SET balance = 0 WHERE status = 1;

-- 查看执行计划
EXPLAIN UPDATE users SET balance = 0 WHERE status = 1;

-- 大表分批更新
-- 方法1：使用LIMIT循环
-- 每次更新1000行
UPDATE users 
SET status = 0 
WHERE status = 1 
LIMIT 1000;

-- 方法2：使用ID范围
UPDATE users 
SET status = 0 
WHERE id BETWEEN 1 AND 1000 AND status = 1;

-- 方法3：使用存储过程
DELIMITER //
CREATE PROCEDURE batch_update()
BEGIN
    DECLARE done INT DEFAULT 0;
    WHILE done = 0 DO
        UPDATE users SET status = 0 WHERE status = 1 LIMIT 1000;
        IF ROW_COUNT() = 0 THEN
            SET done = 1;
        END IF;
        DO SLEEP(0.1);  -- 短暂休息
    END WHILE;
END //
DELIMITER ;

-- 避免：没有索引的条件
UPDATE users SET status = 0 WHERE YEAR(created_at) = 2024;

-- 优化：使用范围条件
UPDATE users SET status = 0 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 检查WHERE条件
SELECT * FROM users WHERE id = 999;  -- 确认数据存在

-- 检查权限
SHOW GRANTS FOR CURRENT_USER();

-- 检查事务
SELECT @@autocommit;  -- 是否自动提交

-- 查看锁等待
SHOW PROCESSLIST;

-- 查看锁信息
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

-- 终止阻塞的进程
KILL <process_id>;

-- 设置锁等待超时
SET innodb_lock_wait_timeout = 50;  -- 默认50秒

-- 查看当前设置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 创建更新日志表
CREATE TABLE user_update_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    old_balance DECIMAL(10,2),
    new_balance DECIMAL(10,2),
    updated_by VARCHAR(50),
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 使用触发器记录更新
DELIMITER //
CREATE TRIGGER log_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.balance != NEW.balance THEN
        INSERT INTO user_update_log (user_id, old_balance, new_balance, updated_by)
        VALUES (NEW.id, OLD.balance, NEW.balance, CURRENT_USER());
    END IF;
END //
DELIMITER ;