Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.4Mb
PDF (A4) - 39.4Mb
PDF (RPM) - 38.7Mb
HTML Download (TGZ) - 11.1Mb
HTML Download (Zip) - 11.1Mb
HTML Download (RPM) - 9.8Mb
Man Pages (TGZ) - 213.4Kb
Man Pages (Zip) - 322.5Kb
Info (Gzip) - 3.6Mb
Info (Zip) - 3.6Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  Alternative Storage Engines  /  The CSV Storage Engine

15.4 The CSV Storage Engine

The CSV storage engine stores data in text files using comma-separated values format.

The CSV storage engine is always compiled into the MySQL server.

To examine the source for the CSV engine, look in the storage/csv directory of a MySQL source distribution.

When you create a CSV table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine also creates a data file. Its name begins with the table name and has a .CSV extension. The data file is a plain text file. When you store data into the table, the storage engine saves it into the data file in comma-separated values format.

       ENGINE = CSV;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
| i | c          |
| 1 | record one |
| 2 | record two |
2 rows in set (0.00 sec)

Creating a CSV table also creates a corresponding Metafile that stores the state of the table and the number of rows that exist in the table. The name of this file is the same as the name of the table with the extension CSM.

If you examine the test.CSV file in the database directory created by executing the preceding statements, its contents should look like this:

"1","record one"
"2","record two"

This format can be read, and even written, by spreadsheet applications such as Microsoft Excel or StarOffice Calc.

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Ben Clewett on August 24, 2011
On UNIX is it also possible to use a symbolic link to point to any file on the system.

Delete the .CSV file, then symbolically link to your own file as the name you have deleted.


# rm passwd.CSV
# ln -s /etc/passwd passwd.CSV

You get the idea...

  Posted by Chris Wagner on October 22, 2011
The CSV engine is great for easily bringing a spreadsheet into a REAL DATABASE for analysis, manipulation, and extraction.

CREATE TABLE memfromcsv SELECT * FROM csvtable;

Now u can create indexes to do the analysis. And, create another CSV table and select into it. Then it can be pulled out and loaded right into Excel. Do FLUSH TABLES to make sure it's clean first. This is only one scenario, there are countless possibilities.

It's also an easy way to get a CSV file out of a table.

CREATE TABLE csvtable SELECT * FROM innodbtable;

  Posted by Marcin Szalowicz on March 2, 2013
There is missing info that when manually changing .csv file in the mysql datadir, you should issue: FLUSH TABLE csv_table; so mysql will reload it contents.
FLUSH TABLE csv_table; is not required when you're replacing the file i.e.delete/create from scratch.
  Posted by Mathias Brem Garcia on July 23, 2014
Yeah Clewett!

I make this! On my case, i needed read MySQL Error log in database, because i needed make a plugin for check errors on MySQL Error Log, but my plugins access database and don't accessed file system. So, i make this:

mysql> create table mysql.error_log(msg text not null) engine=CSV;

shell> rm -f /var/lib/mysql/mysql/error_log.CSV
shell> ln -s /var/log/mysqld.log /var/lib/mysql/mysql/error_log.CSV

So, now i read the MySQL Error Log with querys, look this:

mysql> select count(*) as ERROR from mysql.error_log where msg like '%erro%';

If the query return ERROR more then 1, alert CRITICAL.

It's an idea for another cases or to monitoring like my case


Sign Up Login You must be logged in to post a comment.