Bản tóm tắt. trong hướng dẫn này, bạn sẽ học các kỹ thuật khác nhau về cách xuất bảng MySQL sang tệp CSV
CSV là viết tắt của các giá trị được phân tách bằng dấu phẩy. Bạn thường sử dụng định dạng tệp CSV để trao đổi dữ liệu giữa các ứng dụng như Microsoft Excel, Open Office, Google Docs, v.v.
Sẽ rất hữu ích khi có dữ liệu từ cơ sở dữ liệu MySQL ở định dạng tệp CSV vì bạn có thể phân tích và định dạng dữ liệu theo cách bạn muốn
MySQL cung cấp một cách dễ dàng để xuất kết quả của truy vấn thành tệp CSV nằm trong máy chủ cơ sở dữ liệu
Trước khi xuất dữ liệu, bạn phải đảm bảo rằng
- Quy trình của máy chủ MySQL có quyền ghi vào thư mục đích chứa tệp CSV đích
- Tệp CSV đích không được tồn tại
Truy vấn sau đây chọn các đơn đặt hàng đã hủy từ bảng
8Code language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled';
Để xuất tập kết quả này thành tệp CSV, bạn thêm một số mệnh đề vào truy vấn trên như sau
Code language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Câu lệnh đã tạo một tệp CSV có tên
9 trong thư mục
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL [Structured Query Language] [sql]
0 chứa tập hợp kết quảCode language: SQL [Structured Query Language] [sql]
SET @TS = DATE_FORMAT[NOW[],'_%Y_%m_%d_%H_%i_%s']; SET @FOLDER = 'c:/tmp/'; SET @PREFIX = 'orders'; SET @EXT = '.csv'; SET @CMD = CONCAT["SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT, "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'", " LINES TERMINATED BY '\r\n';"]; PREPARE statement FROM @CMD; EXECUTE statement;
Tệp CSV chứa các dòng hàng trong tập hợp kết quả. Mỗi dòng được kết thúc bằng một chuỗi ký tự xuống dòng và ký tự xuống dòng được chỉ định bởi mệnh đề
1. Mỗi dòng chứa các giá trị của từng cột của hàng trong tập kết quảCode language: SQL [Structured Query Language] [sql]
SET @TS = DATE_FORMAT[NOW[],'_%Y_%m_%d_%H_%i_%s']; SET @FOLDER = 'c:/tmp/'; SET @PREFIX = 'orders'; SET @EXT = '.csv'; SET @CMD = CONCAT["SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT, "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'", " LINES TERMINATED BY '\r\n';"]; PREPARE statement FROM @CMD; EXECUTE statement;
Mỗi giá trị được đặt trong dấu ngoặc kép được biểu thị bởi mệnh đề
2. Điều này ngăn giá trị có thể chứa dấu phẩy [,] sẽ được hiểu là dấu tách trường. Khi đặt các giá trị bằng dấu ngoặc kép, dấu phẩy bên trong giá trị không được nhận dạng là dấu tách trườngCode language: SQL [Structured Query Language] [sql]
SET @TS = DATE_FORMAT[NOW[],'_%Y_%m_%d_%H_%i_%s']; SET @FOLDER = 'c:/tmp/'; SET @PREFIX = 'orders'; SET @EXT = '.csv'; SET @CMD = CONCAT["SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT, "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'", " LINES TERMINATED BY '\r\n';"]; PREPARE statement FROM @CMD; EXECUTE statement;
Xuất dữ liệu sang tệp CSV có tên tệp chứa dấu thời gian
Bạn thường cần xuất dữ liệu thành tệp CSV có tên chứa dấu thời gian mà tại đó tệp được tạo. Để làm như vậy, bạn cần sử dụng câu lệnh chuẩn bị sẵn của MySQL
Các lệnh sau xuất toàn bộ bảng đơn đặt hàng thành tệp CSV với dấu thời gian là một phần của tên tệp
Code language: SQL [Structured Query Language] [sql]
SET @TS = DATE_FORMAT[NOW[],'_%Y_%m_%d_%H_%i_%s']; SET @FOLDER = 'c:/tmp/'; SET @PREFIX = 'orders'; SET @EXT = '.csv'; SET @CMD = CONCAT["SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT, "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'", " LINES TERMINATED BY '\r\n';"]; PREPARE statement FROM @CMD; EXECUTE statement;
Hãy xem xét các lệnh trên chi tiết hơn
- Đầu tiên, chúng tôi đã tạo một truy vấn với dấu thời gian hiện tại như một phần của tên tệp
- Thứ hai, chúng tôi đã chuẩn bị câu lệnh để thực thi bằng cách sử dụng câu lệnh
0 câu lệnhSELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
1
Code language: SQL [Structured Query Language] [sql]SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
- Thứ ba, chúng tôi đã thực hiện câu lệnh bằng cách sử dụng lệnh
2
Code language: SQL [Structured Query Language] [sql]SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Bạn có thể gói lệnh theo sự kiện và lên lịch chạy sự kiện định kỳ nếu cần
Xuất dữ liệu với tiêu đề cột
Sẽ thuận tiện nếu tệp CSV chứa dòng đầu tiên làm tiêu đề cột để tệp dễ hiểu hơn
Để thêm tiêu đề cột, bạn cần sử dụng câu lệnh UNION như sau
Code language: SQL [Structured Query Language] [sql]
[SELECT 'Order Number','Order Date','Status'] UNION [SELECT orderNumber,orderDate, status FROM orders INTO OUTFILE 'C:/tmp/orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n'];
Như truy vấn đã hiển thị, bạn cần bao gồm tiêu đề cột của mỗi cột
Xử lý giá trị NULL
Trong trường hợp các giá trị trong tập hợp kết quả chứa giá trị NULL, thì tệp đích sẽ chứa
3 thay vìCode language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
4. Để khắc phục sự cố này, bạn cần thay thế giá trịCode language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
4 bằng giá trị khác e. g. , không áp dụng [Code language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
6 ] bằng cách sử dụng hàm IFNULL như truy vấn sau
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL [Structured Query Language] [sql]
1Code language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Chúng tôi đã thay thế các giá trị
4 trong cộtCode language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
8 bằng chuỗiCode language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
6. Tệp CSV hiển thị giá trịCode language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
6 thay vìCode language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
4Code language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Xuất dữ liệu sang tệp CSV bằng MySQL Workbench
Trong trường hợp bạn không có quyền truy cập vào máy chủ cơ sở dữ liệu để lấy tệp CSV đã xuất, bạn có thể sử dụng MySQL Workbench để xuất tập kết quả của truy vấn sang tệp CSV trong máy tính cục bộ của mình như sau
- Đầu tiên, thực hiện một truy vấn lấy tập kết quả của nó
- Thứ hai, từ bảng kết quả, nhấp vào “xuất bản ghi sang tệp bên ngoài”. Tập kết quả còn được gọi là tập bản ghi
- Thứ ba, một hộp thoại mới hiển thị. Nó hỏi bạn tên tệp và định dạng tệp. Nhập tên tệp, chọn CSV làm định dạng tệp và nhấp vào nút Lưu
Tệp CSV được xuất bởi MySQL Workbench hỗ trợ tiêu đề cột, giá trị
4 và các tính năng tuyệt vời khácCode language: SQL [Structured Query Language] [sql]
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';