Hãy để chúng tôi xem một ví dụ. Đầu tiên, chúng ta sẽ tạo một bảng. Sau đây là lệnh CREATE để tạo bảng
mysql> CREATE table MultipleRecordWithValues - > [ - > id int, - > name varchar[100] - > ]; Query OK, 0 rows affected [0.88 sec]
Sau đây là cú pháp chèn hàng loạt
INSERT into yourTableName values[column1,column2,....N],[column1,column2,....N],[column1,column2,....N],...........N;
Áp dụng cú pháp trên để chèn bản ghi hàng loạt
mysql> insert into MultipleRecordWithValues values[1,'John'],[2,'Carol'],[3,'Bob'],[4,'Smith']; Query OK, 4 rows affected [0.16 sec] Records: 4 Duplicates: 0 Warnings: 0
Vì 4 hàng bị ảnh hưởng, điều đó có nghĩa là chúng tôi đã chèn bản ghi thành công. Để kiểm tra xem tất cả các bản ghi có trong bảng hay không, hãy sử dụng lệnh SELECT
Gần đây tôi đã viết một nút. js để lặp lại hàng triệu tệp mỗi ngày và chèn nội dung của chúng vào cơ sở dữ liệu MySQL. Thay vì xử lý một bản ghi tại một thời điểm, tập lệnh lưu trữ nội dung tệp trong bộ nhớ và sau đó chạy câu lệnh INSERT cứ sau 1000 tệp. Để làm điều đó, tôi đã sử dụng dạng chèn hàng loạt của câu lệnh INSERT. Tùy thuộc vào yêu cầu cụ thể của bạn, bạn có thể chọn sử dụng một giải pháp khác. Trong blog hôm nay, chúng ta sẽ xem xét một vài lựa chọn thay thế
Câu lệnh INSERT hỗ trợ một số biến thể cú pháp, một trong số đó là để chèn nhiều hàng cùng một lúc. Để làm điều đó, chúng ta chỉ cần đặt từng danh sách giá trị trong ngoặc đơn và phân tách chúng bằng dấu phẩy
INSERT INTO table_name [column_list]
VALUES
[value_list_1],
[value_list_2],
..
[value_list_n];
Đủ đơn giản. Đây là một tuyên bố mẫu được hiển thị trong
Mặc dù câu lệnh trên được định dạng để dễ đọc, nhưng bạn không cần phải lo lắng về điều đó khi tạo SQL động. Miễn là cú pháp đúng về mặt ngữ nghĩa, nó sẽ hoạt động tốt. Cuối cùng, lưu ý rằng 1000 là số hàng tối đa có thể được chèn cùng một lúc bằng cách sử dụng câu lệnh INSERT
Một tùy chọn khác, dành cho những người không hứng thú với việc viết mã kịch bản, là sử dụng thứ gì đó như LOAD DATA INFILE. Đó là một lệnh dành riêng cho MySQL, nhưng hầu hết các hệ thống cơ sở dữ liệu [DBMS] khác đều hỗ trợ một cái gì đó tương tự. Nó có thể nhập nhiều định dạng tệp được phân tách, bao gồm dấu phẩy [CSV], Tab [TDV] và các định dạng khác
Đây là câu lệnh nhập dữ liệu từ "c. \tmp\giảm giá. csv" vào bảng giảm giá
LOAD DATA INFILE 'c:/tmp/discounts.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
Trong câu lệnh trên, tùy chọn IGNORE 1 ROWS được sử dụng để bỏ qua các tiêu đề
Tôi muốn sử dụng phương pháp này để nhập dữ liệu, nhưng các tệp mà chúng tôi đang nhập từ đó sử dụng định dạng phức tạp và chuyên biệt cao, đòi hỏi nhiều logic giao diện người dùng
Vẫn còn một cách tiếp cận khác là sử dụng tiện ích nhập chẳng hạn như Trình hướng dẫn nhập của Navicat. Nó hỗ trợ hầu hết mọi định dạng mà bạn có thể tưởng tượng, bao gồm CSV, Excel, HTML, XML, JSON và nhiều định dạng khác
Có một màn hình để chọn dấu phân cách bản ghi, dấu phân cách trường và vòng loại văn bản
Navicat cho bạn thấy sự tiến bộ trong thời gian thực
Sau khi hoàn tất, bạn có thể lưu tất cả các cài đặt của mình để sử dụng sau này, điều này không chỉ hữu ích để chạy cùng một cách thường xuyên mà còn cho phép bạn tự động hóa nó, để quá trình nhập diễn ra mà không cần bất kỳ sự can thiệp bổ sung nào từ phía bạn
Trong blog hôm nay, chúng tôi đã đề cập đến một số lựa chọn thay thế để thực hiện thao tác chèn hàng loạt vào MySQL và các DBMS khác
Khi bạn cần chèn hàng triệu bản ghi vào cơ sở dữ liệu MySQL, bạn sẽ sớm nhận ra rằng việc gửi từng câu lệnh
INSERT into yourTableName values[column1,column2,....N],[column1,column2,....N],[column1,column2,....N],...........N;4 không phải là một giải pháp khả thi
Tài liệu MySQL có một số mẹo tối ưu hóa INSERT đáng để bắt đầu đọc
Tôi sẽ cố gắng tóm tắt ở đây hai kỹ thuật chính để tải dữ liệu vào cơ sở dữ liệu MySQL một cách hiệu quả
TẢI DỮ LIỆU VÀO TẬP TINNếu bạn đang tìm kiếm hiệu suất thô, đây chắc chắn là giải pháp bạn lựa chọn.
INSERT into yourTableName values[column1,column2,....N],[column1,column2,....N],[column1,column2,....N],...........N;5 là một câu lệnh dành riêng cho MySQL, được tối ưu hóa cao để chèn trực tiếp dữ liệu vào bảng từ tệp CSV/TSV
Có hai cách để sử dụng
INSERT into yourTableName values[column1,column2,....N],[column1,column2,....N],[column1,column2,....N],...........N;5. Bạn có thể sao chép tệp dữ liệu vào thư mục dữ liệu của máy chủ [thường là
INSERT into yourTableName values[column1,column2,....N],[column1,column2,....N],[column1,column2,....N],...........N;7] và chạy
LOAD DATA INFILE '/path/to/products.csv' INTO TABLE products;Điều này khá cồng kềnh vì nó yêu cầu bạn phải có quyền truy cập vào hệ thống tệp của máy chủ, đặt quyền thích hợp, v.v.
Tin vui là bạn cũng có thể lưu trữ tệp dữ liệu ở phía máy khách và sử dụng từ khóa
INSERT into yourTableName values[column1,column2,....N],[column1,column2,....N],[column1,column2,....N],...........N;8
LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;Trong trường hợp này, tệp được đọc từ hệ thống tệp của máy khách, được sao chép trong suốt vào thư mục tạm thời của máy chủ và được nhập từ đó. Nói chung, nó gần như nhanh như tải trực tiếp từ hệ thống tệp của máy chủ. Tuy nhiên, bạn cần đảm bảo rằng tùy chọn này được bật trên máy chủ của mình
Có nhiều tùy chọn để
INSERT into yourTableName values[column1,column2,....N],[column1,column2,....N],[column1,column2,....N],...........N;5, chủ yếu liên quan đến cách cấu trúc tệp dữ liệu của bạn [dấu phân cách trường, bao vây, v.v. ]. Hãy xem tài liệu để xem tất cả
Mặc dù
INSERT into yourTableName values[column1,column2,....N],[column1,column2,....N],[column1,column2,....N],...........N;5 là tùy chọn hiệu suất tốt nhất của bạn, nhưng nó yêu cầu bạn phải chuẩn bị sẵn dữ liệu của mình dưới dạng tệp văn bản được phân tách bằng dấu phân cách. Nếu bạn không có các tệp như vậy, bạn sẽ cần sử dụng thêm tài nguyên để tạo chúng và có thể sẽ tăng thêm mức độ phức tạp cho ứng dụng của bạn. May mắn thay, có một giải pháp thay thếphần chèn mở rộng
Một câu lệnh SQL
INSERT into yourTableName values[column1,column2,....N],[column1,column2,....N],[column1,column2,....N],...........N;4 điển hình trông giống như
INSERT INTO user [id, name] VALUES [1, 'Ben'];Một
INSERT into yourTableName values[column1,column2,....N],[column1,column2,....N],[column1,column2,....N],...........N;4 mở rộng nhóm một số bản ghi vào một truy vấn duy nhất________số 8
Chìa khóa ở đây là tìm số lần chèn tối ưu cho mỗi truy vấn để gửi. Không có con số chung nào phù hợp với tất cả, vì vậy bạn cần đánh giá chuẩn một mẫu dữ liệu của mình để tìm ra giá trị mang lại hiệu suất tối đa hoặc sự đánh đổi tốt nhất về hiệu suất/sử dụng bộ nhớ
Để tận dụng tối đa các phần chèn mở rộng, bạn cũng nên
- sử dụng báo cáo chuẩn bị
- chạy báo cáo trong một giao dịch
Tôi đang chèn 1. 2 triệu hàng, 6 cột thuộc nhiều loại khác nhau, trung bình ~26 byte mỗi hàng. Tôi đã thử nghiệm hai cấu hình phổ biến
- Máy khách và máy chủ trên cùng một máy, giao tiếp qua ổ cắm UNIX
- Máy khách và máy chủ trên các máy riêng biệt, với độ trễ rất thấp [> Chỉnh sửa >> Tùy chọn >> tab Truy vấn SQL. Ở đây, bạn sẽ có tùy chọn Giới hạn hàng. Bạn có thể đặt giá trị này thành giá trị rất cao hoặc bỏ chọn tùy chọn này . Khi bạn bỏ chọn tùy chọn đó, nó sẽ truy xuất tất cả các hàng từ một truy vấn [tương đương với không giới hạn].