Upload image directly through mySQL Command Line

LOAD_FILE works only with certain privileges and if the file is on the server. I've found out a way to make it work completely client side:

mysql -e "update mytable set image=FROM_BASE64('`base64 -i image.png`')" DBNAME

The idea is to encode the image to base64 on the fly and let then MySql decode it.


This is a variation on Teudimundo's answer that works with older MySQL versions, where Base64 functions are not available:

mysql -e "update mytable set col = x'$(xxd -p image.png | tr -d \\n)' where ..."

The trick is to use xxd -p to convert a binary file to a plain hexdump:

$ xxd -p /usr/share/font-manager/data/blank.png
89504e470d0a1a0a0000000d4948445200000040000000400806000000aa
6971de000000274944415478daedc1010d000000c220fba77e0e37600000
00000000000000000000000000c0bd0040400001ee1dbb2f000000004945
4e44ae426082

then using tr -d \\n to remove the newlines, and finally embedding the result into a MySQL-specific hexdump string literal: x'...'


Try using the LOAD_FILE() function.

UPDATE `certain_table`
SET image = LOAD_FILE('/full/path/to/new/image.jpg')
WHERE id = 1234;

See the manual for requirements about the path to the filename, privileges, etc.