MySQL 5.1 Reference Manual  /  Storage Engines  /  The CSV Storage Engine

14.13 The CSV Storage Engine

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

To enable the CSV storage engine if you build MySQL from source, invoke configure with the --with-csv-storage-engine option.

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.12 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 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)

Starting with MySQL 5.1.9, 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.

Download this Manual
User Comments
  Posted by Neil Davis on June 17, 2006
Using the windows version of mysql 5.1.11 beta:

CSV rows need to be terminated with a unix newline.

If you load a csv data into a csv table by replacing a blank csv table file, with a file already loaded with data, as described in csv overview, replacing new table with one already populated with data, and the data originates from a windows box, you need to first replace the \r\n with \n (windows linefeed/carriage return with a unix newline) or the table will not be loaded properly.

You will get a message about a crashed table when you attempt to access it.

Open file in a program like PFE 32 that can "Save As" replacing the crlf with unix newlines and mysql will then be able to access table normally.
  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.