Mysql – How to use LOAD DATA LOCAL INFILE for specific columns

insertmariadb-10.5MySQLquery

I have a directory of text files, as the filename is the id, and the content text for this table:

CREATE TABLE texts
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

I use PHP glob function to find files to INSERT file by file (row by row) as

INSERT INTO texts (ID,Added,Text) VALUES ($id,CURDATE(),'$text')";

$id is the filename, and $text the entire content of the file.

How can I use LOAD DATA LOCAL INFILE to directly INSERT into the database and to avoid reading the large text into PHP variable?

Best Answer

Mysql can do following with user defined variables

 LOAD DATA LOCAL INFILE '/your_path/yourfule.csv'  
 INTO TABLE texts 
 FIELDS TERMINATED BY ','   
 LINES TERMINATED BY '\n'  
 ( @ID
 , @Text
 )
 SET `ID`   = @ID
   , `Added` = CURDATE()
   , `Text`     = @Text
 ;

Please check, if MySQL allows from that path to insert data.

And also you may have to change the options for INFILE

Related Question