Q:

Insert file into SQL field and extract back into file

I created simple table:
SHOW DATABASES;
CREATE DATABASE observe;

USE observe;

CREATE TABLE `see_me` (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1), 
signup_date DATE,
file_contents LONGBLOB // or VARBINARY(MAX)
);

and a file called t.txt. How can I insert the file into the field file_contents with sql commands and extract it likewise into a file? 
The insert may be something like 
 INSERT INTO see_me(id, file_contents) 
 SELECT '1', BulkColumn FROM OPENROWSET (BULK '/Users/usr_00/lab/t.txt', SINGLE_BLOB) as ...;

Can someone advise?

A:

You can insert a file content to a field with help LOAD_FILE function. For example:
INSERT INTO see_me (file_contents)
VALUES (LOAD_FILE('/var/lib/mysql-files/myfile.txt'));

or in an update query
UPDATE see_me
SET file_contents = LOAD_FILE('/var/lib/mysql-files/myfile.txt');

If you want to extract the file content into the new file you could use SELECT .. INTO DUMPFILE. For example:
SELECT file_contents INTO DUMPFILE '/var/lib/mysql-files/myfile.txt'
FROM see_me
WHERE id = <your_id>;

But, as said @VonC, keep in mind if  secure_file_priv system variable is nonempty you should work with files which only located in a defined directory 

