Các tệp Giá trị phân tách bằng dấu phẩy (CSV) là cách chuyển dữ liệu giữa các ứng dụng. Cơ sở dữ liệu như MySQL và phần mềm bảng tính như Excel hỗ trợ nhập và xuất qua CSV, vì vậy bạn có thể sử dụng tệp CSV để trao đổi dữ liệu giữa hai cơ sở.
Các tệp CSV là bản rõ, vì vậy chúng rất nhẹ và dễ xuất từ MySQL.
Từ máy chủ cơ sở dữ liệu
Nếu bạn có quyền truy cập vào máy chủ mà MySQL đang chạy, bạn có thể xuất lựa chọn bằng INTO OUTFILE lệnh.
SELECT id, column1, column2 FROM table
INTO OUTFILE '/tmp/mysqlfiles/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'Điều này sẽ xuất một tệp CSV đến /tmp/mysqlfiles/table.csvhoặc bất cứ nơi nào bạn định cấu hình nó. Bạn sẽ cần đảm bảo người dùng đang chạy MySQL (thường mysql hoặc root) có quyền sở hữu và ghi quyền truy cập vào thư mục.
Bạn cũng cần đảm bảo rằng secure_file_priv cài đặt cho phép MySQL truy cập vào thư mục đó. Điều này, theo mặc định, chặn truy cập đọc và ghi từ các truy vấn SQL. Đây là một điều tốt; nếu mã của bạn dễ bị tấn công SQL, bất kỳ kẻ tấn công tiềm năng nào cũng chỉ có quyền truy cập vào MySQL chứ không phải phần còn lại của hệ thống tệp.
Bạn có thể lập danh sách trắng các thư mục cụ thể bằng cách chỉnh sửa tệp cấu hình MySQL của bạn (thường nằm ở /etc/my.cnf) để bao gồm:
[mysqld]
secure-file-priv = "/tmp/mysqlfiles"Điều này sẽ cho phép MySQL đọc và ghi vào /tmp/mysqlfiles/ (mà bạn sẽ phải tạo bằng mkdir). Khi MySQL có thể xuất tệp, bạn sẽ có thể chạy truy vấn và xuất tệp CSV.
Với ENCLOSED BY cài đặt, dấu phẩy sẽ được thoát đúng, ví dụ:
"3","Escape, this","also, this"Mà bạn có thể lấy và nhập trực tiếp vào bất kỳ chương trình bảng tính hoặc phần mềm khác.
Hãy nhớ rằng tệp CSV đã xuất không bao gồm các tiêu đề cột, nhưng các cột sẽ theo cùng thứ tự với SELECT tuyên bố. Ngoài ra, các giá trị null sẽ được xuất dưới dạng \N, đó là hành vi dự kiến, nhưng nếu bạn muốn thay đổi điều này, bạn có thể sửa đổi lựa chọn bằng cách bao ifnull(field, "") quanh các trường trong SELECT câu lệnh của bạn .
Từ dòng lệnh MySQL
Nếu bạn chỉ có quyền truy cập dòng lệnh vào phiên bản MySQL và không truy cập vào chính máy chủ (chẳng hạn như khi nó không do bạn quản lý, trong trường hợp Amazon RDS ), vấn đề sẽ phức tạp hơn một chút. Mặc dù bạn có thể sử dụng FIELDS TERMINATED BY ',' trên máy chủ để tạo danh sách được phân tách bằng dấu phẩy, MySQL CLI sẽ phân tách bằng các tab theo mặc định.
Chỉ cần nhập truy vấn từ dòng lệnh và chuyển nó sang tệp:
mysql -u root -e "select * from database;" > output.tsvDo đầu ra của MySQL được phân tách bằng các tab, nên đây được gọi là tệp TSV, cho các giá trị được phân tách bằng tab,, và có thể hoạt động thay cho tệp CSV của bạn trong một số chương trình như nhập bảng tính. Nhưng nó không phải là tệp CSV và việc chuyển đổi nó thành một tệp rất phức tạp.
Bạn chỉ có thể thay thế mỗi tab bằng dấu phẩy, sẽ hoạt động nhưng sẽ khiến nó bị lỗi nếu có dấu phẩy trong dữ liệu đầu vào. Nếu bạn hoàn toàn chắc chắn 100% rằng không có dấu phẩy trong tệp TSV của mình (hãy kiểm tra grep), bạn có thể thay thế các tab bằng sed:
sed "s/\t/,/g" output.tsv > output.csvNhưng nếu bạn có dấu phẩy trong dữ liệu của mình, bạn sẽ phải sử dụng biểu thức chính quy dài hơn nhiều :
sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" output.tsv > output.csvĐiều này sẽ thoát đúng các trường có dấu ngoặc kép, sẽ giải quyết vấn đề dấu phẩy.
Lưu ý: các ký tự tab \t là không đạt tiêu chuẩn . Trên macOS và BSD, nó không có sẵn, điều này dẫn đến một mớ hỗn độn của mỗi chữ viết tắt chữ T, gây ra sedviệc chèn các dấu phẩy sai. Để giải quyết vấn đề này, bạn sẽ cần sử dụng ký tự tab theo nghĩa đen thay cho \t:
sed "s/ /,/g" output.tsv > output.csvNếu dữ liệu đầu vào của bạn chứa các tab, bạn sẽ không gặp may và sẽ phải tự tạo một tệp CSV bằng ngôn ngữ kịch bản.
Làm thủ công với ngôn ngữ lập trình
MySQL (và hầu hết các cơ sở dữ liệu) được thiết kế để tương tác, vì vậy bạn có thể đã có một số loại ngôn ngữ lập trình được kết nối với MySQL. Hầu hết các ngôn ngữ cũng có thể ghi vào đĩa, vì vậy bạn có thể tạo tập lệnh đầu ra CSV của riêng mình bằng cách đọc trực tiếp các trường từ cơ sở dữ liệu, thoát chúng đúng cách và viết tệp được phân cách bằng dấu phẩy.