Insert file into SQL field and extract back into file

Question

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?

Answer 1

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

Answer 2

Try, as in here to check where MySql has upload access, as specified in the manual:

select @@secure_file_priv;

If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.
(Prior to MySQL 8.0.17, the file must be readable by all, not just readable by the server.)

On Windows, you would have to escape the \, as show in this answer