Hướng dẫn how do you output mysql query results to a file? - làm thế nào để bạn xuất kết quả truy vấn mysql vào một tệp?

Bạn có cần lưu kết quả của mình từ truy vấn MySQL vào tệp CSV hoặc văn bản không?

Nó rất dễ làm trong MySQL. Bạn có thể làm điều này bằng cách sử dụng một dòng IDE hoặc dòng lệnh, sử dụng lệnh MySQL tích hợp.

Hãy để một cái nhìn.

Truy vấn cơ bản

Hãy để sử dụng một ví dụ đơn giản về một câu lệnh Chọn cho điều này.

SELECT id, first_name, last_name
FROM customer;

Đây là kết quả:

Chúng ta có thể thấy những kết quả này trong đầu ra của dòng lệnh của chúng ta hoặc trong IDE, chẳng hạn như MySQL Workbench.

Làm thế nào chúng ta có thể lưu chúng vào một tệp văn bản?

Chúng tôi có thể sao chép và dán chúng, nhưng đó là chậm và thủ công.

Lưu kết quả MySQL vào tệp

Có một tính năng đầu ra MySQL tích hợp cho Tệp như một phần của câu lệnh CHỌN.

Chúng tôi chỉ cần thêm các từ vào trang phục, theo sau là tên tệp, đến cuối câu lệnh select.

Ví dụ:

SELECT id, first_name, last_name
FROM customer
INTO OUTFILE '/temp/myoutput.txt';

Điều này sẽ tạo một tệp mới có tên MyOutput.txt chứa kết quả của truy vấn này, trong một thư mục có tên Temp.

Điều gì xảy ra nếu bạn gặp lỗi này [như tôi đã làm]?

Mã lỗi: 1290. Máy chủ MySQL đang chạy với tùy chọn file-file-priv để nó không thể thực thi câu lệnh này

Tôi sẽ giải thích thêm về việc giải quyết điều này sau trong hướng dẫn này.

Hiện tại, giả sử bạn có thể chạy câu lệnh để tạo tệp, nó sẽ trông như thế này:

Văn bản trong tệp là:

1 John Smith
2 Mary McAdams
3 Steve Pitt
4 Mark Cousins
5 Shaun Jones
7 Amy McDonald
8 Brad Swan
10 Wendy Johnson

Như bạn có thể thấy, các trường được phân tách bằng các tab. Đây là hành vi mặc định, nhưng nó có thể được thay đổi.

Thay đổi tham số để đặt các giá trị phân tách bằng dấu phẩy

Bạn có thể thay đổi các tham số của điều này thành từ khóa Outfile để thay đổi cách viết tệp.

Có một số tham số bổ sung. Đây là một số trong những phổ biến nhất:

• Các trường chấm dứt bởi: Điều này chỉ ra [các] ký tự được sử dụng để kết thúc một trường.: this indicates the character[s] that are used to end a field.
• Kèm theo: Điều này chỉ ra [các] ký tự sẽ được sử dụng để bao quanh mỗi trường.: this indicates the character[s] that will be used to surround each field.
• Các dòng bị chấm dứt bởi: Điều này chỉ ra [các] ký tự được sử dụng để kết thúc một dòng và bắt đầu một dòng mới.: this indicates the character[s] that are used to end a line and start a new line.

Ví dụ: để chọn dữ liệu vào tệp CSV và gửi từng trường trong một trích dẫn kép:

SELECT id, first_name, last_name
FROM customer
INTO OUTFILE '/temp/myoutput.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Nếu chúng ta chạy câu lệnh này, chúng ta có thể kiểm tra tệp, trông sẽ như thế này:

Văn bản trong tệp là:

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

Như bạn có thể thấy, các trường được phân tách bằng các tab. Đây là hành vi mặc định, nhưng nó có thể được thay đổi.

Thay đổi tham số để đặt các giá trị phân tách bằng dấu phẩy

Bạn có thể thay đổi các tham số của điều này thành từ khóa Outfile để thay đổi cách viết tệp.

Có một số tham số bổ sung. Đây là một số trong những phổ biến nhất:

Các trường chấm dứt bởi: Điều này chỉ ra [các] ký tự được sử dụng để kết thúc một trường.

Kèm theo: Điều này chỉ ra [các] ký tự sẽ được sử dụng để bao quanh mỗi trường.

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

Các dòng bị chấm dứt bởi: Điều này chỉ ra [các] ký tự được sử dụng để kết thúc một dòng và bắt đầu một dòng mới.

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

Ví dụ: để chọn dữ liệu vào tệp CSV và gửi từng trường trong một trích dẫn kép:

Nếu chúng ta chạy câu lệnh này, chúng ta có thể kiểm tra tệp, trông sẽ như thế này:

Vì vậy, cách mà bạn có thể tạo tệp CSV hoặc văn bản trong MySQL. Bạn chỉ cần thêm từ khóa Outfile vào cuối truy vấn chọn và chỉ định một số tham số.

Điều này là do kiểu dữ liệu của cột được xác định bởi truy vấn đầu tiên trong Liên minh. Tất cả các tiêu đề cột là các giá trị văn bản, vì vậy khi phần thứ hai của truy vấn liên minh được chạy, nó có thể cố gắng thêm một ngày vào cột ký tự và gây ra lỗi.

Có thể có vấn đề với việc đặt hàng

Nếu truy vấn của bạn bao gồm một đơn đặt hàng theo mệnh đề, thì các tiêu đề cột của bạn sẽ giành được hiển thị chính xác.

Điều này là do mệnh đề theo thứ tự đi vào cuối truy vấn và sẽ bao gồm hàng tiêu đề cột của bạn theo thứ tự. Điều này có thể có nghĩa là các tiêu đề cột của bạn sẽ kết thúc tại một nơi trong kết quả không phải là đỉnh cao.

Bạn có thể hiểu được điều này bằng cách đặt truy vấn chính của bạn vào một truy vấn con và sau đó sử dụng liên minh về điều đó.

Ví dụ:

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

Điều này sẽ đảm bảo rằng các tiêu đề cột của bạn được hiển thị ở đầu.

Các vấn đề hiệu suất có thể

Nếu bạn cố gắng sử dụng tất cả liên minh hoặc liên minh, cơ sở dữ liệu MySQL có thể cố gắng sử dụng một kế hoạch thực thi khác để hiển thị dữ liệu, mặc dù bạn chỉ thêm một hàng.

Điều này có thể có nghĩa là thời gian chạy chậm hơn rất nhiều.

Vì vậy, nó có một cái gì đó bạn nên kiểm tra trước khi bắt đầu sử dụng điều này như một giải pháp vĩnh viễn.

Điều gì sẽ xảy ra nếu tập tin đã tồn tại?

Vì vậy, điều gì xảy ra nếu bạn thử lệnh này và tệp đã tồn tại?

Ví dụ: giả sử tệp myoutput.txt tồn tại, bạn chạy lệnh này:

SELECT id, first_name, last_name
FROM customer
INTO OUTFILE '/temp/myoutput.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Bạn sẽ nhận được tin nhắn này:

Mã lỗi: 1086. Tệp ‘/temp/myoutput.txt, đã tồn tại

Điều này là khá rõ ràng. Các tập tin đã tồn tại. Vì vậy, bạn cần sử dụng một tên tệp không tồn tại.

Lỗi với tệp bảo mật-priv

Bạn có đang chạy điều này chọn vào trang phục và gặp lỗi về tệp an toàn-file-priv không?

Mã lỗi: 1290. Máy chủ MySQL đang chạy với tùy chọn file-file-priv để nó không thể thực thi câu lệnh này

Điều này về cơ bản có nghĩa là MySQL có một bộ thư mục cụ thể có thể được sử dụng cho đầu vào và đầu ra, và tệp mà chúng tôi cố gắng viết không nằm trong thư mục đó.

Để giải quyết điều này, chúng ta cần:

1. Tìm hiểu những thư mục cụ thể là gì
2. Thêm đường dẫn thư mục này vào tham số trang phục của chúng tôi để tệp của chúng tôi được tạo ở đó.

Để tìm hiểu xem các thư mục ở đâu, chúng ta có thể chạy một trong hai lệnh này:

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

Giá trị

Secure_File_Priv

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

Đầu ra trong ví dụ này hiển thị giá trị là ‘/usr/files/xông.

Vì vậy, tất cả những gì bạn cần làm là thêm con đường này vào đầu con đường trang phục.

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

Vì vậy, tất cả những gì bạn cần làm là thêm con đường này vào đầu con đường trang phục.

Câu lệnh Chọn của bạn sau đó sẽ trông như thế này:

FILE-FILE-PRIV là NULL

Nếu bạn có lỗi ở trên và bạn tìm kiếm biến Secure_File_Priv, bạn có thể thấy đó là NULL. Điều này xảy ra thường xuyên nhất trên macOS với Mamp.

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

VÔ GIÁ TRỊ

Để giải quyết điều này, chúng ta cần thêm biến này vào tệp My.cnf.

Đầu tiên, hãy dừng máy chủ MAMP của bạn bằng cách mở MAMP và nhấp vào Dừng.

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Mở một cửa sổ thiết bị đầu cuối.

Nhập vào lệnh này:

Màn hình của bạn sẽ trông như thế này.

Nhấn Enter để chạy lệnh. Điều này mở ra một trình soạn thảo cho tệp My.cnf.

Sao chép các dòng sau vào cửa sổ thiết bị đầu cuối:

[Thay thế các chữ cái BB bằng bất kỳ thư mục người dùng nào của bạn]

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

Vì vậy, tất cả những gì bạn cần làm là thêm con đường này vào đầu con đường trang phục.

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Câu lệnh Chọn của bạn sau đó sẽ trông như thế này:

FILE-FILE-PRIV là NULL

Nếu bạn có lỗi ở trên và bạn tìm kiếm biến Secure_File_Priv, bạn có thể thấy đó là NULL. Điều này xảy ra thường xuyên nhất trên macOS với Mamp.

VÔ GIÁ TRỊ