使用Set使用哈希和日期加载数据本地文件 [英] Load Data Local Infile with Hash and Date Using Set
问题描述
我对此无能为力.我已经对此进行了彻底研究,似乎无法解决.我有以下PHP语句:
I am at wits end on this. I have researched this to death and cannot seem to resolve. I have the following PHP statement:
$sqlstatement="LOAD DATA LOCAL INFILE '$temp' INTO TABLE contacts FIELDS
TERMINATED BY ','
LINES TERMINATED BY '\r'
IGNORE 1 LINES
(@ignore, @ID, @Name, @Email, @Type, @Hash, @Time)
SET Date_Update=date('Y-m-d')
";
我的CSV文件包含以下标题行:ID,用户名,用户电子邮件,类型.
My CSV file includes the following header row: ID, User Name, User Email, Type.
ID User Name User Email Type
12345 Jackson, F fj@me.com Level 1
67890 Stewart, J js@me.com Level 1
43210 Fuller, T tf@me.com Level 2
62295 Lewis, M ml@me.com Level 2
CSV文件还具有其他不需要导入的字段.希望我可以选择要导入的字段.不知道该技术.
The CSV file also has extra fields which I do not need to import. Was hoping I could select which fields to import. Not sure of the technique.
我的MYSQL表具有以下字段:标记(自动递增),ID,名称,电子邮件,类型,哈希,日期更新,时间更新.
My MYSQL table has the following fields: Tag (auto-increment), ID, Name, Email, Type, Hash, Date_Update, Time_Update.
我似乎无法弄清楚如何在此命令中使用SET功能.我将@ignore设置为跳过标签(自动递增字段).
I can't seem to figure out how to use the SET feature in this command. I set @ignore to skip the Tag (auto-increment field).
- 如何将CSV用户名"字段输入MYSQL名称"字段?
- 如何为MYQL日期更新"字段生成今天的日期?我试过SET Date_Update = date('Y-m-d'),但显示为空白.
- 如何从CSV"ID"字段生成哈希?我尝试了Hash = Hash('sha384',$ pre.@ ID.$ suf)
- How do I get the CSV "User Name" field into the MYSQL "Name" field?
- How do I generate today's date for the MYQL "Date_Update" field? I tried SET Date_Update=date('Y-m-d') but it shows up blank.
- How do I generate a Hash from the CSV "ID" field? I tried Hash=Hash('sha384',$pre.@ID.$suf)
我很茫然.任何帮助将不胜感激.
I'm at a loss. Any help would be appreciated.
推荐答案
这基于Tab '\t'
作为列定界符的假设.
This banks on the assumption of a Tab '\t'
as the column delimiter.
按需修改哈希.我现在只是将sha2()
用作占位符.
Modify your Hash as you will. I just put the sha2()
in as a placeholder for now.
花了一些时间才弄清楚.我不得不进入编辑"您的问题的假设,那里有选项卡.最终在此处由用户
It took a while to figure this out. I had to go into 'Edit' of your question to assume there were tabs. And finally found a peer Answer over Here by user peixe.
您从来没有提供过一个模式,所以我选择了这个模式.如果需要,请添加您的时间"列.但是我将日期和时间折叠成一列,而now()
折叠成了datetime
.
You never supplied a schema, so I flew with this one. Add your Time column if you want. But I collapsed the date and time into one column with now()
into a datetime
.
架构:
drop table if exists pumpkin001;
create table pumpkin001
( Tag int auto_increment primary key,
ID varchar(10) not null,
Name varchar(100) not null,
Email varchar(100) not null,
Type varchar(100) not null,
Hash varchar(1000) not null,
Date_Update datetime not null
);
加载(Windows):
LOAD DATA LOCAL INFILE 'c:\\nate\\file007.txt'
into table pumpkin001
COLUMNS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@ID, @Name, @Email, @Type)
SET ID=@ID, Name=@Name, Email=@Email, Type=@Type,
Hash=sha2(@ID,384), Date_Update=now();
加载(Linux):
LOAD DATA LOCAL INFILE 'file007.txt'
into table pumpkin001
COLUMNS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@ID, @Name, @Email, @Type)
SET ID=@ID, Name=@Name, Email=@Email, Type=@Type,
Hash=sha2(@ID,384), Date_Update=now();
结果:
select Tag,ID,Name,Email,Type,left(Hash,50) as L50_Hash,Date_Update from pumpkin001;
+-----+-------+------------+-----------+---------+----------------------------------------------------+---------------------+
| Tag | ID | Name | Email | Type | L50_Hash | Date_Update |
+-----+-------+------------+-----------+---------+----------------------------------------------------+---------------------+
| 1 | 12345 | Jackson, F | fj@me.com | Level 1 | 0fa76955abfa9dafd83facca8343a92aa09497f98101086611 | 2016-07-14 18:21:40 |
| 2 | 67890 | Stewart, J | js@me.com | Level 1 | 6988c291a83b05760b93263fc78e8feeca8ca4641b007c6978 | 2016-07-14 18:21:40 |
| 3 | 43210 | Fuller, T | tf@me.com | Level 2 | 6d07aa9758595e1dfe5dca93acc46dea01fef0856fe7dadf04 | 2016-07-14 18:21:40 |
| 4 | 62295 | Lewis, M | ml@me.com | Level 2 | f3d4154869ef03ff09ea778b5066bd909c3ce5baf894e0593b | 2016-07-14 18:21:40 |
+-----+-------+------------+-----------+---------+----------------------------------------------------+---------------------+
这篇关于使用Set使用哈希和日期加载数据本地文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!