MySQL CSV Storage Engine (original) (raw)

Summary: in this tutorial, you will learn about MySQL CSV storage engine and how to create tables that use the CSV storage engine to store data in CSV format.

Introduction to MySQL CSV storage engine

The CSV storage engine stores table data as a plain text file in comma-separated values (CSV) format.

The CSV storage engine is useful when you need to directly share the table data with other applications that use the CSV format.

It’s crucial to be aware of the limitations of the CSV storage engine, including:

Lack of indexing: CSV tables do not support indexes, which impacts query performance for large datasets.
Do not support transactions: CSV tables do not support transactions, making them less suitable for applications that require critical data integrity.

To check if your MySQL Server supports CSV storage engine, you use the following query:

SELECT engine, support FROM information_schema.engines ORDER BY engine;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+---------+ | engine | support | +--------------------+---------+ | ARCHIVE | YES | | BLACKHOLE | YES | | CSV | YES | | FEDERATED | NO | | InnoDB | DEFAULT | | MEMORY | YES | | MRG_MYISAM | YES | | MyISAM | YES | | ndbcluster | NO | | ndbinfo | NO | | PERFORMANCE_SCHEMA | YES | +--------------------+---------+ 11 rows in set (0.01 sec)Code language: JavaScript (javascript)

In the output, the engine column stores storage engine types and the support column indicates whether a storage engine is supported.

Let’s take an example of using the CSV storage engine.

1) Creating a CSV table

To create a table that uses the CSV storage engine, you set the ENGINE clause to "CSV" in the CREATE TABLE statement.

For example, the following creates a table called contacts that uses the CSV storage engine:

CREATE TABLE contacts( name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL ) ENGINE = CSV;Code language: SQL (Structured Query Language) (sql)

Note that the CSV table doesn’t support the column with the AUTO_INCREMENT attribute.

When you create a table that uses the CSV storage engine, MySQL creates two files in the database directory:

Data File: This is a plain text data file with a name that begins with the table name and has a .CSV extension. The data file stores the actual data in comma-separated values format.
Metafile: The corresponding metafile with the same name as the table and the .CSM extension. The metafile file stores the state and the number of rows of the table.

For example, when you create the contacts CSV table, MySQL creates two files:

contacts.CSV – stores the actual data.
contact.CSM – stores the metadata of the table such as the number of rows.

2) Inserting data into a CSV table

The following statement inserts some rows into the contacts table:

INSERT INTO contacts (name, email) VALUES ('john', '[[email protected]](/cdn-cgi/l/email-protection)'), ('jane', '[[email protected]](/cdn-cgi/l/email-protection)');Code language: SQL (Structured Query Language) (sql)

When you insert rows into a CSV table, MySQL adds them to the corresponding .CSV file, such as contacts.CSV. In addition, it updates the metafile (contacts.CSM) with the new information.

3) Querying data from a CSV table

The following statement retrieves data from the contacts table:

SELECT name, email FROM contacts;Code language: SQL (Structured Query Language) (sql)

Output:

If you examine the contacts.csv file from the data directory, you’ll see the same records:

"john","[[email protected]](/cdn-cgi/l/email-protection)" "jane","[[email protected]](/cdn-cgi/l/email-protection)"Code language: JavaScript (javascript)

Summary

CSV storage engine stores data of tables entirely in CSV files with the names matching those of the tables.
Use the CSV storage engine when you want to share table data directly with scripts or applications that consume CSV files.

Was this tutorial helpful?