4

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?

Improve this question
16
  • Can somebody help with what. Please tell us what the probelm is Commented Apr 16, 2019 at 23:53
  • stackoverflow.blog/2018/04/26/… "Let’s stop judging users for not knowing things. (We’re a Q&A site!) " Commented Apr 17, 2019 at 4:59
  • 1
    Mysql does not have openrowset, have you looked at load data infile? and what does t.text look like? Commented Apr 17, 2019 at 7:04
  • t.txt can be of any form from text to binary. what is the mysql load data infile command? Commented Apr 17, 2019 at 16:20
  • I am seeing this ** mysql> UPDATE see_me -> SET file_contents=LOAD_FILE('/Users/usr/lab/t.txt') -> WHERE id=1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 ** however, the table remains unchanged. Commented Apr 17, 2019 at 16:36

3 Answers 3

Reset to default
5
+75

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The field still states 'NULL'.
What value has your secure_file_priv variable?
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

Improve this answer

Comments

2

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

Improve this answer

2 Comments

The folder structure is different in mac and ubuntu. I am not able to locate the said path or file, i.e. my.ini, etc.
If you are using mqsqlworkbench run the command I mentioned. while running the command if you are getting any sql error(this operations is not allowed etc. ) then you have to change my.cnf file for permissions(you can change my.cnf file by going through the links I mentioned). Could you please just go through the links?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.