SQL 语句:
LOAD DATA LOCAL INFILE 'data_info.txt' INTO TABLE t1
IGNORE 1 LINES
(@date,@time,@name,@weight_lb,@state)
SET dt = CONCAT(@date,' ',@time),
first_name = SUBSTRING_INDEX(@name,' ',1),
last_name = SUBSTRING_INDEX(@name,' ',-1),
weight_kg = @weight_lb * .454,
st_abbrev = (SELECT abbrev FROM states WHERE name = @state);
data_info.txt:
Date time Name Weight State
2006-09-01 12:00:00 Bill Wills 200 Nevada
2006-09-02 09:00:00 Jeff Deft 150 Oklahoma
2006-09-04 03:00:00 Bob Hobbs 225 Utah
2006-09-07 08:00:00 Hank Banks 175 Texas
states 表：
mysql> select * from states;
+--------+----------+
| abbrev | name |
+--------+----------+
| NV | Nevada |
| OK | Oklahoma |
| UT | Utah |
| TX | Texas |
+--------+----------+
t1表中最终数据：
mysql> select * from t1;
+---------------------+-----------+------------+-----------+-----------+
| dt | last_name | first_name | weight_kg | st_abbrev |
+---------------------+-----------+------------+-----------+-----------+
| 2006-09-01 12:00:00 | Wills | Bill | 90.8 | NULL |
| 2006-09-02 09:00:00 | Deft | Jeff | 68.1 | NULL |
| 2006-09-04 03:00:00 | Hobbs | Bob | 102.15 | NULL |
| 2006-09-07 08:00:00 | Banks | Hank | 79.45 | NULL |
+---------------------+-----------+------------+-----------+-----------+
为什么最后这一列会是空？求大神指点
