Xuất dữ liệu MySQL sang Excel bằng dòng lệnh

Các tệp Giá trị được Phân tách bằng Dấu phẩy (CSV) là một cách truyề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 thông qua CSV, vì vậy bạn có thể sử dụng tệp CSV để trao đổi dữ liệu giữa hai bên

Các tệp CSV là văn bản thuần túy, 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 trên đó, bạn có thể xuất một lựa chọn bằng lệnh

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

3

SELECT id, column1, column2 FROM table
INTO OUTFILE '/tmp/mysqlfiles/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'

Thao tác này sẽ xuất tệp CSV thành

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

4 hoặc bất cứ nơi nào bạn đã định cấu hình tệp thành. Bạn cần đảm bảo rằng người dùng đang chạy MySQL (thường là

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

5 hoặc

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

0) có quyền sở hữu và quyền ghi vào thư mục

Bạn cũng cần đảm bảo cài đặt

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

1 cho phép MySQL truy cập vào thư mục đó. Theo mặc định, điều này chặn quyền truy cập đọc và ghi từ các truy vấn SQL. Đây là một điều tốt;

Bạn có thể đưa các thư mục cụ thể vào danh sách trắng bằng cách chỉnh sửa tệp cấu hình MySQL của mình (thường nằm ở

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

2) để đưa vào

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

Điều này sẽ cho phép MySQL đọc và ghi vào

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

3 (mà bạn sẽ phải tạo bằng

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

4). 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 cài đặt

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

5, dấu phẩy sẽ được thoát đúng cách, e. g. ,

________số 8

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 nào khác

Xin lưu ý 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ẽ có cùng thứ tự như câu lệnh

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

6. Ngoài ra, các giá trị null sẽ được xuất dưới dạng

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

7, đây là hành vi dự kiến, nhưng nếu muốn thay đổi điều này, bạn có thể sửa đổi lựa chọn bằng cách bao bọc

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

8 xung quanh các trường của bạn trong câu lệnh

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

6

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 có quyền truy cập vào chính máy chủ (chẳng hạn như khi máy chủ không do bạn quản lý, trong trường hợp của 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

"3","Escape, this","also, this"

0 trên máy chủ để tạo danh sách được phân tách bằng dấu phẩy, nhưng CLI của MySQL sẽ phân tách bằng các tab theo mặc định

Chỉ cần nhập một truy vấn từ dòng lệnh và chuyển nó vào một tệp

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

4

Vì đầu ra của MySQL được phân tách bằng các tab, nên tệp này được gọi là tệp TSV, dành 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ế từng tab bằng dấu phẩy, điều này sẽ hoạt động nhưng sẽ khiến nó không thành công 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 (kiểm tra bằng

"3","Escape, this","also, this"

1), bạn có thể thay thế các tab bằng

"3","Escape, this","also, this"

2

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

7

Như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

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

8

Điều này sẽ thoát đúng các trường có dấu ngoặc kép, điều này sẽ giải quyết vấn đề về dấu phẩy

Ghi chú. ký tự tab 

"3","Escape, this","also, this"

3 không chuẩn. Trên macOS và BSD, nó không khả dụng, dẫn đến sự lộn xộn của mỗi chữ “t” viết thường khiến

"3","Escape, this","also, this"

2 chèn sai dấu phẩy. Để giải quyết vấn đề này, bạn sẽ cần sử dụng ký tự tab bằng chữ thay cho

"3","Escape, this","also, this"

3

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

2

Nếu dữ liệu đầu vào của bạn chứa các tab, thì bạn không gặp may và sẽ phải tự tạo tệp CSV bằng ngôn ngữ kịch bản

Thực hiện thủ công với ngôn ngữ lập trình thực

MySQL (và hầu hết các cơ sở dữ liệu) được thiết kế để tương tác với nhau, 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 rồi. 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à ghi tệp được phân cách bằng dấu phẩy

Làm cách nào để xuất dữ liệu từ MySQL sang Excel bằng dòng lệnh?

Làm cách nào để xuất dữ liệu từ dòng lệnh MySQL?

Làm cách nào để xuất bảng MySQL sang dòng lệnh CSV?

Làm cách nào để kết xuất cơ sở dữ liệu MySQL bằng dòng lệnh?