CODEWITHSUNDEEP
mysqlfile
Ursa Major
Ursa Major

Reputation: 891

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?

Upvotes: 3

Views: 19277

Answers (3)

Pallamolla Sai
Pallamolla Sai

Reputation: 2493

After working a lot the following thing worked for me. First thing is we have to make sure whether we have proper permissions to read files from specific directory. For me it wasn't there. So I changed some files.

  1. I went to this location (C:\ProgramData\MySQL\MySQL Server 8.0\my.ini) and checked the path secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads" and this location I created a file "check.txt" in which I have content.

  2. I went to mysql workbench and checked some properties. Run the following command

    SHOW GLOBAL VARIABLES LIKE 'local_infile';

if it shows OFF we have to make it ON using following command.

SET GLOBAL local_infile=1;

  1. Now let's check directory of secure-file-priv it should 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/uploads' run the below command

    SHOW VARIABLES LIKE "secure_file_priv" //you will see path as 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/uploads'

Now till now we changed the properties. Now we are ready to read file and create files using mysql.

  1. Read data from txt file (I have already created check.txt in "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"). Later created a temporary table in which I am storing the data in check.txt 

    CREATE TEMPORARY TABLE foo (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,content 
longblob); // created temporary table called "foo"

// reading the data from txt file

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/check.txt'
INTO TABLE foo 
#FIELDS TERMINATED BY '' ENCLOSED BY '' ESCAPED BY ''
LINES STARTING BY '' TERMINATED BY '' // mysql creates new record for every line
(content); 

 //by running above command entire txt data came to content column (mysql created only 1 row bcz we didn't mention lines terminated by. So it will copy entire txt data in 1st row of "content" column of "foo" table)

  2. Writing content to file(in "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads" directory )

    SELECT * FROM foo
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/out.txt'; // out.txt will automatically created by mysql

BY running above commands you can perform read and write operations. You can use above "foo" table data in your "see_me" table and perform read or write operations.

data from "foo" table where id="1" into "see_me" table (file_contents)

Following links helped me.

mysql bugs documentation

setting secure-file-priv in mac and ubuntu

my.cnf file in mac

my-cnf location

Upvotes: 2

Maksym Fedorov
Maksym Fedorov

Reputation: 6456

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

Upvotes: 4

VonC
VonC

Reputation: 1323223

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

Upvotes: 2

Related Questions