0

Possible Duplicate:
Insert file contents into MySQL table's column

I want to load the content of a file into a specific cell of a table, or to be more precise i want to update a specific cell and the content of the cell after updating should be the content of a file.

Preferably by means of the command line. My first attempt that illustrate, what i want to achieve was;

mysql -p -u user -e "update my_table set body='`cat filename`' where id=x;" dbname

But of course mysql have trouble with special characters within the file. Alternatively i would agree with a perl one-liner.

Community
  • 1
  • 1
user1146332
  • 2,630
  • 1
  • 15
  • 19
  • I'm unclear on some details. Could you give a (short) example of a complete file, together with the SQL statements that you could use if that were the file? – ruakh Jan 12 '12 at 20:18
  • (In particular, what I'm unclear on is: Is the entire file supposed to go into a single table-cell, or is each line of the file supposed to go into a separate row?) – ruakh Jan 12 '12 at 20:27
  • @ruakh The entire file should be transferred into a single table-cell of an existing row. You are right 'column' is very misleading, i will change that – user1146332 Jan 12 '12 at 22:07

1 Answers1

1

You may be able to use the LOAD_FILE function given in the answer to the question that Charles Sprayberry links to above. That would look like this:

mysql -p -u user -e "update my_table set body=load_file('/path/to/filename') where id=x;" dbname

If that doesn't work out for you (e.g., due to permissions issues or whatnot), then you solve your problem differently, by using features of Bash and sed to fix your existing command, as follows:

Most special characters, such as newlines and whatnot, seem to be just fine inside single-quoted strings. The only characters that should be a problem are ' (which would be interpreted as terminating the string) and \ (which would be interpreted as introducing an escape sequence), and both of these can be addressed by prefixing a \ (for example, '\'' and '\\' are string literals denoting ' and \, respectively). This sed script inserts a \ before any ' or \:

s/\('\|\\\)/\\\1/g

which you can invoke from Bash like this:

sed 's/\('"'"'\|\\\)/\\\1/g' filename

(for example, if filename contains this:

a \ b \ c
d ' e ' f

then sed 's/\('"'"'\|\\\)/\\\1/g' filename will print this:

a \\ b \\ c
d \' e \' f

). But you can't just wrap the above in `...`, because these characters are special to Bash as well, and it will get confused. (I'm assuming you're using Bash?) Instead, you need to use $(...), which is a Bash notation that's equivalent to `...` except that it better handles various details like this. (I recommend always using $(...) over `...`.) So your overall command would be:

mysql -p -u user -e "update my_table set body='$(sed 's/\('"'"'\|\\\)/\\\1/g' filename)' where id=x;" dbname
ruakh
  • 175,680
  • 26
  • 273
  • 307
  • Thank you very much! Your first suggestion works after i gave me the corresponding rights with `GRANT FILE ON *.* TO me@localhost` – user1146332 Jan 13 '12 at 14:55