Home / server / Questions / 333713
Accepted
Jeff
Asked: 2011-11-23 07:40:32 +0800 CST

CSV engine on MySQL server

  • 772

I don't think that this is a programming question so I am going to ask it here -

Reading the book high performance mysql, I read about the CSV engine. The paragraph says:

The CSV engine can treat comma-separated values (CSV) files as table, but it does not support indexes on them. This engine lets you copy files in and out of the database while the server is running. If you export a CSV file from a spreadsheet and save it in the MySQL server's data directory, the server can read it immediately. Similary, if you write data to a CSV table, an external program can read it right away. CSV tables are especially useful as a data interchange format and for certain kinds of logging.

What I get from this paragraph is that I can copy a .CSV file into the data directory of database, and it should show as a table that is able to be read from. However, whenever I copy a test .csv file into the directory, it does not appear as a table. I can't access it.

I am using MySQL 5.5 also

Does anyone know why this is not working, or what I am doing wrong?

mysql database csv mysql5.5

1 Answers

  1. Best Answer

    I opted to try this out on my MySQL server to see what the problem was. It appears that you need to create the table in MySQL (with engine=csv specified) so that the table will appropriately be added to MySQL and it will keep track of the table field metadata. In my testing, I was unable to make MySQL learn of the csv file without first using "create table."

    Example:

    1. Create new csv named foo in this example a column named "i" for an integer, and "c" for a character field: CREATE TABLE foo (i int not null, c char(10) not null) engine=csv;
    2. foo.frm, foo.CSM, foo.CSV are created in /var/lib/mysql/data/dbname, you will note that foo.CSV is zero-byte.

    3. Copy your existing csv (that matches the format defined for the table) into foo.CSV:

      1,"FOOBAR"
2,"FOOBAZ"
3,"BARBAZ"

    4. In mysql, select * from foo should produce the following result:

      mysql> select * from foo;
+---+--------+
| i | c      |
+---+--------+
| 1 | FOOBAR |
| 2 | FOOBAZ |
| 3 | BARBAZ |
+---+--------+
3 rows in set (0.00 sec)
    • 5