MySQL JDBC 的 BATCH 執行和 rewriteBatchedStatements 參數

本來以為這是一個已解決的問題，但是發現有同學不知道，是以寫一下。

經常使用 MySQL 的同學可能知道，預設情況下 MySQL JDBC 驅動是不支援 BATCH 的：

try (Connection conn = dataSource.getConnection();
    Statement stmt = conn.createStatement()) {
    stmt.addBatch("INSERT INTO test (id, name, number, gmt) VALUES (1, 'Adam',  100, NOW())");
    stmt.addBatch("INSERT INTO test (id, name, number, gmt) VALUES (2, 'Brown', 200, NOW())");
    stmt.addBatch("INSERT INTO test (id, name, number, gmt) VALUES (3, 'Clair', 300, NOW())");
    ...
    stmt.executeBatch();  
} catch (SQLException e) {
    e.printStackTrace();
}           

在真正執行的時候，MySQL JDBC 驅動仍然是把三條語句順序發給 MySQL 執行。這樣其實就是沒有 BATCH —— 既沒有降低網絡通訊的成本，也不能在服務端批量執行。

是以 MySQL JDBC 提供了一個參數 rewriteBatchedStatements 改善這個行為。設定 rewriteBatchedStatements=true 以後，MySQL JDBC 會以兩種方式重寫批量送出的 SQL 語句，實作一次送達，批量執行的目标：

1） INSERT / INSERT ON DUPLICATE KEY UPDATE / REPLACE (5.1.37+) 會重寫成 Multi-Values 的形式，但是限制是必須是使用 PreparedStatement 批量執行的語句。這個限制很容易了解，因為 Statement 提供的 BATCH 接口不能保證批量執行的語句全部是相同類型。

INSERT INTO test (id, name, number, gmt) VALUES 
    (1, 'Adam',  100, NOW()), 
    (2, 'Brown', 200, NOW()), 
    (3, 'Clair', 300, NOW()), 
    ... 
[ ON DUPLICATE KEY UPDATE 
    name   = VALUES(name), 
    number = VALUES(number), 
    gmt    = VALUES(gmt), 
    ... ]           

2）重寫成 Multi-Query 的形式，簡單來說就是在 SQL 間加入分号，合并成一條多語句發給 MySQL 服務端。所有不符合 1） 的都會重寫成多語句，但是多語句在 MySQL 服務端的執行效率較低：

UPDATE test SET name = 'Adam',  number = 100, gmt = NOW() WHERE id = 1;
UPDATE test SET name = 'Brown', number = 200, gmt = NOW() WHERE id = 2;
UPDATE test SET name = 'Clair', number = 300, gmt = NOW() WHERE id = 3;
...           

特别要注意的一點是：MySQL JDBC 版本在 5.1.37 以下不支援 REPLACE 的 Multi-Values 重寫。是以批量 REPLACE 會使用多語句的方式發送到 MySQL 服務端，會觀察到 REPLACE 的批量寫入效率嚴重低于 INSERT 和 INSERT ON DUPLICATE KEY UPDATE。

有關 MySQL BATCH 的主要問題就這些，謝謝，做了一些微小的工作。