I’ve just started using the CSV engine for a practical application and I’ve come across a few subtle and unexpected issues/limitations.
First, you can’t create any columns in your CSV table nullable.
mysql> create table t1(i INT) ENGINE=CSV; ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns
RTFM shows this was introduced in 5.1.23. See CSV Limitations
The second and more annoying was creating a CSV table, inserting a sample row (just to check the syntax), then replacing the file ([datadir]/[schema]/[table].CSV) with the same format, but with additional generated rows. This was to no avail when attempting to SELECT from the table.
The solution was to do a REPAIR TABLE [table] in order to see the newly refreshed data.
Futhermore, some more RTFM shows in Repairing and Checking CSV Tables
an important caveat to using the CSV engine and loading data directly by file copy.
_Warning: Note that 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.
_
Buyer beware.
