Using LOAD DATA INFILE command in a stored procedu

问题:

Is this possible at all? I read on a few websites that one can not use LOAD DATA command inside a procedure.

回答1:

No, it is not possible due to security reasons, I suppose. However, you can generate "LOAD DATA" queries for tables in a database using this trick (return a series of queries for each table: truncate table before load data, then disable keys, then load data, then enable keys):

SELECT CONCAT('TRUNCATE TABLE ',table_name,'; ALTER TABLE ',table_name,' DISABLE KEYS;    LOAD DATA INFILE "',table_name,'.txt" INTO TABLE ',table_name,' FIELDS TERMINATED BY "\\t" LINES TERMINATED BY "\\n"; ALTER TABLE ',table_name,' ENABLE KEYS; ')
FROM information_schema.`TABLES` as infs
WHERE infs.`TABLE_SCHEMA`=DATABASE()
AND infs.`TABLE_TYPE`!='VIEW';

After you run this query, rows resulted are queries for transferring data. I use it when moving a full database content to another. Of course, in that query you can filter your needed tables using more conditions. Hope it helps.