Before running any export statement, you have to create a new database and tables or select an existing database and tables. Run the following commands to create a library database and three tables named books, borrowers and book_borrow_info.
CREATE TABLE books (
id INT NOT NULL AUTO_INCREMENT,
title varchar(50) NOT NULL,
author varchar(50) NOT NULL,
publisher varchar(50) NOT NULL,
PRIMARY KEY (id)
CREATE TABLE borrowers (
id VARCHAR(50) NOT NULL,
name varchar(50) NOT NULL,
address varchar(50) NOT NULL,
email varchar(50) NOT NULL,
PRIMARY KEY (id)
CREATE TABLE book_borrow_info (
borrow_date DATE NOT NULL,
return_date DATE NOT NULL,
status VARCHAR(15) NOT NULL,
INDEX par_ind (book_id),
PRIMARY KEY (borrow_id, borrow_date),
FOREIGN KEY (book_id) REFERENCES books(id)
ON DELETE CASCADE
ON UPDATE CASCADE );
Run the following SQL commands to insert some data into three tables.
(NULL, 'To Kill a Mockingbird', 'Harper Lee', 'Grand Central Publishing'),
(NULL, 'One Hundred Years of Solitude', 'Garcia Marquez', 'Lutfi Ozkok'),
(NULL, 'Invisible Man', 'Ralph Ellison', 'Encyclopadeia Britannica, Inc.');
INSERT INTO borrowers VALUES
('123490', 'Patrick Wood', '34 West Street LANCASTER LA14 9ZH', 'email@example.com'),
('157643', 'Ezra Martin', '10 The Grove BIRMINGHAM B98 1EU', 'firstname.lastname@example.org'),
('146788', 'Frederick Hanson', '85 Highfield Road SHREWSBURY SY46 3ME',
INSERT INTO book_borrow_info VALUES
('123490', 1, '2020-02-15', '2020-02-25', 'Returned'),
('157643', 2, '2020-03-31', '2020-03-10', 'Pending'),
('146788', 3, '2020-04-10', '2020-01-20', 'Borrowed');
Every exported file stores in a particular location in MySQL and the location is stored in the variable, “secure_file_priv“. Run the following command to find out the path of the file. This is a read-only variable that you can’t change.
The location of the file is ‘/var/lib/mysql-files/’. You have to use this location at the time of running export command.
Export data in CSV format using INTO OUTFILE statement:
Any database table can be exported by using INTO OUTFILE statement. Suppose, I want to export the data of the books table. Run the following command to check the existing data of this table.
Any filename with .csv extension can be set for the export filename. Run the following export command to create books.csv file in the location,/var/lib/mysql-files/.
Go to the export location to check the file is created on not. Click on the file to display the content of the file. If the comma exists as a content of any field value of the table then it will separate the value into multiple values. In the books table, publisher field contains a comma(,) as content in the third record. If you examine the content of the CSV file you will see that the publisher content is separated into two values that are not appropriate.
To solve the above problem, you can export the books table data into a CSV file by specifying the necessary delimiters properly in the SQL statement. Run the following command to export books table into books2.csv file appropriately. Here, three delimiters are used to export data properly. These are FIELDS TERMINATED BY, ENCLOSED BY and LINES TERMINATED BY.
Export data in CSV format using mysql client:
The table data can be exported into a CSV file by using mysql client. The borrowers table is exported in this part of this article. Run the following command to check the existing content of this table.
mysql statement with `sed` command is used here to export the data. One of the benefits of this statement is that you can set any location and filename for storing the CSV file without the default location that is used in the previous example. If there is no password for the root user then -p option will be omitted. The exported location is /tmp and filename is output.csv here.
sed 's/\t/,/g' > /tmp/output.csv
If you open the output.csv file then the following output will appear.
Export data in CSV format using phpmyadmin:
The database table can be exported into CSV file very easily by using any database administrative tool. You have to install the tool before doing the export. phpmyadmin is used here for exporting the table into the CSV file. Here, the exported filename will be the name of the table. Run the following URL in any browser to display the existing database list of the MySQL server.
Select the table of the database that you want to export and click on the Export tab from the right side. Select the CSV format from the Format drop-down list and click on the Go button. Select the Save File option and press the OK button.
The file will be downloaded in the Downloads folder. Here, book_borrow_info table is exported. So, the CSV filename will be book_borrow_info.csv and the following content will appear if you open the file.
The data exchange task between the applications becomes easier by using the export feature of the database server. MySQL supports many other file formats to export table data. The sql file format is mainly used to transfer data between the same database servers. CSV format is very helpful if you want to exchange data between two different applications.