创建要与一起使用的示例数据库和表。
create database `test`;
use `test`;
create table `test` (`value` float(10,2) null);
insert into test (value) values (null);
select value from test;
+-------+
| value |
+-------+
| NULL |
+-------+
1 row in set (0.00 sec)
现在我想创建一个txt文件,当您加载它时,可以将空数据加载到测试表中。
delete from test;
这是我的数据文件名为 test.txt
在vim中显示(:set list)。
将文件加载到表中。
LOAD DATA LOCAL INFILE "test.txt"
INTO TABLE test
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n' \w;
select value from test;
+-------+
| value |
+-------+
| 0.00 |
| 0.00 |
| 0.00 |
| 0.00 |
| 0.00 |
+-------+
如何加载 null
的数据 value
我的案子里有文件吗?
如何创建这种数据文件,值字段是 null
你装完之后?
运行时environment:win10+mysql-5.7.
select version();
+-----------+
| version() |
+-----------+
| 5.7.22 |
+-----------+
1 row in set (0.03 sec)
在vim中显示test.txt文件 set list
.
\N$
\N^I$
case:without set
加载它而不加载 (value) SET value = (CASE WHEN @value IS NULL THEN NULL ELSE @value END)
.
LOAD DATA LOCAL INFILE "f:/test.txt"
INTO TABLE test
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n' \W;
输出:
Warning (Code 1265): Data truncated for column 'value' at row 1
Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
select * from test;
+-------+
| value |
+-------+
| 0.00 |
| NULL |
+-------+
case:with set
把它装上 (value) SET value = (CASE WHEN @value IS NULL THEN NULL ELSE @value END)
.
delete from test;
LOAD DATA LOCAL INFILE "f:/test.txt"
INTO TABLE test
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(value)SET value = (CASE WHEN @value IS NULL THEN NULL ELSE @value END) \W;
输出:
Warning (Code 1265): Data truncated for column 'value' at row 1
Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
select * from test;
+-------+
| value |
+-------+
| NULL |
| NULL |
+-------+
谢谢@schwern。
LOAD DATA LOCAL INFILE "f:/test.txt"
INTO TABLE test
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n' \W;
两者都可以正常工作,没有任何警告。
1条答案
按热度按时间agxfikkp1#
你可以用
\N
.问题是所有的输入行都有一个额外的空字段。tab终止字段,就像逗号一样。你把它当作引号一样用。例如,你的第一行
^Inull^I
是三个领域。第一个是空的。第二个包含null
. 第三个是空的。mysql应该说
你可以通过阅读这些警告
show warnings
.您所需要的就是:
请注意
lines terminated by
对unix和windows新行比较敏感。从文件里。。。如果已在windows系统上生成文本文件,则可能必须使用
LINES TERMINATED BY '\r\n'
正确读取文件,因为windows程序通常使用两个字符作为行终止符。这就解释了你得到的警告。