Selecting data into a file and table

You can save the output into a file using the SELECT INTO OUTFILE statement. You can specify the column and line delimiters, and later you can import the data into other data platforms.

Saving as a file

To save the output into a file, you need the FILE privilege. FILE is a global privilege, which means you cannot restrict it for a particular database. However, you can restrict what the user selects.

On Ubuntu, by default, MySQL will not allow you to write to file. You should set secure_file_priv in the config file and restart MySQL. Open the config file and add secure_file_priv = /var/lib/mysql:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Restart the MySQL server:

sudo systemctl restart mysql

The following statement will save the output into a CSV format:

SELECT first_name, last_name INTO OUTFILE 'results.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM employees WHERE hire_date<'1986-01-01' LIMIT 10;

Query OK, 10 rows affected (0.00 sec)

sudo cat /var/lib/mysql/employees/results.csv

"Bezalel","Simmel"
"Sumant","Peac"
"Eberhardt","Terkki"
"Otmar","Herbst"
"Florian","Syrotiuk"
"Tse","Herber"
"Udi","Jansch"
"Reuven","Garigliano"
"Erez","Ritzmann"
"Premal","Baek"

Saving as a table

You can save the results of a SELECT statement into a table.

You can save the titles into a new titles_only table:

CREATE TABLE titles_only AS SELECT DISTINCT title FROM titles;

Query OK, 7 rows affected (0.58 sec)
Records: 7  Duplicates: 0  Warnings: 0

If the table already exists, you can use the INSERT INTO SELECT statement:

INSERT INTO titles_only SELECT DISTINCT title FROM titles;

Query OK, 7 rows affected (0.50 sec)
Records: 7  Duplicates: 0  Warnings: 0

To avoid duplicates, you can use INSERT IGNORE. However, in this case, there is no PRIMARY KEY on the titles_only table. So the IGNORE clause does not make any difference.

SELECT * FROM titles_only;

+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+
14 rows in set (0.00 sec)