mysqlcsvstorage-engines

When to use CSV storage engine for MySQL?

From the docs, it states:

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

What are the advantages of this? Here are some I can think of:

  1. You can edit the CSV files using simple text editor (however, you can export data easily using SELECT INTO OUTFILE)
  2. Can be easily imported into Spreadsheet programs
  3. Lightweight and maybe better performance (wild guess)

What are some disadvantages?

  1. No indexing
  2. Cannot be partitioned
  3. No transactions
  4. Cannot have NULL values

Granted this (non-exhaustive) list of advantages and disadvantages, in what practical scenarios should I consider using the CSV storage engine over others?

Solution

  • I seldom use the CSV storage engine. One scenario I have found it useful, however, is for bulk data imports.

    1. Create a table with columns matching my input CSV file.
    2. Outside of mysql, just using a shell prompt, mv the CSV file into the MySQL data dictionary, overwriting the .csv file that belongs to my table I just created.
    3. ALTER TABLE mytable ENGINE=InnoDB

    Voilà! One-step import of a huge CSV data file using DDL instead of INSERT or LOAD DATA.

    Granted, it's less flexible than INSERT or LOAD DATA, because you can't do NULLs or custom overrides of individual columns, or any "replace" or "ignore" features for handling duplicate values. But if you have an input file that is exactly what you want to import, it could make the import very easy.