PHP配上MySQL實現批量更新插入

Chaos發表於2019-02-16
PHPMySql

單資料小量資料的更新於插入，操作起來簡單，不用太考慮SQL語句怎麼書寫。正常來說，按照條件判斷資料是否已經在表中存在，存在更新，不存在插入。如果是N條資料的話，執行的SQL語句最大值為2N條。當N數值不大的時候，可以用這種簡單的方式去更新插入。但是當N資料量不可預估，或者很大的時候，這麼做肯定不行，在執行這個操作的時候，可能會將MySQL的連線數佔完，造成系統資源被消耗完，影響其他操作。

批量插入

以MySQL舉例，網路上有很多的SQL例子：
一下為兩種批量插入SQL

insert into table （ `column1`,`column2`,`column3` ）values (1,2,3),(2,3,4),(5,6,7)
insert into table （ `column1`,`column2`,`column3` ）select 1,2,3 union all select 2,3,4 union all select 4,5,6

下面是在MySQL5.79中以第二種方式分三次插入2527條資料所用耗時，以供參考

[2018-12-05 10:58:59] 1000 rows affected in 452 ms
[2018-12-05 11:04:54] 1000 rows affected in 389 ms
[2018-12-05 11:06:12] 527 rows affected in 163 ms

批量更新

批量更新也有幾種方式，
一：

replace into table （ `column1`,`column2`,`column3` ）values (1,2,3),(2,3,4),(5,6,7)

這種相當於將資料庫的資料與需要插入的資料做交集，將交集資料刪除再重新插入。表中的自增id會改變，當沒有另外的與資料相關的唯一鍵或表中有其他資料需要積累或與其他表有關聯關係的時候，不適用。
這篇文章有詳細的介紹

二：

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;
UPDATE t1 SET c=c+1 WHERE a=1;

這種更新，需要唯一鍵來限制，同時更新的欄位的值限制比較大，不能自定需要更新欄位的數值。只能按照一定邏輯去更新，或許可以用於簽到計數類似的場景。
這篇文章有詳細的介紹

三：

update table set 
column1 = case  when column2 = 1 then 2 else 3 end ,
column3 = case  when column4 = 1 then 2 else 3 end 
where id in (1,2,3,4)

利用了MySQL的case when 函式，可以按照不同的條件去為不同的欄位更新不同的值,同時可以給語句限定不同的條件。

下面用PHP實現以下拼裝SQL語句過程：

//拼裝按條件批量更新SQL語句
protected function handleUpdate($data, $key)
    {
        if (empty($data) || !is_array($data)) {
            return false;
        }

        $keys_array = array_keys(current($data));

//需要被更新的欄位
        $update_column = [
            $keys_array[5],
            $keys_array[6],
            $keys_array[8],
            $keys_array[9],
            $keys_array[16],
            $keys_array[14]
        ];

//更新條件
        $vehicle_id = $keys_array[2];
        $body_color = $keys_array[3];
        $interior_color = $keys_array[4];
        $city_id = $keys_array[11];
        $province_id = $keys_array[10];

        $q = "UPDATE ce SET ";
        foreach ($update_column as $update_colum) {
            $q .= ` ` . $update_colum . ` = CASE `;
            foreach ($data as $value) {
                $value[$update_colum] = !isset($value[$update_colum]) || empty($value[$update_colum])
                    ? 0 : $value[$update_colum];
                $q .= ` when ` . $vehicle_id . ` =` . $value[$vehicle_id]
                    . ` and ` . $body_color . ` = "` . $value[$body_color] . `"`
                    . ` and ` . $interior_color . ` = "` . $value[$interior_color] . `"`
                    . ` and ` . $city_id . ` =` . $value[$city_id]
                    . ` and ` . $province_id . ` =` . $value[$province_id] . ` then ` . $value[$update_colum];
            }
            $q .= " ELSE " . $update_colum . " END, ";
        }
        $q = rtrim($q, ", ");
    }

表結構如下

-- auto-generated definition
create table cd
(
  id                int auto_increment
    primary key,
  brand_id          int default `0`              not null
  comment `車輛品牌id`,
  model_id          int default `0`              not null
  comment `車輛車型id`,
  vehicle_id        int default `0`              not null
  comment `車輛型號id`,
  body_color        varchar(50) default ``       not null
  comment `車身外觀顏色`,
  interior_color    varchar(50) default ``       not null
  comment `車輛內飾顏色`,
  guide_price       decimal(8, 2) default `0.00` not null
  comment `指導價`,
  province_id       int default `0`              not null
  comment `省份id`,
  province          varchar(50) default ``       not null
  comment `省份名稱`,
  city_id           int default `0`              not null
  comment `地級市id`,
  city              varchar(50) default ``       not null
  comment `地級市名稱`,
  report_price      decimal(8, 2) default `0.00` not null
  comment `集團報備價`,
  average_price     decimal(8, 2) default `0.00` not null
  comment `平均價`,
  coefficient       float(5, 2) default `0.00`   not null
  comment `係數`,
  price_increase    decimal(8, 2) default `0.00` not null
  comment `加價額`,
  operator          int default `0`              not null
  comment `操作者id`,
  created_at        int default `0`              not null
  comment `建立時間`,
  updated_at        int default `0`              not null
  comment `更新時間`,
  constraint unique__index
  unique (vehicle_id, body_color, interior_color, city_id, province_id)
)
  comment ``;

判斷需要插入與更新的資料

一：保證資料唯一，只需要確保一個值即可
例如，只需要保證身份證唯一，即更新的查詢條件只有身份證一個欄位的。簡單處理可以拿出陣列中的值，然後查詢資料庫，判斷是否存在。

$where_in = array_column($array,`column`);//全部資料的搜尋條件;

$res = mysql_execute(`select column from table where column in (`.$where_in.`)`);//MySQL執行

$res_in = array_column($res,`column`);//按搜尋條件查詢已經在資料庫中的資料;

$update_arr = array_diff($where_in,$res_in);//獲得不在資料庫中的資料

$update_info = array_filter($array,function($item) use($update_arr){
    return in_array($item[`column`],$update_arr) ? true : false;
});//獲取更新資料

$insert_info = array_filter($array,function($item) use($res_in){
    return in_array($item[`column`],$update_arr) ? true : false;
});//獲取插入資料

二：多條件篩選
我的解決思路和上面差不多，只是不能從資料庫用 where colum in 這樣的條件去篩選，主要是考慮到索引的問題，在表資料很大的時候，不合適。

所以利用Redis的set來解決，在set中記錄查詢條件，只需要遍歷陣列，判斷當前資料中的搜尋條件是否在Redis鍵中即可。

主要是利用第三方來儲存條件，當然也可以使用MySQL，保證查詢效率即可。

附加優化

一：避免載入的陣列太大，佔用記憶體，可以使用yield進行切片，每次輸出定量的資料，然後遍歷執行。

二：可以使用佇列，分批次執行任務。利用Redis或者RabbitMQ等。

三：似乎可以嘗試一下swoole的協程，好像很厲害的樣子。

以上是我在做專案是遇到的問題與我的思路，有不同的思路或者覺得不對不合適的地方歡迎提出。

相關文章