简单的SQL Lite表/导入问题 [英] Simple SQL Lite table/import question

问题描述

我有一个简单的SQL问题.我想建立一个3列的数据库，并且我有以下代码:

I have a simple SQL question. I want to make a 3 column database and I have the following code:

sqlite3 meshdb.db "create table t1 (t1key INTEGER PRIMARY KEY, prideID, pubmedID);"

当我尝试导入具有两列(prideID和pubmedID)的简单csv文件时，出现预期3列数据，但发现2"错误.我希望t1key是一个整数，并在添加新字段时自动计数.我必须在主键前放置NOT NULL才能使它起作用吗?

When I try to import a simple csv file with two columns (prideID and pubmedID), I get a "expected 3 columns of data but found 2" error. I want the t1key to be an integer, and automatically count up as new fields are added. Do I have to put NOT NULL in front of PRIMARY KEY to for this to work?

推荐答案

.import不支持更改输入的形状(设置分隔符除外).您需要将CSV文件导入到临时表中，然后将其插入到真实表中.这是一个示例会话:

.import does not support reshaping the input (except from setting the separator). You need to import the CSV file into a temporary table and the insert that into the real table. Here is a example session:

$ cat a.csv 
1,2
3,4
5,6
$ sqlite3 a.db
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo(id integer primary key,x,y);
sqlite> create temp table footmp(x,y);
sqlite> .separator ,
sqlite> .import a.csv footmp
sqlite> select * from footmp;
1,2
3,4
5,6
sqlite> insert into foo(x,y) select * from footmp; 
sqlite> select * from foo; 
1,1,2
2,3,4
3,5,6
sqlite> drop table footmp; 

您看到ID已递增.这是因为类型为INTEGER PRIMARY KEY的列被视为内部ROWID的别名-内部ROWID始终是唯一的，升序的数字.

You see that ID is counted up. This is because a column with type INTEGER PRIMARY KEY is treated as an alias for the internal ROWID - which always is a unique, ascending number.

这篇关于简单的SQL Lite表/导入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！