您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 查詢結果寫入新表：UPDATE JOIN 與 INSERT SELECT 實戰

編輯：MySQL 資料庫相關知識

準備測試數據

CREATE TABLE t1 (id INT PRIMARY KEY, username VARCHAR(20), score INT);
CREATE TABLE t2 (id INT PRIMARY KEY, username VARCHAR(20), score INT);

INSERT INTO t1 VALUES (1, 'alice', NULL), (2, 'bob', NULL), (3, 'charlie', 90);
INSERT INTO t2 VALUES (1, 'alice_new', 85), (2, 'bob_new', 95), (4, 'david', 88);

場景一：條件更新（多表 UPDATE）

-- t2 有分數則更新到 t1
UPDATE t1
JOIN t2 ON t1.id = t2.id
SET t1.score = t2.score
WHERE t2.score IS NOT NULL;

-- 結果：t1.id=1 score=85, id=2 score=95, id=3 保持 90

場景二：雙向同步更新

-- t1 分數→t2，t2 用戶名→t1
UPDATE t1
JOIN t2 ON t1.id = t2.id
SET t1.score = t2.score,
    t2.username = t1.username;

場景三：查詢結果插入新表（INSERT SELECT）

-- 將 t2 某用戶數據插入 t1
INSERT INTO t1 (id, username, score)
SELECT id, username, score FROM t2 WHERE username = 'lucy';

場景四：異表結構字段映射插入

CREATE TABLE t3 (user_id INT, user_name VARCHAR(30), user_score INT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP);

INSERT INTO t3 (user_id, user_name, user_score)
SELECT id, username, score FROM t2 WHERE score > 90;

核心語法對比

操作	語法	適用場景	性能特點
更新現有行	UPDATE t1 JOIN t2 ... SET ...	主鍵/唯一鍵關聯更新	單條 SQL，避免應用層循環
插入新行	INSERT INTO t1 SELECT ... FROM t2	全量/增量同步、歸檔	服務端直連，無網絡往返
覆蓋寫入	INSERT ... ON DUPLICATE KEY UPDATE	存在更新不存在插入	原子化 Upsert
替換寫入	REPLACE INTO t1 SELECT ...	主鍵衝突刪舊插新	觸發 DELETE+INSERT，慎用自增

進階：ON DUPLICATE KEY UPDATE 實戰

-- 增量同步：主鍵衝突則更新分數與用戶名
INSERT INTO t1 (id, username, score)
SELECT id, username, score FROM t2
ON DUPLICATE KEY UPDATE
    username = VALUES(username),
    score = VALUES(score);

大數據量分批處理模板

-- 避免長事務鎖表，分批提交
DELIMITER //
CREATE PROCEDURE batch_sync()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM t2 WHERE id > 10000 LIMIT 1000;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    REPEAT
        FETCH cur INTO v_id;
        IF NOT done THEN
            INSERT INTO t1 (id, username, score)
            SELECT id, username, score FROM t2 WHERE id = v_id
            ON DUPLICATE KEY UPDATE username=VALUES(username), score=VALUES(score);
        END IF;
    UNTIL done END REPEAT;
    CLOSE cur;
END //
DELIMITER ;