polardbx-sql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PolarDB-X SQL（MySQL 兼容性聚焦）

PolarDB-X SQL (Focus on MySQL Compatibility)

目标：生成能在 PolarDB-X 2.0 分布式版（企业版）AUTO 模式数据库上正确运行的 SQL，避免"MySQL 上能跑但 PolarDB-X 上报错"的问题。

Objective: Generate SQL that can run correctly on PolarDB-X 2.0 Distributed Edition (Enterprise Edition) in AUTO mode, avoiding the issue of "runs on MySQL but errors on PolarDB-X".

适用范围

Scope of Application

本 skill 仅适用于以下版本和模式：

PolarDB-X 2.0 企业版（又称分布式版） + AUTO 模式数据库

不适用于：

PolarDB-X 1.0（DRDS 1.0）
PolarDB-X 2.0 标准版
PolarDB-X 2.0 企业版的 DRDS 模式数据库

AUTO 模式和 DRDS 模式的主要区别：AUTO 模式使用 MySQL 兼容的

PARTITION BY

语法定义分区，DRDS 模式使用旧版

dbpartition/tbpartition

语法。可通过以下方式确认数据库模式：

sql

SHOW CREATE DATABASE db_name;
-- 输出中 MODE = 'auto' 表示 AUTO 模式

This skill only applies to the following version and mode:

PolarDB-X 2.0 Enterprise Edition (also known as Distributed Edition) + AUTO mode database

Not applicable to:

PolarDB-X 1.0 (DRDS 1.0)
PolarDB-X 2.0 Standard Edition
PolarDB-X 2.0 Enterprise Edition in DRDS mode database

Main differences between AUTO mode and DRDS mode: AUTO mode uses MySQL-compatible

PARTITION BY

syntax to define partitions, while DRDS mode uses the legacy

dbpartition/tbpartition

syntax. You can confirm the database mode via the following method:

sql

SHOW CREATE DATABASE db_name;
-- MODE = 'auto' in the output indicates AUTO mode

Workflow（每次使用时遵循）

Workflow (Follow Each Time You Use)

确认目标引擎和版本：
- 执行
```
SELECT VERSION();
```
  判断实例类型：
  - 结果含
```
TDDL
```
    且版本号 > 5.4.12（如
```
5.7.25-TDDL-5.4.19-20251031
```
    ）-> 2.0 企业版（分布式版），本 skill 适用。从中解析企业版版本号（如 5.4.19）。
  - 结果含
```
TDDL
```
    且版本号 <= 5.4.12（如
```
5.6.29-TDDL-5.4.12-16327949
```
    ）-> DRDS 1.0，本 skill 不适用。
  - 结果含
```
X-Cluster
```
    （如
```
8.0.32-X-Cluster-8.4.20-20251017
```
    ）-> 2.0 标准版，本 skill 不适用，请使用
```
polardbx-standard
```
    skill。
- 确认是 2.0 企业版后，执行
```
SHOW CREATE DATABASE db_name;
```
  确认是 AUTO 模式（MODE = 'auto'）。
- 版本号影响特性可用性（如 NEW SEQUENCE 需要 5.4.14+，CCI 需要较新版本）。
确认表类型需求：
- 小表或字典表 -> 广播表
```
BROADCAST
```
  （全量复制到每个 DN）。
- 无需分布式的表 -> 单表
```
SINGLE
```
  （仅存储在一个 DN）。
- 其他情况 -> 分区表（默认），选择合适的分区键和分区策略。
生成 SQL 时使用 PolarDB-X 安全默认值：
- 避免不支持的 MySQL 特性（存储过程/触发器/EVENT/SPATIAL 等）。
- 使用
```
KEY
```
  或
```
HASH
```
  分区替代 MySQL 的 AUTO_INCREMENT 主键写热点。
- 需要非分区键查询时，考虑创建全局二级索引（GSI）。
- 需要全局唯一 ID 时，使用 Sequence 而非依赖 AUTO_INCREMENT 的连续性。
如果用户提供 MySQL SQL，进行兼容性检查：
- 替换不支持的特性，给出 PolarDB-X 替代方案。
- 明确标注行为差异和版本要求。
SQL 慢或报错时，使用 PolarDB-X 诊断工具：
- ```
EXPLAIN
```
  查看逻辑执行计划。
- ```
EXPLAIN EXECUTE
```
  查看下推到 DN 的物理执行计划。
- ```
EXPLAIN SHARDING
```
  查看分片扫描情况，判断是否存在全分片扫描。
- ```
EXPLAIN ANALYZE
```
  实际执行并收集运行统计。

Confirm target engine and version:
- Execute
```
SELECT VERSION();
```
  to determine the instance type:
  - Result contains
```
TDDL
```
    and version number > 5.4.12 (e.g.,
```
5.7.25-TDDL-5.4.19-20251031
```
    ) -> 2.0 Enterprise Edition (Distributed Edition), this skill is applicable. Parse the enterprise edition version number (e.g., 5.4.19) from it.
  - Result contains
```
TDDL
```
    and version number <= 5.4.12 (e.g.,
```
5.6.29-TDDL-5.4.12-16327949
```
    ) -> DRDS 1.0, this skill is not applicable.
  - Result contains
```
X-Cluster
```
    (e.g.,
```
8.0.32-X-Cluster-8.4.20-20251017
```
    ) -> 2.0 Standard Edition, this skill is not applicable, please use the
```
polardbx-standard
```
    skill.
- After confirming it's 2.0 Enterprise Edition, execute
```
SHOW CREATE DATABASE db_name;
```
  to confirm it's in AUTO mode (MODE = 'auto').
- Version number affects feature availability (e.g., NEW SEQUENCE requires 5.4.14+, CCI requires a newer version).
Confirm table type requirements:
- Small tables or dictionary tables -> Broadcast Table
```
BROADCAST
```
  (fully replicated to each DN).
- Tables that don't need distribution -> Single Table
```
SINGLE
```
  (stored only on one DN).
- Other cases -> Partitioned Table (default), select appropriate partition key and partition strategy.
Use PolarDB-X safe defaults when generating SQL:
- Avoid unsupported MySQL features (stored procedures/triggers/EVENT/SPATIAL, etc.).
- Use
```
KEY
```
  or
```
HASH
```
  partitioning instead of MySQL's AUTO_INCREMENT primary key write hotspots.
- Consider creating a Global Secondary Index (GSI) when querying with non-partition keys.
- Use Sequence instead of relying on AUTO_INCREMENT continuity when you need globally unique IDs.
If the user provides MySQL SQL, perform compatibility checks:
- Replace unsupported features and provide PolarDB-X alternatives.
- Clearly mark behavioral differences and version requirements.
When SQL is slow or errors occur, use PolarDB-X diagnostic tools:
- ```
EXPLAIN
```
  to view the logical execution plan.
- ```
EXPLAIN EXECUTE
```
  to view the physical execution plan pushed down to DN.
- ```
EXPLAIN SHARDING
```
  to view shard scanning status and determine if full shard scanning exists.
- ```
EXPLAIN ANALYZE
```
  to actually execute and collect runtime statistics.

核心差异速查

Quick Reference for Core Differences

三种表类型：单表(
```
SINGLE
```
)、广播表(
```
BROADCAST
```
)、分区表（默认）；根据数据量和访问模式选择。
分区表：支持 KEY/HASH/RANGE/LIST/RANGE COLUMNS/LIST COLUMNS/CO_HASH + 二级分区（49 种组合）。
全局二级索引 GSI：解决非分区键查询导致的全分片扫描问题，支持 GSI / UGSI / Clustered GSI 三种类型。
列存索引 CCI：行列混合存储，通过
```
CLUSTERED COLUMNAR INDEX
```
加速 OLAP 分析查询。
Sequence：全局唯一序列，默认类型为
```
NEW SEQUENCE
```
（5.4.14+），替代 AUTO_INCREMENT 的分布式方案。
分布式事务：基于 TSO 全局时钟 + MVCC + 2PC，默认强一致；单分片事务自动优化为本地事务。
表组：相同分区规则的表绑定在同一表组，确保 JOIN 计算下推，避免跨分片数据搬运。
TTL 表：基于时间列自动过期和清理冷数据，可配合 CCI 实现冷热数据分离。
不支持的 MySQL 特性：存储过程/触发器/EVENT/SPATIAL/GEOMETRY/LOAD XML/HANDLER 等。
不支持 STRAIGHT_JOIN / NATURAL JOIN：使用标准 JOIN 语法替代。
不支持 := 赋值运算符：将逻辑移到应用层。
HAVING/JOIN ON 子句中不支持子查询：将子查询改写为 JOIN 或 CTE。

Three table types: Single Table (
```
SINGLE
```
), Broadcast Table (
```
BROADCAST
```
), Partitioned Table (default); choose based on data volume and access pattern.
Partitioned tables: Support KEY/HASH/RANGE/LIST/RANGE COLUMNS/LIST COLUMNS/CO_HASH + sub-partitioning (49 combinations).
Global Secondary Index (GSI): Solves the full shard scanning problem caused by non-partition key queries, supports three types: GSI / UGSI / Clustered GSI.
Columnar Index (CCI): Hybrid row-column storage, accelerates OLAP analysis queries via
```
CLUSTERED COLUMNAR INDEX
```
.
Sequence: Globally unique sequence, default type is
```
NEW SEQUENCE
```
(5.4.14+), a distributed alternative to AUTO_INCREMENT.
Distributed transactions: Based on TSO global clock + MVCC + 2PC, strongly consistent by default; single-shard transactions are automatically optimized to local transactions.
Table groups: Tables with the same partitioning rules are bound to the same table group, ensuring JOIN computation is pushed down and avoiding cross-shard data movement.
TTL tables: Automatically expire and clean up cold data based on time columns, can cooperate with CCI to achieve hot-cold data separation.
Unsupported MySQL features: Stored procedures/triggers/EVENT/SPATIAL/GEOMETRY/LOAD XML/HANDLER, etc.
Does not support STRAIGHT_JOIN / NATURAL JOIN: Use standard JOIN syntax instead.
Does not support := assignment operator: Move the logic to the application layer.
Subqueries are not supported in HAVING/JOIN ON clauses: Rewrite subqueries as JOIN or CTE.

参考文件（本 skill 内）

Reference Files (Within This Skill)

```
skills/polardbx-sql/references/create-table.md
```
- 建表语法、表类型（单表/广播表/分区表）、分区策略、二级分区、分区管理操作。
```
skills/polardbx-sql/references/gsi.md
```
- 全局二级索引 GSI/UGSI/Clustered GSI 的创建、查询和限制。
```
skills/polardbx-sql/references/cci.md
```
- 列存索引 CCI 的创建、使用和适用场景。
```
skills/polardbx-sql/references/sequence.md
```
- Sequence 类型（NEW/GROUP/SIMPLE/TIME）、创建和使用。
```
skills/polardbx-sql/references/transactions.md
```
- 分布式事务模型、隔离级别和注意事项。

skills/polardbx-sql/references/mysql-compatibility-notes.md

- MySQL 与 PolarDB-X 的兼容性差异和开发限制。

```
skills/polardbx-sql/references/explain.md
```
- EXPLAIN 系列命令的用法和执行计划诊断。
```
skills/polardbx-sql/references/ttl-table.md
```
- TTL 表定义、冷数据归档和清理调度。
```
skills/polardbx-sql/references/online-ddl.md
```
- Online DDL 判断、无锁执行策略、长事务检查、DMS 无锁变更等。

```
skills/polardbx-sql/references/create-table.md
```
- Table creation syntax, table types (single/broadcast/partitioned tables), partitioning strategies, sub-partitioning, partition management operations.
```
skills/polardbx-sql/references/gsi.md
```
- Creation, querying, and restrictions of Global Secondary Index (GSI/UGSI/Clustered GSI).
```
skills/polardbx-sql/references/cci.md
```
- Creation, usage, and applicable scenarios of Columnar Index (CCI).
```
skills/polardbx-sql/references/sequence.md
```
- Sequence types (NEW/GROUP/SIMPLE/TIME), creation, and usage.
```
skills/polardbx-sql/references/transactions.md
```
- Distributed transaction model, isolation levels, and notes.
```
skills/polardbx-sql/references/mysql-compatibility-notes.md
```
- Compatibility differences and development restrictions between MySQL and PolarDB-X.
```
skills/polardbx-sql/references/explain.md
```
- Usage of EXPLAIN series commands and execution plan diagnosis.
```
skills/polardbx-sql/references/ttl-table.md
```
- TTL table definition, cold data archiving, and cleanup scheduling.
```
skills/polardbx-sql/references/online-ddl.md
```
- Online DDL judgment, lock-free execution strategy, long transaction check, DMS lock-free changes, etc.