mysqlcsv

Does MySQL have a way to read directly from a csv file instead of importing it?


Postgresql has a concept of foreign tables and can use a csv file as the table source. Is there a way to do this in MySQL? In my case I do not want to import the file as it changes every minute (gets rewritten via crontab).

CREATE FOREIGN TABLE IF NOT EXISTS rawdata.system_ps (
  pid text,
  started text,
  cpu_pcnt text,
  mem_pcnt text,
  command text
) 
SERVER "import" options (
  FILENAME '/var/tmp/system_ps.csv',
  FORMAT 'csv',
  HEADER 'true'
);

Solution

  • MySQL supports a CSV storage engine. It's described in the manual here: https://dev.mysql.com/doc/refman/8.0/en/csv-storage-engine.html

    The syntax is different than the example you show. You can't specify the filename or path. It must be in the data directory, and must be in a file matching the table name. Your external cron process must be able to access that file.

    But it satisfies your requirement of reading data from the CSV file on every query, so if an external process updates the file, the next SQL query reads the updated data. The CSV file is not imported into another storage engine in MySQL.

    Read the manual page I linked to for more details.