SQLite3-如何从csv导入NULL值 [英] Sqlite3 - How to import NULL values from csv
问题描述
我已将mysql表转储为CSV。在此CSV文件中,NULL值写为 \N
现在,我想将此数据导入sqlite数据库,但是我无法告诉sqlite \N
为空值。它会将其视为字符串,并且该列值存储为 asN而不是NULL。
I have dumped a mysql table as CSV. In this CSV file, the NULL values are written as \N
Now I want to import this data into sqlite database, but I am unable to tell sqlite that \N
are null values. It is treating it as a string and that column value is stored as "\N" instead of NULL.
任何人都可以指导如何使用 .nullvalue
点命令。我无法将 \N
设置为空值。
Can anyone guide in how to use .nullvalue
dot command from sqlite. I am unable to set \N
as nullvalue.
sqlite> .show
nullvalue: ""
sqlite> .nullvalue \N
sqlite> .show
nullvalue: "N"
sqlite> .nullvalue '\N'
sqlite> .show
nullvalue: "\\N"
sqlite> .nullvalue "\N"
sqlite> .show
nullvalue: "N"
sqlite> .nullvalue \\N
sqlite> .show
nullvalue: "\\N"
sqlite> .nullvalue '\'N
Usage: .nullvalue STRING
sqlite> .nullvalue '\\'N
Usage: .nullvalue STRING
sqlite> .nullvalue \\N
sqlite> .show
nullvalue: "\\N"
sqlite>
这是每个 nullvalue
sqlite> .import /tmp/mysqlDump.csv employee
sqlite> select count(*) from employee where updatedon='\N';
94143
sqlite> select count(*) from employee where updatedon is null;
0
我如何告诉sqlite治疗 \N
作为NULL值?我不能使用空字符串作为NULL值,因为我的数据包含空字符串。
How can I tell sqlite to treat \N
as NULL value? I cannot use empty string as NULL value as my data contains empty strings.
推荐答案
CSV文件仅包含文本值。无法从CSV文件导入NULL值。
CSV files contain only text values. It is not possible to import NULL values from a CSV file.
要将 \N
值转换为NULL ,只需在事后使用UPDATE即可。
To convert the \N
values into NULLs, just use UPDATE afterwards:
UPDATE employee SET updatedon = NULL WHERE updatedon = '\N';
这篇关于SQLite3-如何从csv导入NULL值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!