SQLite导入ColDP的NameUsage.tsv列异常，求规范导入方案

问题背景 #

从ColDP归档文件导出NameUsage.tsv后，用以下命令导入SQLite的name_usage表：

.mode tabs
.import NameUsage.tsv name_usage

结果生成的表仅包含一个TEXT类型的超长列，所有带col:前缀的字段名被合并成了该列的名称，表内有约200万行数据。需要去除col:前缀，并为每个字段指定合适的数据类型，实现正确的表结构。

方案一：重新创建表并导入（推荐，适配大数据量） #

这种方法效率更高，适合处理百万级数据。

步骤1：生成正确的建表语句 #

根据NameUsage.tsv的表头，去除col:前缀后，为每个字段匹配合适的数据类型：

• ID类字段（如ID、parentID）：TEXT（字符串格式的唯一标识）
• 年份/序号字段（如publishedInYear、sequenceIndex）：INTEGER
• 数值字段（如branchLength）：REAL
• 文本/枚举类字段（如scientificName、status）：TEXT

最终建表语句示例：

CREATE TABLE IF NOT EXISTS name_usage (
    ID TEXT PRIMARY KEY,
    alternativeID TEXT,
    nameAlternativeID TEXT,
    sourceID TEXT,
    parentID TEXT,
    basionymID TEXT,
    status TEXT,
    scientificName TEXT,
    authorship TEXT,
    rank TEXT,
    notho TEXT,
    uninomial TEXT,
    genericName TEXT,
    infragenericEpithet TEXT,
    specificEpithet TEXT,
    infraspecificEpithet TEXT,
    cultivarEpithet TEXT,
    namePhrase TEXT,
    nameReferenceID TEXT,
    publishedInYear INTEGER,
    publishedInPage TEXT,
    publishedInPageLink TEXT,
    code TEXT,
    nameStatus TEXT,
    accordingToID TEXT,
    accordingToPage TEXT,
    accordingToPageLink TEXT,
    referenceID TEXT,
    scrutinizer TEXT,
    scrutinizerID TEXT,
    scrutinizerDate TEXT,
    extinct TEXT,
    temporalRangeStart TEXT,
    temporalRangeEnd TEXT,
    environment TEXT,
    species TEXT,
    section TEXT,
    subgenus TEXT,
    genus TEXT,
    subtribe TEXT,
    tribe TEXT,
    subfamily TEXT,
    family TEXT,
    superfamily TEXT,
    suborder TEXT,
    order TEXT,
    subclass TEXT,
    class TEXT,
    subphylum TEXT,
    phylum TEXT,
    kingdom TEXT,
    sequenceIndex INTEGER,
    branchLength REAL,
    link TEXT,
    nameRemarks TEXT,
    remarks TEXT
);

注：将ID设为主键，可大幅提升后续查询性能。

步骤2：导入数据 #

1. 先删除错误的旧表：

DROP TABLE IF EXISTS name_usage;

2. 执行上述建表语句，然后导入数据：
   • 若你的SQLite版本≥3.32.0，支持直接跳过表头：

     PRAGMA synchronous = OFF; -- 关闭同步加快导入
     PRAGMA journal_mode = MEMORY; -- 内存模式减少磁盘IO
     .mode tabs
     .import --skip 1 NameUsage.tsv name_usage
     PRAGMA synchronous = NORMAL; -- 恢复默认设置
     PRAGMA journal_mode = WAL;
     

   • 若版本较低，先处理TSV文件去掉表头再导入：

     tail -n +2 NameUsage.tsv > NameUsage_noheader.tsv
     

     再执行SQL：

     PRAGMA synchronous = OFF;
     PRAGMA journal_mode = MEMORY;
     .mode tabs
     .import NameUsage_noheader.tsv name_usage
     PRAGMA synchronous = NORMAL;
     PRAGMA journal_mode = WAL;
     

方案二：修复已有的单列表（适合不想重新导入的场景） #

如果已经导入了错误的单列表，可通过字符串拆分来修复，但速度较慢，不推荐百万级数据使用。

步骤1：创建正确结构的新表 #

同方案一的建表语句，创建name_usage_new表。

步骤2：拆分数据并插入新表 #

利用SQLite的JSON函数拆分制表符分隔的字符串：

INSERT INTO name_usage_new (
    ID, alternativeID, nameAlternativeID, sourceID, parentID, basionymID, status, scientificName,
    authorship, rank, notho, uninomial, genericName, infragenericEpithet, specificEpithet,
    infraspecificEpithet, cultivarEpithet, namePhrase, nameReferenceID, publishedInYear,
    publishedInPage, publishedInPageLink, code, nameStatus, accordingToID, accordingToPage,
    accordingToPageLink, referenceID, scrutinizer, scrutinizerID, scrutinizerDate, extinct,
    temporalRangeStart, temporalRangeEnd, environment, species, section, subgenus, genus,
    subtribe, tribe, subfamily, family, superfamily, suborder, order, subclass, class,
    subphylum, phylum, kingdom, sequenceIndex, branchLength, link, nameRemarks, remarks
)
SELECT
    json_extract(arr, '$[0]') AS ID,
    json_extract(arr, '$[1]') AS alternativeID,
    json_extract(arr, '$[2]') AS nameAlternativeID,
    json_extract(arr, '$[3]') AS sourceID,
    json_extract(arr, '$[4]') AS parentID,
    json_extract(arr, '$[5]') AS basionymID,
    json_extract(arr, '$[6]') AS status,
    json_extract(arr, '$[7]') AS scientificName,
    json_extract(arr, '$[8]') AS authorship,
    json_extract(arr, '$[9]') AS rank,
    json_extract(arr, '$[10]') AS notho,
    json_extract(arr, '$[11]') AS uninomial,
    json_extract(arr, '$[12]') AS genericName,
    json_extract(arr, '$[13]') AS infragenericEpithet,
    json_extract(arr, '$[14]') AS specificEpithet,
    json_extract(arr, '$[15]') AS infraspecificEpithet,
    json_extract(arr, '$[16]') AS cultivarEpithet,
    json_extract(arr, '$[17]') AS namePhrase,
    json_extract(arr, '$[18]') AS nameReferenceID,
    json_extract(arr, '$[19]') AS publishedInYear,
    json_extract(arr, '$[20]') AS publishedInPage,
    json_extract(arr, '$[21]') AS publishedInPageLink,
    json_extract(arr, '$[22]') AS code,
    json_extract(arr, '$[23]') AS nameStatus,
    json_extract(arr, '$[24]') AS accordingToID,
    json_extract(arr, '$[25]') AS accordingToPage,
    json_extract(arr, '$[26]') AS accordingToPageLink,
    json_extract(arr, '$[27]') AS referenceID,
    json_extract(arr, '$[28]') AS scrutinizer,
    json_extract(arr, '$[29]') AS scrutinizerID,
    json_extract(arr, '$[30]') AS scrutinizerDate,
    json_extract(arr, '$[31]') AS extinct,
    json_extract(arr, '$[32]') AS temporalRangeStart,
    json_extract(arr, '$[33]') AS temporalRangeEnd,
    json_extract(arr, '$[34]') AS environment,
    json_extract(arr, '$[35]') AS species,
    json_extract(arr, '$[36]') AS section,
    json_extract(arr, '$[37]') AS subgenus,
    json_extract(arr, '$[38]') AS genus,
    json_extract(arr, '$[39]') AS subtribe,
    json_extract(arr, '$[40]') AS tribe,
    json_extract(arr, '$[41]') AS subfamily,
    json_extract(arr, '$[42]') AS family,
    json_extract(arr, '$[43]') AS superfamily,
    json_extract(arr, '$[44]') AS suborder,
    json_extract(arr, '$[45]') AS order,
    json_extract(arr, '$[46]') AS subclass,
    json_extract(arr, '$[47]') AS class,
    json_extract(arr, '$[48]') AS subphylum,
    json_extract(arr, '$[49]') AS phylum,
    json_extract(arr, '$[50]') AS kingdom,
    json_extract(arr, '$[51]') AS sequenceIndex,
    json_extract(arr, '$[52]') AS branchLength,
    json_extract(arr, '$[53]') AS link,
    json_extract(arr, '$[54]') AS nameRemarks,
    json_extract(arr, '$[55]') AS remarks
FROM (
    SELECT '["' || replace([col:ID   col:alternativeID   ...   col:remarks], '\t', '","') || '"]' AS arr
    FROM name_usage
);

注：将SQL中[col:ID col:alternativeID ... col:remarks]替换为你现有单列表的实际列名。

后续优化 #

导入完成后，可给常用查询字段建立索引提升性能：

CREATE INDEX idx_scientific_name ON name_usage(scientificName);
CREATE INDEX idx_rank ON name_usage(rank);
CREATE INDEX idx_parent_id ON name_usage(parentID);

内容的提问来源于stack exchange，提问作者user3425506