Copy the contents of a file to one field in mysql

Question

Is it possible to copy the contents of a file into a field in a mysql table from the command line? Either at the command line or the mysql prompt. I don't want to have to write a script if there is an easier way.

Ideally, I'd like something like:

UPDATE MYTABLE SET MYFIELD=READ_CONTENTS_OF_FILE('myfile.txt') WHERE ID=1234;

Obviously that's not a real command but it illustrates what I'd like to do.

This works from the command line:

echo UPDATE MYTABLE SET MYFIELD=\'`cat myfile.txt`\' WHERE ID=1234 |mysql

But it doesn't preserve new lines and it gets screwed up if the file contains apostrophes.

This is something I've looked off-and-on into for years now. The issue came up so seldom I would quickly give up and just copy/paste into a gui client. It would be a handy trick for testing purposes once in a while.

Thanks!

Answer 1

I think you're looking for the MySQL load_file function:

UPDATE MYTABLE SET MYFIELD=LOAD_FILE('myfile.txt') WHERE id=1234;