如何通过SQL查询提取任意SQL语句中的表名？（已有Python SQL Parse方案）

由于SQL语法本身的复杂性，纯SQL提取表名的方案会因数据库类型而异，且对复杂SQL（如嵌套子查询、CTE、带特殊字符的表名）的支持有限。以下是主流数据库的实现方案：

MySQL #

方案1：正则表达式循环提取 #

适用于简单SELECT语句，匹配FROM/JOIN后的表名：

SET @sql_stmt = 'SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = ''active''';
SET @pos = 1;
SET @table_name = '';

CREATE TEMPORARY TABLE IF NOT EXISTS extracted_tables (table_name VARCHAR(255));

WHILE @pos > 0 DO
    -- 匹配FROM或JOIN后的表名，忽略大小写
    SET @table_name = REGEXP_SUBSTR(@sql_stmt, 'FROM\\s+([^\\s,]+)|JOIN\\s+([^\\s,]+)', @pos, 1, 'i', 1);
    IF @table_name IS NOT NULL THEN
        INSERT INTO extracted_tables VALUES (TRIM(@table_name));
        SET @pos = LOCATE(@table_name, @sql_stmt, @pos) + LENGTH(@table_name);
    ELSE
        SET @pos = 0;
    END IF;
END WHILE;

SELECT DISTINCT table_name FROM extracted_tables;
DROP TEMPORARY TABLE IF EXISTS extracted_tables;

方案2：利用执行计划解析（更可靠） #

通过EXPLAIN FORMAT=JSON生成执行计划，从JSON中提取表名：

SET @sql_stmt = 'SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = ''active''';
PREPARE stmt FROM @sql_stmt;

-- 提取所有表名
SELECT DISTINCT JSON_UNQUOTE(JSON_EXTRACT(plan, '$.query_block.table_accesses.table_name')) AS table_name
FROM (SELECT EXPLAIN FORMAT=JSON stmt AS plan) AS t;

DEALLOCATE PREPARE stmt;

PostgreSQL #

方案1：正则表达式匹配 #

使用regexp_matches提取所有匹配的表名：

WITH sql_input AS (
    SELECT 'SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = ''active''' AS sql_stmt
)
SELECT DISTINCT TRIM(UNNEST(regexp_matches(sql_stmt, 'FROM\s+([^\s,]+)|JOIN\s+([^\s,]+)', 'gi'))) AS table_name
FROM sql_input;

方案2：利用执行计划解析 #

通过pg_get_plan获取执行计划，解析文本提取表名：

WITH sql_input AS (
    SELECT 'SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = ''active''' AS sql_stmt
),
plan_data AS (
    SELECT pg_get_plan(pg_prepare('', sql_stmt, '{}')) AS plan
)
SELECT DISTINCT substring(plan FROM 'Table "(.*?)"') AS table_name
FROM plan_data;

SQL Server #

方案1：结合字符串函数与正则 #

适用于简单语句，匹配FROM/JOIN后的表名：

DECLARE @sql_stmt NVARCHAR(MAX) = N'SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = ''active''';

SELECT DISTINCT TRIM(SUBSTRING(@sql_stmt, pos + 5, CHARINDEX(' ', @sql_stmt, pos + 5) - pos - 5)) AS table_name
FROM (
    SELECT CHARINDEX('FROM ', @sql_stmt, n) AS pos
    FROM (SELECT TOP (LEN(@sql_stmt)) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns) AS nums
    WHERE CHARINDEX('FROM ', @sql_stmt, n) > 0
) AS from_pos
UNION
SELECT DISTINCT TRIM(SUBSTRING(@sql_stmt, pos + 5, CHARINDEX(' ', @sql_stmt, pos + 5) - pos - 5)) AS table_name
FROM (
    SELECT CHARINDEX('JOIN ', @sql_stmt, n) AS pos
    FROM (SELECT TOP (LEN(@sql_stmt)) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns) AS nums
    WHERE CHARINDEX('JOIN ', @sql_stmt, n) > 0
);

注意事项 #

• 所有正则方案无法完美处理带引号（如"users-table"）、Schema前缀（如public.users）、嵌套子查询、CTE、临时表等复杂场景。
• 优先使用数据库内置的执行计划解析方案，准确性更高。

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