Menu:
se-csv-limitations:: CSV Limitations
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 plain text data file having a name that begins with the table name and has a '.CSV' extension. When you store data into the table, the storage engine saves it into the data file in comma-separated values format.
mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL)
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.
File: manual.info.tmp, Node: se-csv-repair, Next: se-csv-limitations, Prev: csv-storage-engine, Up: csv-storage-engine
The 'CSV' storage engine supports the note 'CHECK TABLE': check-table. and note 'REPAIR TABLE': repair-table. statements to verify and, if possible, repair a damaged 'CSV' table.
When running the *note 'CHECK TABLE': check-table. statement, the 'CSV' file is checked for validity by looking for the correct field separators, escaped fields (matching or missing quotation marks), the correct number of fields compared to the table definition and the existence of a corresponding 'CSV' metafile. The first invalid row discovered reports an error. Checking a valid table produces output like that shown below:
mysql> CHECK TABLE csvtest;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.csvtest | check | status | OK |
+--------------+-------+----------+----------+A check on a corrupted table returns a fault such as
mysql> CHECK TABLE csvtest;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.csvtest | check | error | Corrupt |
+--------------+-------+----------+----------+To repair a table, use *note 'REPAIR TABLE': repair-table, which copies as many valid rows from the existing 'CSV' data as possible, and then replaces the existing 'CSV' file with the recovered rows. Any rows beyond the corrupted data are lost.
mysql> REPAIR TABLE csvtest;
+--------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| test.csvtest | repair | status | OK |
+--------------+--------+----------+----------+Warning:
During repair, only the rows from the 'CSV' file up to the first damaged row are copied to the new table. All other rows from the first damaged row to the end of the table are removed, even valid rows.
File: manual.info.tmp, Node: se-csv-limitations, Prev: se-csv-repair, Up: csv-storage-engine
The 'CSV' storage engine does not support indexing.
Partitioning is not supported for tables using the 'CSV' storage engine.
All tables that you create using the 'CSV' storage engine must have the 'NOT NULL' attribute on all columns.
File: manual.info.tmp, Node: archive-storage-engine, Next: blackhole-storage-engine, Prev: csv-storage-engine, Up: storage-engines