[laravel] 라라벨 여러 row 한번에 업데이트 하는 법 mass update

종종 DB 테이블의 여러 row 에 대해 업데이트가 필요한 경우가 있는데 보통 다음과 같을 것이다.

 

1. 여러 post 의 value 를 하나의 값으로 업데이트 해야 하는 경우

2. 여러 post 의 value 를 각각의 값으로 업데이트 해야 하는 경우

 

업데이트시 row 의 갯수 만큼 n 번 트랜잭션을 발생시키지 않고 한번에 처리하는게 오버헤드도 줄일수 있고 성능상 유리하다.

 

1. 번 같은 경우, 아래와 같이 처리할수 있고 Eloquent 빌더, 쿼리빌더 모두 간편하게 구현가능하다.

        Post::where('code', 'A')->update([
            'category' => 'new category'
        ]);
        DB::table('posts')->where('code', 'A')->update([
            'category' => 'new category'
        ]);

        Post::whereIn('code', ['A', 'B'])->update([
            'category' => 'new category'
        ]);
        DB::table('posts')->whereIn('code', ['A', 'B'])->update([
            'category' => 'new category'
        ]);

 

 

하지만 2. 번 같은 경우 Eloquent 빌더에서 지원해주지 않기 때문에 update case 문을 활용하여 Query 빌더와 함께 이용하도록 하자.

 

우선 sql 문으로 2. 번의 경우를 작성해보면 다음과 같다.

UPDATE db.posts
SET category = CASE
              WHEN code = A THEN 'new category1'
              WHEN code = B THEN 'new category2'
           END
WHERE code IN ('A', 'B')

 

그리고 위 update case 문을 쿼리빌더와 함께 이용하면 다음과 같이 적용할수 있다.

 

        $cases = [];
        $params = [];
        $inParams = [];
        $postsWithNewCategories = [
            'A' => 'new category1',
            'B' => 'new category2',
        ];
        foreach ($postsWithNewCategories as $code => $category) {
            $cases[] = "WHEN ? THEN ?";
            $params[] = $code;
            $params[] = $category;
            $inParams[] = $code;
        }

        $cases = implode(' ', $cases);
        $inCodes = implode(',', array_fill(0, count($inParams), '?'));

        $allParams = array_merge($params, $inParams);
        $sql = "UPDATE posts SET category = CASE code {$cases} END WHERE code IN ({$inCodes})";

        DB::update($sql, $allParams);

 

 

유의해야 할 점은 update 해야 하는 데이터가 많을때 sql 문의 길이가 길어져 mysql 에서 한번에 처리 못할수도 있다. 이럴 경우 chunk 를 사용하여 분할해서 처리해주도록 해야한다.

 

관련하여 mysql 와 클라이언트 사이에서 주고 받을수있는 최대 packet 사이즈는 다음 명령어를 통해 확인할수 있으니 참고하자.

show variables like 'max_allowed_packet';