使用sqlglot读取含PARTITION的MySQL文件解析报错，是否不支持该语法？

架构老林

2026-6-12

问题：sqlglot解析含PARTITION的MySQL SQL文件报错

使用sqlglot 27.8.0版本读取包含PARTITION关键字的MySQL SQL文件时，出现解析错误：

An error occurred during parsing: Expecting ). Line 19, Col: 26.
  created_at`) USING BTREE
    ) PARTITION BY RANGE ( UNIX_TIMESTAMP(audit_ts)) (
    PARTITION p2401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
    PARTITION p2402 VALUES LESS THAN (UNIX_TIMES

用于解析的Python代码：

import logging
import sqlglot
from sqlglot import exp

# Configure logger
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s"
)
logger = logging.getLogger(__name__)


def extract_table_names(sql_file_path, dialect="mysql"):
    """
    Parse the SQL file and return a set of unique table names found.
    Logs errors if file not found or parsing fails.
    """
    try:
        with open(sql_file_path, "r") as f:
            sql_script = f.read()

        expression_trees = sqlglot.parse(sql_script, dialect=dialect)
        table_names = set()
        for tree in expression_trees:
            table_names.update([table.name for table in tree.find_all(exp.Table)])
        return table_names
    except FileNotFoundError:
        logger.error(f"File not found: {sql_file_path}")
        return set()
    except Exception as e:
        logger.error(f"Error parsing `{sql_file_path}`: {e}")
        return set()

if __name__ == "__main__":
    sql_file = "changeLogs/health-service/create_db.sql"
    tables = extract_table_names(sql_file)

    logger.info(f"Total unique tables found: {len(tables)}")
    logger.info(f"Table names: {sorted(list(tables))}")

对应的SQL文件示例：

-- liquibase formatted sql

-- changeset debraj.manna@nexla.com:NEX-18235
CREATE TABLE IF NOT EXISTS `audit_control`
(
   `id`            BIGINT auto_increment NOT NULL,
   `message_id`    VARCHAR(100) DEFAULT NULL,
    `resource_type` VARCHAR(30) NOT NULL,
    `event_type`    VARCHAR(30) NOT NULL,
    `resource_id`   INT NOT NULL,
    `origin`        VARCHAR(100) NOT NULL,
    `created_at`    TIMESTAMP NOT NULL,
    `body`          mediumtext NOT NULL,
    `audit_ts`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, audit_ts),
    KEY `audit_control_resource_type_resource_id_IDX` (`resource_type`,`resource_id`) USING BTREE,
    KEY `audit_control_created_at_IDX` (`created_at`) USING BTREE
    ) PARTITION BY RANGE ( UNIX_TIMESTAMP(audit_ts)) (
    PARTITION p2401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
    PARTITION p2402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01 00:00:00')),
    PARTITION p2403 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01 00:00:00')),
    PARTITION p2404 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-01 00:00:00')),
    PARTITION p2405 VALUES LESS THAN (UNIX_TIMESTAMP('2024-06-01 00:00:00')),
    PARTITION p2406 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01 00:00:00')),
    PARTITION p2407 VALUES LESS THAN (UNIX_TIMESTAMP('2024-08-01 00:00:00')),
    PARTITION p2408 VALUES LESS THAN (UNIX_TIMESTAMP('2024-09-01 00:00:00')),
    PARTITION p2409 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01 00:00:00')),
    PARTITION p2410 VALUES LESS THAN (UNIX_TIMESTAMP('2024-11-01 00:00:00')),
    PARTITION p2411 VALUES LESS THAN (UNIX_TIMESTAMP('2024-12-01 00:00:00')),
    PARTITION p2412 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01 00:00:00')),
    PARTITION pN VALUES LESS THAN MAXVALUE
);

CREATE TABLE IF NOT EXISTS `audit_coordination`
(
    `id`         BIGINT auto_increment NOT NULL,
    `message_id` VARCHAR(100) DEFAULT NULL,
    `event_type` VARCHAR(30) NOT NULL,
    `created_at` TIMESTAMP NOT NULL,
    `body`       TEXT NOT NULL,
    `audit_ts`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, audit_ts)
    ) PARTITION BY RANGE ( UNIX_TIMESTAMP(audit_ts)) (
    PARTITION p2401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
    PARTITION p2402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01 00:00:00')),
    PARTITION p2403 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01 00:00:00')),
    PARTITION p2404 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-01 00:00:00')),
    PARTITION p2405 VALUES LESS THAN (UNIX_TIMESTAMP('2024-06-01 00:00:00')),
    PARTITION p2406 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01 00:00:00')),
    PARTITION p2407 VALUES LESS THAN (UNIX_TIMESTAMP('2024-08-01 00:00:00')),
    PARTITION p2408 VALUES LESS THAN (UNIX_TIMESTAMP('2024-09-01 00:00:00')),
    PARTITION p2409 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01 00:00:00')),
    PARTITION p2410 VALUES LESS THAN (UNIX_TIMESTAMP('2024-11-01 00:00:00')),
    PARTITION p2411 VALUES LESS THAN (UNIX_TIMESTAMP('2024-12-01 00:00:00')),
    PARTITION p2412 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01 00:00:00')),
    PARTITION pN VALUES LESS THAN MAXVALUE
);

疑问：这是预期情况吗？sqlglot是否不支持PARTITION语法？使用环境为Python 3.9.6。

解答

这不是预期情况，sqlglot 27.8.0版本对MySQL的PARTITION语法支持不完善，属于版本局限性问题。后续版本（如>=30.x）已经针对MySQL分区语法做了支持优化，升级后可以正常解析这类语句。

如果暂时无法升级sqlglot，针对你提取表名的需求，可以通过预处理SQL文件，移除PARTITION相关内容来绕过解析错误：

修改你的Python代码，在读取SQL脚本后添加正则替换步骤：

import re  # 需要导入re模块

def extract_table_names(sql_file_path, dialect="mysql"):
    """
    Parse the SQL file and return a set of unique table names found.
    Logs errors if file not found or parsing fails.
    """
    try:
        with open(sql_file_path, "r") as f:
            sql_script = f.read()
        
        # 预处理：移除PARTITION相关内容，不影响表名提取
        sql_script = re.sub(r'\s*\) PARTITION BY.*?;', ');', sql_script, flags=re.DOTALL)

        expression_trees = sqlglot.parse(sql_script, dialect=dialect)
        table_names = set()
        for tree in expression_trees:
            table_names.update([table.name for table in tree.find_all(exp.Table)])
        return table_names
    except FileNotFoundError:
        logger.error(f"File not found: {sql_file_path}")
        return set()
    except Exception as e:
        logger.error(f"Error parsing `{sql_file_path}`: {e}")
        return set()

该正则会将) PARTITION BY ... ;的内容替换为);，保留CREATE TABLE语句的核心结构，既能让sqlglot正常解析，又不影响表名的提取。

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