tidb-sql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Chinese

Goal: generate SQL that runs correctly on TiDB by default, and avoid "works on MySQL but breaks on TiDB" constructs.

目标：生成默认可在TiDB上正确运行的SQL，避免出现「在MySQL上可行但在TiDB上失效」的语法结构。

Identify the target engine and version:
- Run
```
SELECT VERSION();
```
- If the result contains
```
TiDB
```
  , treat it as TiDB and parse the version (needed for feature gates like Vector / Foreign Key).
- If connecting to TiDB Cloud, ensure the client enables SSL with certificate + identity verification (see
```
skills/tidb-sql/references/tidb-cloud-ssl.md
```
  ).
Ask 2 quick capability questions if the request depends on them:
- "Do you have TiFlash?" (needed for vector indexes)
- "Is this TiDB Cloud Starter/Essential in a supported region for Full-Text Search?" (availability is limited)
Generate SQL using TiDB-safe defaults:
- Avoid unsupported MySQL features (procedures/triggers/events/UDF/GEOMETRY/SPATIAL, etc.)
- Treat views as read-only
- Treat primary key changes as migration/rebuild work
If the user provides MySQL SQL, do a compatibility pass:
- Replace unsupported features with TiDB alternatives
- Call out behavior differences and version prerequisites explicitly
If SQL is slow or fails unexpectedly, use TiDB-native diagnostics:
- Use
```
EXPLAIN FORMAT = "tidb_json"
```
  for structured plans and operator trees.
- Use
```
EXPLAIN ANALYZE
```
  to compare
```
estRows
```
  vs
```
actRows
```
  (it executes the query).
- If the plan looks wrong, consider
```
ANALYZE TABLE ...
```
  to refresh statistics.

识别目标引擎及版本：
- 执行
```
SELECT VERSION();
```
- 如果结果包含
```
TiDB
```
  ，则按TiDB处理并解析版本（部分特性如Vector/外键需依赖版本）。
- 若连接TiDB Cloud，确保客户端通过证书+身份验证启用SSL（详见
```
skills/tidb-sql/references/tidb-cloud-ssl.md
```
  ）。
若请求依赖以下能力，先快速询问两个问题：
- "您是否使用TiFlash？"（向量索引需要依赖它）
- "您使用的TiDB Cloud Starter/Essential是否在支持全文搜索的区域？"（该特性的可用范围有限）
使用TiDB安全默认值生成SQL：
- 避免使用TiDB不支持的MySQL特性（存储过程/触发器/事件/UDF/GEOMETRY/SPATIAL等）
- 将视图视为只读
- 主键变更需按迁移/重建流程处理
如果用户提供MySQL SQL，执行兼容性检查：
- 用TiDB替代方案替换不支持的特性
- 明确指出行为差异和版本前提条件
若SQL运行缓慢或意外失败，使用TiDB原生诊断工具：
- 使用
```
EXPLAIN FORMAT = "tidb_json"
```
  获取结构化执行计划和算子树。
- 使用
```
EXPLAIN ANALYZE
```
  对比
```
estRows
```
  与
```
actRows
```
  （该命令会实际执行查询）。
- 如果执行计划不符合预期，考虑执行
```
ANALYZE TABLE ...
```
  刷新统计信息。

Vector: TiDB supports
```
VECTOR
```
/
```
VECTOR(D)
```
types and vector functions/indexes; MySQL does not.
No GEOMETRY/SPATIAL: avoid
```
GEOMETRY
```
, spatial functions, and
```
SPATIAL
```
indexes.
No procedures / functions / triggers / events: move logic to the application layer or an external scheduler.
Full-text search (TiDB feature): use TiDB full-text search SQL when available; don't assume MySQL
```
FULLTEXT
```
works everywhere.
Views are read-only: no
```
UPDATE/INSERT/DELETE
```
against views.
Foreign keys: supported in TiDB v6.6.0+; otherwise, don't rely on FK enforcement.
Primary key changes are restricted: assume "create new table + backfill + swap" for PK changes.
AUTO_RANDOM: prefer
```
AUTO_RANDOM
```
over
```
AUTO_INCREMENT
```
for write-hotspot avoidance when appropriate.
Transactions: TiDB supports pessimistic and optimistic modes; handle optimistic
```
COMMIT
```
failures in application logic.

```
skills/tidb-sql/references/vector.md
```
- VECTOR types, functions, vector index DDL, and query patterns.
```
skills/tidb-sql/references/full-text-search.md
```
- Full-text search SQL patterns and availability gotchas.

skills/tidb-sql/references/auto-random.md

AUTO_RANDOM

rules, DDL patterns, and restrictions.

```
skills/tidb-sql/references/transactions.md
```
- pessimistic vs optimistic mode and session/global knobs.
```
skills/tidb-sql/references/mysql-compatibility-notes.md
```
- other "MySQL vs TiDB" differences that commonly break SQL.
```
skills/tidb-sql/references/explain.md
```
- EXPLAIN / EXPLAIN ANALYZE usage, tidb_json and dot formats.
```
skills/tidb-sql/references/flashback.md
```
- FLASHBACK TABLE/DATABASE and FLASHBACK CLUSTER recovery playbooks.
```
skills/tidb-sql/references/tidb-cloud-ssl.md
```
- TiDB Cloud SSL verification requirements and client flags.

skills/tidb-sql/references/auto-random.md

AUTO_RANDOM

规则、DDL模式及限制。

```
skills/tidb-sql/references/transactions.md
```
- 悲观与乐观模式及会话/全局参数。
```
skills/tidb-sql/references/mysql-compatibility-notes.md
```
- 其他常见的「MySQL vs TiDB」差异点，这些差异常导致SQL执行失败。
```
skills/tidb-sql/references/explain.md
```
- EXPLAIN/EXPLAIN ANALYZE的用法、tidb_json及dot格式。
```
skills/tidb-sql/references/flashback.md
```
- FLASHBACK TABLE/DATABASE及FLASHBACK CLUSTER恢复指南。
```
skills/tidb-sql/references/tidb-cloud-ssl.md
```
- TiDB Cloud SSL验证要求及客户端参数。