27

I know there's the LOAD DATA INFILE statement, which allows me to INSERT structured data into a table.

What I'm curious about, is whether it is possible to INSERT contents of a file into single column. So, is something like this:

INSERT INTO my_table (stamp, what) VALUES (NOW(), LOAD DATA INFILE 'my_file');

possible?

Martin Tóth
  • 1,747
  • 3
  • 24
  • 35

2 Answers2

46

Yes it's possible. You can use the LOAD_FILE() function:

CREATE TABLE my_table (stamp datetime, what text);

INSERT INTO my_table (stamp, what) VALUES (NOW(), LOAD_FILE('/tmp/my_file.txt'));

You'll have to make sure that the file is readable by MySQL, and that your MySQL user has the FILE privilege. This is the same privilege required for LOAD DATA INFILE.

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • 1
    Unfortunately, LOAD_FILE seems to be buggy to broken on Linux. http://bugs.mysql.com/bug.php?id=38403 – ehfeng Nov 27 '12 at 15:49
  • Thank you for both the Question and this Answer! Exactly what I needed. The last few days had been searching for days, evaluating different suggestions that came up first, and ending up implementing ineffective solutions! Aside note: My "what" columns must contain the entire JSON or XML docs, so make sure to set datatype = LONGTEXT. Also, important to know the file to load must be on the same machine to SQL server! – Panini Luncher Oct 28 '19 at 19:26
  • LOAD_FILE() requirement/limitations https://stackoverflow.com/questions/18069054/mysql-load-file-loads-null-values#answer-23618521 – Panini Luncher Oct 28 '19 at 20:47
  • LOAD_FILE() alternative = If the file upload is to manually update table content (like mine!) and not part of programmatical upload, then alternatively use MySQL Workbench upload file menu which allow local file to remote server. Thanks to this: https://stackoverflow.com/questions/10729824/how-to-insert-blob-and-clob-files-in-mysql#answer-29946724 – Panini Luncher Oct 28 '19 at 20:51
  • @ehfeng FWIW it works for me on `mysql Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2`. Test with a file in e.g. `/var/tmp/` (not in someone’s home directory) and with `chown mysql:mysql `. – Guildenstern May 22 '22 at 13:29
  • 1
    @Guildenstern Maybe it's been fixed in the interim decade? – ehfeng May 24 '22 at 03:36
0

edit: I understood you wrong, sorry, Daniel Vassallo has the right answer. May this is useful anyway

I think you are looking for BLOB or TEXT, see docs. If you want to store file date inside the datebase, these are what you are possibly looking for

DrColossos
  • 12,656
  • 3
  • 46
  • 67