SQLite中如何高效插入与查询固定行列矩阵表数据

别手写逐列枚举的SQL，你的三个矩阵行列都是固定常量，不管是建表、插入还是查询，都可以靠固定配置动态生成语句，或者用通用表结构彻底规避列枚举问题，两种成熟方案选就行：

方案1：动态生成宽表SQL（性能最优，兼容原有写法习惯） #

本质和你之前了解的逐列写法执行逻辑完全一致，只是把硬编码列名的部分改成自动生成，不用手写几十列的枚举：

• 第一步先把三个表的固定元数据写成静态配置，一次写完永久用：
  • 30行×18列表：配置表名、行标识列表（Value1_{Value30）、列标识列表（Column1}Column18）
  • 18行×16列表：配置表名、行标识列表（Value1_{Value18）、列标识列表（Column1}Column16）
  • 12行×17列表：配置表名、行标识列表（Value1_{Value12）、列标识列表（Column1}Column17）
• 建表时自动拼接语句，示例逻辑（所有编程语言逻辑通用）：

# 以30×18的表为例，Python写法，其他语言换对应字符串拼接逻辑即可
table_name = "matrix_30_18"
col_defs = ["row_id TEXT PRIMARY KEY"] # 行标识作为主键，唯一标记每一行
col_defs += [f"{col} REAL" for col in col_headers] # 数值类型按需调整，整数就用INTEGER
create_sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({', '.join(col_defs)});"

• 插入数据时自动生成列名和参数占位符，不用逐列写：

insert_cols = ', '.join(col_headers)
placeholders = ', '.join([f"@{col}" for col in col_headers])
insert_sql = f"INSERT INTO {table_name} (row_id, {insert_cols}) VALUES (@row_id, {placeholders});"
# 录入时循环遍历每一个行标识，逐行把单元格值传入对应参数即可，全程不用手写列名

• 按SheetMetarial查询时同样自动拼接查询列：

query_cols = ', '.join(col_headers)
query_sql = f"SELECT {query_cols} FROM {table_name} WHERE row_id = @SheetMetarial;"

这个方案和你原生逐列写法的性能完全一样，没有额外开销，只是把重复的列名枚举工作交给代码自动完成，因为你的行列都是固定常量，配置不会变，不会出现SQL拼接错误的问题。

方案2：通用窄表存储（零表结构维护，灵活性最高） #

如果不想给每个矩阵单独建表，直接用一张统一的三元组表存所有矩阵的单元格数据，建一次表就够，完全不用关心每个矩阵的行列数：

CREATE TABLE matrix_data (
    sheet_tag TEXT NOT NULL, -- 标记属于哪个矩阵表
    row_id TEXT NOT NULL, -- 存储行标识，比如Value1
    col_id TEXT NOT NULL, -- 存储列标识，比如Column1
    cell_value REAL, -- 存储单元格数值
    PRIMARY KEY (sheet_tag, row_id, col_id) -- 联合主键保证单元格唯一
);

插入时不需要枚举任何列，直接逐单元格写入即可：

INSERT INTO matrix_data (sheet_tag, row_id, col_id, cell_value)
VALUES (@SheetMetarial, @row_id, @col_id, @cell_value);

查询逻辑也非常简单，不需要拼接动态列：

-- 查询指定SheetMetarial下的某一整行数据
SELECT col_id, cell_value FROM matrix_data
WHERE sheet_tag = @SheetMetarial AND row_id = @target_row;

-- 查询指定SheetMetarial下的全量矩阵数据
SELECT row_id, col_id, cell_value FROM matrix_data
WHERE sheet_tag = @SheetMetarial;

查询拿到结果后，在内存里按行/列做个简单的分组就能还原成矩阵结构，你这三个表最大也就540个单元格，内存转换的开销可以忽略不计。

这个方案的优势是后续新增任意维度的矩阵表都不需要改表结构、改SQL，直接往同一张表写数据就行，维护成本极低。

实操注意事项 #

• 批量录入数据时一定要开SQLite事务，不要每插一条就提交一次，三个表总数据量不到1000条，开事务后录入速度会快几个数量级。
• 如果怕列名和SQLite关键字冲突，可以给所有自动生成的列名加统一前缀，比如mat_col1，避免语法错误。
• 不要为这三个矩阵写单独的ORM实体类，用字典类型传参配合动态SQL，代码量最少。

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