PlanetScale : Import / Export to CSV in MySQL Shell requires Access to Servers Filesystem

antonwilhelm 0
Viewed 28

If I want to do an export to csv or an import from csv, I will need access to the filesystem from within my MySQL Database Shell.

e.g.

SELECT id, filename
FROM attachments
INTO OUTFILE '/tmp/results.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

I am using PlanetScale right now, and i don't know how or where I can get access to the servers filesystem in order to import or export data from within the mysql shell.

export-to-csv shell mysql database
1 Answers

See https://vitess.io/docs/14.0/reference/compatibility/mysql-compatibility/

The SELECT ... INTO form of SELECT in MySQL enables a query result to be stored in variables or written to a file. Vitess supports SELECT ... INTO DUMPFILE and SELECT ... INTO OUTFILE constructs for unsharded keyspaces but does not support storing results in variable. Moreover, the position of INTO must be towards the end of the query and not in the middle. An example of a correct query is as follows:

SELECT * FROM <tableName> INTO OUTFILE 'x.txt' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\t' LINES TERMINATED BY '\n'

For sharded keyspaces this statement can still be used but only after specifying the exact shard with a USE Statement.

(emphasis mine)

So you must know which server to use, because it's required that you specify the shard.

Bill Karwin0
Related