在存储过程中使用LOAD DATA INFILE命令 [英] Using LOAD DATA INFILE command in a stored procedure

问题描述

这有可能吗?我在一些网站上读到一个程序内不能使用LOAD DATA命令.

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

推荐答案

不，我认为由于安全原因，这是不可能的.但是，您可以使用此技巧为数据库中的表生成"LOAD DATA"查询(为每个表返回一系列查询:在加载数据之前截断表，然后禁用键，然后加载数据，然后启用键):

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.

这篇关于在存储过程中使用LOAD DATA INFILE命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！