CSV Overview

The CSV Storage Engine can read and append to files stored in CSV (comma-separated-values) format.

However, since MariaDB 10.0, a better storage engine is able to read and write such files: CONNECT.

The CSV storage engine and logging to tables

The CSV storage engine is the default storage engine when using logging of SQL queries to tables.

mysqld --log-output=table

CSV Storage Engine files

When you create a table using the CSV storage engine, three files are created:

  • <table_name>.frm
  • <table_name>.CSV
  • <table_name>.CSM

The .frm file is the table format file.

The .CSV file is a plain text file. Data you enter into the table is stored as plain text in comma-separated-values format.

The .CSM file stores metadata about the table such as the state and the number of rows in the table.

Limitations

  • CSV tables do not support indexing.
  • CSV tables cannot be partitioned.
  • Columns in CSV tables must be declared as NOT NULL.
  • No transactions.
  • The original CSV-format does not enable IETF-compatible parsing of embedded quote and comma characters. From MariaDB 10.1.8, it is possible to do so by setting the IETF_QUOTES option when creating a table.

Examples

Forgetting to add NOT NULL:

CREATE TABLE csv_test (x INT, y DATE, z CHAR(10)) ENGINE=CSV;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

Creating, inserting and selecting:

CREATE TABLE csv_test (
  x INT NOT NULL, y DATE NOT NULL, z CHAR(10) NOT NULL
  ) ENGINE=CSV;
INSERT INTO csv_test VALUES
    (1,CURDATE(),'one'),
    (2,CURDATE(),'two'),
    (3,CURDATE(),'three');
SELECT * FROM csv_test;
+---+------------+-------+
| x | y          | z     |
+---+------------+-------+
| 1 | 2011-11-16 | one   |
| 2 | 2011-11-16 | two   |
| 3 | 2011-11-16 | three |
+---+------------+-------+

Viewing in a text editor:

$ cat csv_test.CSV
1,"2011-11-16","one"
2,"2011-11-16","two"
3,"2011-11-16","three"

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.