为什么SQLite允许为不存在的字段创建索引？如何检测这类无效索引？

你提供的复现脚本可以在SQLite中正常执行属于预期行为，以下是对应问题的明确解答：

1. 为什么SQLite支持该操作？ #

这是SQLite对表达式索引的原生支持设计导致的。当你在CREATE INDEX语句中指定的字段名无法匹配到对应表的实际字段时，SQLite不会直接抛出错误，而是会将无法识别的标识符解析为表达式：你示例中双引号包裹的PortalUsersID因为找不到对应列，会被SQLite当成字符串常量处理，属于合法的表达式，因此索引可以正常创建。

2. 是bug还是特殊设计？ #

这属于SQLite的有意设计，并非bug。SQLite为了支持灵活的表达式索引（比如基于函数计算结果、常量、多字段组合运算的索引），在DDL阶段不会强制要求索引项必须是表的物理字段，仅会校验语法合法性。

注：该设计和大多数关系型数据库（比如MySQL、PostgreSQL）不同，其他数据库会在建索引阶段直接校验字段存在性并报错。

3. 这类索引是否属于无效索引？ #

要看创建意图：

• 如果你确实需要基于常量值创建特殊的表达式索引，那它是有效的，可以满足特定的查询优化需求
• 如果你是拼写错误、误写了不存在的字段名，那这就是无效索引：它的第二个索引项是固定字符串PortalUsersID，完全无法匹配你针对UsersID等实际字段的查询，还会额外占用存储空间、拖慢表的写入性能。

4. 如何检测同类无效索引？ #

可以通过SQLite内置的pragma_index_xinfo函数查询索引的元信息，和对应表的实际字段做比对，快速筛选出存在问题的索引，参考检测脚本如下：

WITH all_user_indexes AS (
    -- 取出所有用户自定义的索引
    SELECT name AS index_name, tbl_name AS table_name
    FROM sqlite_master
    WHERE type = 'index' AND name NOT LIKE 'sqlite_%'
),
table_all_columns AS (
    -- 取出所有表的实际字段列表
    SELECT m.name AS table_name, p.name AS column_name
    FROM sqlite_master m
    JOIN pragma_table_info(m.name) p ON m.name = p.name
    WHERE m.type = 'table'
)
SELECT 
    a.index_name,
    a.table_name,
    '索引包含不存在的字段，建议检查拼写' AS risk_warning
FROM all_user_indexes a
WHERE EXISTS (
    -- 检查索引的每个键是否都对应表的实际字段
    SELECT 1
    FROM pragma_index_xinfo(a.index_name) xi
    LEFT JOIN table_all_columns c 
        ON c.table_name = a.table_name AND c.column_name = xi.name
    -- xi.cid = -1 说明是表达式/常量，不是实际表字段
    WHERE xi.cid = -1 AND xi.name IS NOT NULL
)

额外优化建议 #

SQLite 3.37.0及以上版本支持严格DDL模式，执行以下语句开启后，建表、建索引阶段会强制校验字段存在性，直接抛出错误避免误写：

PRAGMA strict_ddl = ON;

你提供的复现脚本执行结果如下：

内容的提问来源于stack exchange，提问作者Wim ten Brink