SQLite/SpatiaLite使用CREATE TABLE..AS时数据类型异常及主键问题

一、保留原表数据类型的解决方法 #

SQLite的CREATE TABLE .. AS SELECT会根据查询返回值的实际类型自动推断新表字段类型，而非继承原表的声明类型，这是导致类型转换的核心原因。要严格保留原表类型，需拆分操作：

1. 手动创建匹配原表结构的新表

   • 执行PRAGMA table_info(原表名);获取原表的字段名、类型、约束等详细信息
   • 编写完整的CREATE TABLE语句，明确指定每个字段的类型，包括SpatiaLite空间字段的专属约束（如GEOMETRY类型、SRID、维度）。示例：

     CREATE TABLE 新表名 (
         id INTEGER PRIMARY KEY,
         username VARCHAR(100),
         record_date DATE,
         location GEOMETRY(POINT, 4326)
     );
     -- 注册空间字段到SpatiaLite元数据
     SELECT AddGeometryColumn('新表名', 'location', 4326, 'POINT', 'XY');
     

   • 新增的计算字段直接在CREATE TABLE中指定数据类型

2. 导入数据至新表
   使用INSERT INTO语句将原表数据（含计算结果）插入新表：

   INSERT INTO 新表名 (id, username, record_date, location, 计算字段)
   SELECT id, username, record_date, location, field1 * field2 AS 计算字段
   FROM 原表名;
   

二、CREATE TABLE .. AS SELECT 能否设置主键？ #

无法直接设置。CREATE TABLE .. AS SELECT仅能基于查询结果生成基础表结构，不支持在创建阶段指定主键、外键等约束。需通过以下两种方式处理：

1. 先手动创建带主键的表，再执行INSERT INTO .. SELECT（与保留类型的流程一致）
2. 先通过CREATE TABLE .. AS SELECT创建表，再用ALTER TABLE添加主键（需确保目标字段无NULL值和重复值）：

   CREATE TABLE 临时表 AS SELECT * FROM 原表名;
   ALTER TABLE 临时表 ADD PRIMARY KEY (id);
   

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