MySQL批量新增和批量更新

注意事项：mysql语句长度有限制，并不是越长越好，具体可以查看mysql的配置文件（Windows系统下是my.ini，Linux系统下是my.cnf）中max_allowed_packet属性（默认是1M），并进行相应设置（比如max_allowed_packet = 20M）；

---

批量新增

SQL语句示例

INSERT INTO table_name (col_name1, col_name2, ...) VALUES (col_value1,col_value2, ...),(col_value1,col_value2, ...);

PHP代码示例

$users = array( 
    array('name' => 'test', 'age' => 20),
    array('name' => 'test1', 'age' => 21),
    array('name' => 'test2', 'age' => 22)
); 
$sql = "INSERT INTO users (name, age) VALUES "; 
foreach ($users as $key => $value) {
    $key != 0 && $sql .= ',';
    $sql .= sprintf("('%s',%s)", $value['name'], $value['age']); 
}
echo $sql;

---

批量更新

CASE WHEN批量更新
SQL语句示例

UPDATE table_name 
    SET update_col1 = CASE where_col1 
        WHEN where_val1 THEN update_val1 
        WHEN where_val2 THEN update_val2 
        WHEN where_val3 THEN update_val3 
    END, 
    update_col2 = CASE where_col2 
        WHEN where_val4 THEN update_val4
        WHEN where_val5 THEN update_val5
    END
WHERE where_col1 IN (where_val1,where_val2,where_val3);

PHP代码示例

//注意事项：CASE WHEN批量更新方法比较适合更新单个字段的业务场景，当业务需要更新多个字段时用CASE WHEN批量更新方法PHP实现不方便，需要多个循环拼接SQL语句
$display_order = array( 
    1 => 4, 
    2 => 1, 
    3 => 2, 
    4 => 3, 
    5 => 9, 
    6 => 5, 
    7 => 8, 
    8 => 9 
); 
$ids = implode(',', array_keys($display_order)); 
$sql = "UPDATE categories SET display_order = CASE id "; 
foreach ($display_order as $id => $ordinal) { 
    $sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal); 
} 
$sql .= "END WHERE id IN ($ids)"; 
echo $sql;

ON DUPLICATE KEY UPDATE批量更新（推荐）
SQL语句示例

INSERT INTO table_name (`id`, `col_name1`, `col_name2`)
VALUES
(1, 'col1_val1', 'col2_val1'),
(2, 'col_val2', 'col2_val2') 
ON DUPLICATE KEY UPDATE 
col_name1 = VALUES(col_name1),
col_name2 = VALUES(col_name2);

PHP代码示例

//ON DUPLICATE KEY UPDATE批量更新方法既适用于单个字段更新也适用于多个字段更新
$users = array( 
    array('id' => 1, 'name' => 'test', 'age' => 20),
    array('id' => 2, 'name' => 'test2', 'age' => 22),
    array('id' => 3, 'name' => 'test3', 'age' => 23)
); 
$sql = "INSERT INTO users (`id`, `name`, `age`) VALUES "; 
foreach ($users as $key => $value) { 
	$key != 0 && $sql .= ',';
    $sql .= sprintf("(%d,%s,%s)", $value['id'], $value['name'], $value['age']); 
} 
$sql .= " ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age); "; 
echo $sql;