如何使用SQL高效查找字符串的索引或插入位置？

我有一张存储名称列表的表，排序后的数据如下（实际数据有数千行）：

排序后，我希望找到给定名称对应的索引（#列）。比如：

这种情况用简单的WHERE name = $query就能实现，但我还需要在查询字符串不在结果集中时也能返回正确的插入位置：

换句话说，我需要实现类似Python中bisect.bisect_left的功能：找到字符串对应的索引，不存在则返回它应该插入的位置。请问如何用SQL高效实现这个功能？

通用SQL方案（兼容多数数据库） #

通过优先匹配存在的记录，不存在则统计小于查询字符串的记录数量，直接得到插入位置：

SELECT COALESCE(
    -- 存在匹配记录时返回对应索引
    (SELECT "#" FROM your_table WHERE name = '${query}'),
    -- 不存在时，统计所有小于查询值的记录数即为插入位置
    (SELECT COUNT(*) FROM your_table WHERE name < '${query}')
) AS position;

性能优化版（依赖索引） #

如果name列有B-Tree排序索引，可以用EXISTS先判断是否存在，避免重复查询：

SELECT 
    CASE 
        WHEN EXISTS(SELECT 1 FROM your_table WHERE name = '${query}')
        THEN (SELECT "#" FROM your_table WHERE name = '${query}')
        ELSE (SELECT COUNT(*) FROM your_table WHERE name < '${query}')
    END AS position;

PostgreSQL 专属实现 #

可以用范围查询结合UNION ALL，或者利用数组转换实现：

-- 方法1：高效范围查询
SELECT COUNT(*) AS position
FROM your_table
WHERE name < '${query}'
UNION ALL
SELECT "#" AS position
FROM your_table
WHERE name = '${query}'
LIMIT 1;

-- 方法2：数组匹配（适合小数据集）
SELECT COALESCE(
    array_position(ARRAY(SELECT name FROM your_table ORDER BY name), '${query}') - 1,
    (SELECT COUNT(*) FROM your_table WHERE name < '${query}')
) AS position;

MySQL 专属实现 #

用IFNULL简化逻辑，配合索引保证效率：

SELECT IFNULL(
    (SELECT "#" FROM your_table WHERE name = '${query}'),
    (SELECT COUNT(*) FROM your_table WHERE name < '${query}')
) AS position;

关键性能提示 #

• 必须在name列创建B-Tree索引，这样范围查询name < '${query}'会走索引扫描，数千行数据的查询耗时可以忽略不计。
• 避免全表扫描类的实现（比如先导出所有数据再计算位置），会浪费数据库资源。

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