programming

Chèn hoặc cập nhật trình tạo truy vấn Laravel

Phát hành vào ngày 13 tháng 10 năm 2020, Laravel 8. 10 đi kèm với việc bổ sung phương thức

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

6 mới trên trình tạo truy vấn cơ sở dữ liệu [và do đó, các truy vấn dựa trên Eloquent cũng vậy]. Điều này cho phép bạn thực hiện các truy vấn "chèn hoặc cập nhật" hiệu quả hơn nhiều - điều này sẽ hữu ích khi nhập các lô dữ liệu lớn

TLDR. Tại sao sử dụng nó?, Nó hoạt động như thế nào?, Nó hoạt động như thế nào?

Một ví dụ thực tế về việc sử dụng upsert[]

Cho đến thời điểm hiện tại, trình tạo truy vấn cơ sở dữ liệu của Laravel đã hỗ trợ việc chèn hoặc cập nhật nhiều hàng [lô dữ liệu] cùng một lúc thông qua các phương thức

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

7 và

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

8. Ngoài ra còn có một phương pháp

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

9 ít được biết đến hơn sẽ chèn một loạt dữ liệu, bỏ qua mọi lỗi [chẳng hạn như một khóa duy nhất đã tồn tại]

Chúng tôi cũng có phương pháp

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

0 trên các mô hình Eloquent cung cấp một tập hợp các thuộc tính, cập nhật một mô hình hiện có hoặc nếu nó không tồn tại, nó sẽ tạo ra nó. Tuy nhiên, điều này chỉ có thể được sử dụng trên một hàng/mô hình tại một thời điểm - nếu chúng tôi có 125.000 hàng để chèn hoặc cập nhật, chúng tôi sẽ phải gọi phương thức Eloquent đó 125.00 lần. Đằng sau hậu trường, điều này thực sự thực hiện hai truy vấn trong cơ sở dữ liệu - một để kiểm tra mục hiện có và một để thực hiện

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

1 hoặc

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

0 - vì vậy điều đó có nghĩa là chạy 250.000 truy vấn. Điều đó có thể chậm

Vì vậy, hãy tưởng tượng chúng ta có một tệp CSV chứa dữ liệu cho 125.000 sản phẩm mà chúng ta cần nhập vào cơ sở dữ liệu của mình một cách hiệu quả nhất có thể. Không vấn đề gì, chúng ta có thể sử dụng phương thức

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

7 trên trình tạo truy vấn để chèn nhiều hàng cùng một lúc

Chuyển nhanh đến 4 tuần sau, chúng tôi có tệp CSV mới về dữ liệu sản phẩm - chi tiết của 125.000 sản phẩm đã được cập nhật và 25.000 sản phẩm mới đã được thêm vào. Trong cơ sở dữ liệu của chúng tôi, chúng tôi cần cập nhật các sản phẩm hiện có và chèn những sản phẩm mới. Để làm điều này, chúng tôi cần biết sản phẩm nào chúng tôi đã có và sản phẩm nào mới để chúng tôi có thể thực hiện truy vấn

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

1 hoặc

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

Thay vì sử dụng

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

0 150.000 lần cho mỗi hàng trong tệp CSV, hãy bắt đầu bằng cách chia nhỏ tệp CSV thành các lô nhỏ hơn, chẳng hạn như 5.000 sản phẩm. Điều đó mang lại cho chúng tôi 30 đợt

Sau đó, chúng tôi có thể chuyển từng lô dữ liệu sản phẩm sang phương thức

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

6 mới trên lớp

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

6, phương thức này đối với mỗi hàng dữ liệu trong tệp CSV sẽ cập nhật sản phẩm hiện có trong cơ sở dữ liệu của chúng tôi hoặc tạo một sản phẩm mới. Bằng cách này, chúng tôi sẽ chỉ thực hiện 30 truy vấn thay vì 300.000 nếu chúng tôi sử dụng phương pháp

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

0 [hãy nhớ rằng thực tế là 2 truy vấn cho mỗi lần sử dụng phương pháp này]

Vậy upsert[] hoạt động như thế nào?

Tôi sẽ cho rằng bạn đang sử dụng MySQL, mặc dù nó hoạt động với các công cụ cơ sở dữ liệu khác

Theo ví dụ của chúng tôi về cơ sở dữ liệu sản phẩm, được đơn giản hóa cho một lô chỉ 3 sản phẩm, đây là SQL sẽ được chạy

INSERT INTO products 
[item_ref, title, price] 
VALUES 
[1, 'Large cake', 15.00], 
[1, 'Medium cake', 10.00], 
[1, 'Small cake', 5.00] 
ON DUPLICATE KEY 
UPDATE 
item_ref = values[item_ref], 
title = values[title], 
price = values[price];

Như bạn có thể thấy, truy vấn bắt đầu bằng một câu lệnh

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

1 bình thường và nó sẽ cố gắng chèn tập giá trị dữ liệu đầu tiên vào bảng. Nếu thành công, nó sẽ loại bỏ sang tập tiếp theo. Tuy nhiên, nếu điều đó không thành công vì mã định danh duy nhất [trong ví dụ của chúng tôi, đó là "item_ref"] đã tồn tại [i. hàng dữ liệu đã có trong bảng], thì nó sẽ bị bắt bởi phần

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

9 của truy vấn. SQL theo sau đó, trong trường hợp này là câu lệnh

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

0, sau đó sẽ được chạy. Điều này sẽ cập nhật hàng cơ sở dữ liệu hiện có với tập hợp các giá trị. Sau khi hoàn thành, nó sẽ chuyển sang bộ giá trị dữ liệu tiếp theo

Còn hiệu suất của upert[] thì sao?

Để kiểm tra hiệu năng của phương thức

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

6 mới, tôi đã bắt đầu một dự án Laravel mới và tạo một lệnh thủ công đơn giản để thực hiện 3 bài kiểm tra - một bài sử dụng phương thức

$uniqueBy = 'item_ref';
// OR
$uniqueBy = ['item_ref'];
// OR
$uniqueBy = ['item_type', 'item_size'];

2, một bài sử dụng phương thức

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

6 cho các hàng đơn lẻ và một bài sử dụng phương thức

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

6 với các lô

Đối với mỗi bài kiểm tra, một mảng gồm 125.000 "sản phẩm" được ghi vào một bảng trống - đo thời gian cần thiết để hoàn thành. Sau đó, mọi mặt hàng trong mảng dữ liệu sản phẩm ban đầu được cập nhật với giá mới và thêm 25.000 sản phẩm khác. Dữ liệu thô được cập nhật này sau đó cũng được ghi vào cơ sở dữ liệu trong khi được hẹn giờ

Tôi đã tạo một máy chủ mới trên Linode [với PHP 7. 4 & MySQL 8] và chạy lệnh thủ công 5 lần. Tôi tính trung bình số lần từ mỗi bài kiểm tra, kết quả như sau

Bài kiểm tra 1 [phương pháp ____20]

CHÈN. 280. 84 giây
CHÈN / CẬP NHẬT. 398. 76 giây

Bài kiểm tra 2 [phương pháp ____16 - hàng số ít]

CHÈN. 203. 99 giây
CHÈN / CẬP NHẬT. 270. 39 giây

Bài kiểm tra 3 [phương pháp ____16 - hàng loạt]

CHÈN. 15. 38 giây
CHÈN / CẬP NHẬT. 17. 97 giây

Đúng như dự đoán, các thử nghiệm đầu tiên với phương pháp

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

0 là chậm nhất. Thử nghiệm thứ hai, sử dụng

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

6 cho từng sản phẩm một lần sẽ nhanh hơn một chút vì nó chỉ thực hiện 1 truy vấn cho từng sản phẩm, thay vì 2. Cuối cùng, thử nghiệm thứ ba, sử dụng

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

6 cho lô [5.000] sản phẩm nhanh hơn rất nhiều so với hai thử nghiệm còn lại. Trong các thử nghiệm của tôi, nó hoạt động nhanh hơn ~95% cho cả truy vấn

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

1 và truy vấn ______72 khi so sánh với

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

Trực quan hóa sự khác biệt về hiệu suất

Thật tuyệt, làm cách nào tôi có thể sử dụng upert[]?

Phương thức này có sẵn trong trình tạo truy vấn cơ sở dữ liệu Laravel và trình tạo truy vấn Eloquent. Nó chấp nhận 3 đối số

$update = ['title', 'price'];

giá trị $

Đối số đầu tiên là một mảng gồm các mảng chứa dữ liệu sẽ được chèn/cập nhật - một mảng con cho mỗi hàng của mô hình/cơ sở dữ liệu Eloquent. Ví dụ

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

Nếu bạn đang sử dụng

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

6 trên một mô hình Eloquent sử dụng dấu thời gian, các thuộc tính

$update = ['title', 'price'];

6 và

$update = ['title', 'price'];

7 sẽ tự động được xử lý

$uniqueBy

Đối số thứ hai mô tả cách xác định một hàng duy nhất

Đây có thể là tên của một cột [được chuyển dưới dạng một chuỗi hoặc một mảng chứa một chuỗi đơn] hoặc nếu bạn có một khóa tổng hợp [hai hoặc nhiều cột được sử dụng để xác định duy nhất một hàng], tên của nhiều cột được chuyển . Ví dụ

$uniqueBy = 'item_ref';
// OR
$uniqueBy = ['item_ref'];
// OR
$uniqueBy = ['item_type', 'item_size'];

Tất cả các công cụ cơ sở dữ liệu ngoại trừ SQL Server đều yêu cầu các cột trong đối số $uniqueBy phải có chỉ mục "chính" hoặc "duy nhất"

cập nhật $

Đối số thứ ba và đối số cuối cùng, là tùy chọn, xác định các cột sẽ được cập nhật nếu một hàng phù hợp đã tồn tại trong cơ sở dữ liệu. Điều này cho phép bạn bao gồm dữ liệu trong đối số

$update = ['title', 'price'];

8 của mình sẽ chỉ được viết trên

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

1. Theo mặc định, tất cả các cột trong

$update = ['title', 'price'];

8 của bạn đều được cập nhật, nhưng đây là một ví dụ nếu bạn muốn cung cấp đối số này

$update = ['title', 'price'];

kết thúc

Tôi hy vọng bạn thấy điều này hữu ích - nếu muốn biết thêm, bạn có thể đọc tài liệu [mặc dù tôi đã đề cập đến mọi thứ và hơn thế nữa trong bài đăng trên blog này] hoặc xem lại các PR ban đầu trên GitHub. #34698, #34712

Tôi là @gbuckingham89 trên Twitter nếu bạn có bất kỳ câu hỏi nào về việc sử dụng

$values = [
    [
        'item_ref'  => 1,
        'title'     => 'Large cake',
        'price'     => 15.00,
    ],
    [
        'item_ref'  => 2,
        'title'     => 'Medium cake',
        'price'     => 10.00,
    ],
    [
        'item_ref'  => 3,
        'title'     => 'Small cake',
        'price'     => 5.00,
    ]
];

6 hoặc bất kỳ điều gì khác liên quan đến Laravel

Làm cách nào để cập nhật hoặc chèn vào Laravel?

Về cơ bản, chúng tôi định vị bảng mà chúng tôi muốn cập nhật hoặc chèn bản ghi vào bằng cách sử dụng bảng[]. Trong trường hợp này là bảng người dùng. Sau đó, chúng ta xâu chuỗi updateOrInsert[] với nó, và trong updateOrInsert[] chúng ta chuyển một mảng có điều kiện

Làm cách nào để cập nhật dữ liệu trong Laravel?

Chúng tôi có thể cập nhật các bản ghi bằng cách sử dụng mặt tiền DB với phương thức cập nhật . Cú pháp của phương thức cập nhật như trong bảng sau. Chạy một câu lệnh cập nhật đối với cơ sở dữ liệu.

Trình tạo truy vấn trôi chảy trong Laravel là gì?

Trình tạo truy vấn cơ sở dữ liệu của Laravel cung cấp giao diện thuận tiện, trôi chảy để tạo và chạy các truy vấn cơ sở dữ liệu . Nó có thể được sử dụng để thực hiện hầu hết các hoạt động cơ sở dữ liệu trong ứng dụng của bạn và hoạt động hoàn hảo với tất cả các hệ thống cơ sở dữ liệu được Laravel hỗ trợ.

Chúng ta nên cập nhật thông tin chi tiết về kết nối cơ sở dữ liệu trong Laravel ở đâu?

Tệp cấu hình cơ sở dữ liệu là app/config/database. php . Trong tệp này, bạn có thể xác định tất cả các kết nối cơ sở dữ liệu của mình, cũng như chỉ định kết nối nào sẽ được sử dụng theo mặc định.