fishScript.com Script con annotazioni in italiano e inglese

Mysql

Home >Mysql > mysql load file into a table

LOAD_FILE is a mysql function to load files on a table.
For example, let's create a simple text file and make sure mysql has the rights.

server1 ~ $ echo 'Hello I am a nice text file!' >  /tmp/test.txt
server1 ~ $ chown mysql:mysql /tmp/test.txt

Now let's create a table with a longblob column and try to load some file into.

server1 ~ $ mysql
Server version: 5.6.21-70.0-log Percona Server (GPL), Release 70.0, Revision 688
mysql> use demodb ;
Database changed

mysql> CREATE TABLE 
    -> `test` (  `id` bigint(20) NOT NULL,  `data` longblob,  
    -> `file_type` varchar(20) DEFAULT NULL, 
    ->  PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.01 sec)

Then, let's insert the file on the table.

mysql> INSERT INTO test (id, data) VALUES(1, LOAD_FILE('/tmp/test.txt'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+-------------------------------+-----------+
| id | data                          | file_type |
+----+-------------------------------+-----------+
|  1 | Hello I am a nice text file!
| NULL      |
+----+-------------------------------+-----------+
1 row in set (0.00 sec)

When loading big files, note that max_allowed_packet server parameter defines the the max packet size and if this is not tuned you may have an error.
For example if max_allowed_packet is set to 16M while trying to insert a 20M file you will get the "Warning 1301".

-- max_allowed_packet is set to 16M
mysql> show variables like 'max_allowed_packet' ;
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)


-- max_allowed_packet is set to 16M 
mysql> INSERT INTO test (id, data) VALUES(2, LOAD_FILE('/tmp/sample_file_20Msize.pdf'));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1301 | Result of load_file() was larger than max_allowed_packet (16777216) - truncated |

In this case you will need to increase accordingly the value on the configuration file (usually my.cnf) and reload mysql server .

Warning: include(ads/text468x15.html): failed to open stream: No such file or directory in D:\inetpub\webs\fishscriptcom\documents\view_document.php on line 131

Warning: include(): Failed opening 'ads/text468x15.html' for inclusion (include_path='.;C:\php\pear') in D:\inetpub\webs\fishscriptcom\documents\view_document.php on line 131

Tutorial
Creazione di un database e assegnazione di grant e password Primi passi [Mysql]
mysql dictionary tables how list and find tables and other objects of databases schemas [Mysql]
mysql inserting data with LOAD DATA INFILE common errors and solutions [Mysql]
Query per generare sql dinamici tecniche dba [Mysql]
Shell scripts executing mysql instruction basic samples to execute sql and other commands via script and put results on file [Mysql]
mysql load file into a table Example with LOAD_FILE function to insert a file in longblob column [Mysql]
Script
Running existing sql files through prompt (like dos) Executing sql script [Mysql]
Utenti e grant Assegnazioni di grant su tutto il database a nuovi utenti [Mysql]
Comandi
Data di oggi Stampa data corrente [Mysql]
Show databases visualizza i databases Comandi iniziali [Mysql]
Shutdown Chiudere Mysql [Mysql]
Uscire dalla shell di mysql Comandi di base [Mysql]
Funzione CONCAT Concatenare i valori delle colonne di una query [Mysql]
CURRENT_DATE() Data odierna [Mysql]
Drop index Cancellare un indice [Mysql]
Estrazione di occorrenze casuali da una query Ordinamento attraverso la funzione RAND() [Mysql]
Esempio formattazione data (giorno,mese,anno) Formattare una data all'interno di un'istruzione SELECT con la funzione DATE_FORMAT [Mysql]
Creazione di un database e di una semplice tabella Primi passi con Mysql [Mysql]
Inserire dei dati Istruzioni di base [Mysql]
Login prompt mysql Accedere a mysql dalla shell [Mysql]
Visualizzare gli indici associati ad una tabella show index [Mysql]
Creazione indice Sintassi e finalità [Mysql]
QUERY: Clausola WHERE e LEFT JOIN [Mysql]

Marco Magnani marcomagnani@fishscript.com

Argomenti

Mysql

Cerca