如何将多个文件加载到mysql中自动添加日期信息? [英] How can I load many files into mysql automatically add date information?

问题描述

我有很多文件，例如:20170319、20170320 ...对于每个文件，我有 2 列，一列用于用户名，另一列是数据.我已经创建了一个表

I have many files, for example: 20170319, 20170320 ... For each file I have 2 columns, one for username and the other was data. I've created a table

create table A(user varchar(35), date date, data varchar(35), primary key(user, date));

然后，我想将这些文件加载​​到数据库中，并在日期字段中使用文件名作为特定日期.

Then, I want to load those files into database, and use filename as specific date in date field.

我还能用吗:

Load data infile '20170320' into table A

推荐答案

答案是你不能单独在 MySQL 中做到这一点，你需要使用一个外部程序或脚本来构建load data infile 带有适当 SET 子句的语句文件名:

The answer is that you cannot do this in MySQL alone, you need to use an external program or script that builds the load data infile statements with the appropriate SET clause derived from the name of the file:

SET 子句可用于提供不是从输入派生的值文件.以下语句将 column3 设置为当前日期和时间:

The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

这样做的原因是:

1. load data infile 不能使用文件名作为输入变量
2. 没有使用 prepare 的 MySQL 准备语句声明，也不是存储过程 允许使用 load data infile 语句.

1. load data infile cannot use the file name as an input variable
2. neither MySQL prepared statements using the prepare statement, nor stored procedures are allowed to use the load data infile statement.

这篇关于如何将多个文件加载到mysql中自动添加日期信息?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！