mysql关联表批量更新数据库表

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    status VARCHAR(20)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_status VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入示例数据
INSERT INTO users (id, name, status) VALUES (1, 'Alice', 'active');
INSERT INTO orders (id, user_id, order_status) VALUES (101, 1, 'pending'), (102, 1, 'pending');

-- 批量更新用户及其订单状态
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.status = 'inactive',
    o.order_status = 'cancelled'
WHERE u.id = 1;

START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET status = 'inactive' WHERE id = 1;
UPDATE orders SET order_status = 'cancelled' WHERE user_id = 1;
COMMIT;

-- 分批次更新示例
SET @batch_size = 100;
SET @offset = 0;

WHILE (@offset < (SELECT COUNT(*) FROM users)) DO
    UPDATE users u
    JOIN orders o ON u.id = o.user_id
    SET u.status = 'inactive',
        o.order_status = 'cancelled'
    WHERE u.id IN (
        SELECT id FROM users LIMIT @batch_size OFFSET @offset
    );
    SET @offset = @offset + @batch_size;
END WHILE;