Essential Database Naming and Design Standards for TiDB & MySQL

Database Naming Conventions

Table name convention <code>Table names lowercase, no camelCase, e.g., ad_audit, job_seq; long names can use underscores</code>

Column name convention – avoid MySQL keywords such as order, group, show, slave <code>See MySQL keywords list: https://dev.mysql.com/doc/refman/8.0/en/keywords.html</code>

Index naming convention <code>Normal index: prefix idx_; unique index: prefix uniq_; e.g., idx_corpid_name, uniq_corpid_name; avoid idx_corp_id_corp_name</code>

Note: database, table, column, and index names must be lowercase and within 64 characters (TiDB limit).

Table Structure Design Guidelines

1. TiDB primary key – every table needs a primary key; it need not be auto‑increment int. Use UUID, string, or composite keys, and add SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3 to spread regions.

<code>SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS=3</code>

For high‑write workloads, consider AUTO_RANDOM primary key:

<code>id BIGINT PRIMARY KEY AUTO_RANDOM</code>

2. Always add comments to tables and columns.

<code>`mobile` VARCHAR(20) DEFAULT NULL COMMENT 'Contact mobile'</code>

3. Include created_at and updated_at columns with CURRENT_TIMESTAMP default values for incremental data extraction.

4. Define columns as NOT NULL whenever possible.

<code>user_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Username'
uid INT(10) NOT NULL DEFAULT '0' COMMENT 'User ID'
-- TEXT type must default NULL</code>

5. Columns declared NOT NULL must have a default value to avoid insert errors.

6. Use utf8mb4 for content fields to support emojis; large, non‑searchable BLOB/TEXT columns should be placed in separate tables.

7. Partition tables: time‑range partitions for logs/reports; hash partitions for heavy writes; list partitions are experimental (TiDB 5.x).

<code>Why use partitions? DROP/TRUNCATE partition is faster than DELETE.
TiDB 4.0 supports up to 1024 partitions, 5.x up to 8192.</code>

8. Choose appropriate column types and sizes:

<code>(1) Use TINYINT instead of INT for status/type/gender fields.
(2) Prefer numeric types over VARCHAR, e.g., store phone as BIGINT, IP as INT.
(3) Use CHAR for fixed‑length values (open_id CHAR(32)), VARCHAR for variable length (name VARCHAR(40)).
(4) Keep VARCHAR length reasonable; avoid default VARCHAR(255) when VARCHAR(50) suffices.
(5) Avoid TEXT when VARCHAR(10000) works; note TiDB KV size limit (6 MB) vs MySQL MEDIUMTEXT (16 MB).</code>

SQL Usage Standards

1. TiDB Index Usage

Use composite indexes when queries involve multiple columns; ensure the index covers all WHERE conditions for optimal performance. <code>Examples of usable composite index (A,B,C): WHERE A=... WHERE A=... AND B=... WHERE A=... AND B=... AND C=... Avoid creating redundant single‑column indexes.</code>

Include ORDER BY columns in the index to prevent costly sorts.

Limit the number of indexes: max 5 per table, max 5 columns per index; use prefix indexes for long strings; avoid indexing low‑cardinality columns. <code>2) No more than 5 fields per index. 3) Use prefix index for long CHAR(100). 4) Do not index columns with few distinct values (e.g., type with only 0/1).</code>

2. SQL Statement Guidelines

Avoid SELECT *; list required columns explicitly to reduce network transfer and prevent breakage when schema changes.

Never run SELECT/UPDATE/DELETE without a WHERE clause.

Avoid OR in WHERE clauses unless both sides are indexed; OR can trigger inefficient index merge.

For core OLTP workloads, avoid JOINs that may cause cluster instability.

Prefer soft‑delete (UPDATE) over hard DELETE for important data.

Batch INSERTs with reasonable batch size; large transactions can cause performance issues.

Beware TiDB’s transaction size limits (single KV ≤ 6 MB, default transaction ≤ 100 MB, configurable up to 10 GB).

TiDB DDL does not support multi‑column changes in a single ALTER statement; split into separate statements.

Do not allow developers to connect directly to production databases; use a dedicated SQL audit and execution platform.

3. Six Cases When an Index Is Not Used

<code>(1) Implicit conversion of string columns to numbers.
(2) Functions on indexed columns (e.g., DATE(date_column)).
(3) Arithmetic expressions (e.g., start - end = 10).
(4) Leading wildcard LIKE '%text' prevents index use; use 'text%' instead.
(5) Indexing low‑selectivity columns (e.g., type with only 0/1).
(6) Implicit conversion where varchar column stores numeric strings and is queried as numbers.</code>

TiDB vs MySQL Compatibility Differences

When migrating large‑scale MySQL or sharded databases to TiDB, be aware of these differences:

1. TiDB auto‑increment IDs are not contiguous; ordering by ID must be replaced with time‑based ordering.

2. TiDB does not support foreign keys, stored procedures, triggers, or full‑text indexes.

3. Collation differs: default binary collation (utf8mb4_bin) is case‑sensitive, whereas MySQL’s default (utf8mb4_general_ci) is case‑insensitive. TiDB 4.0+ and 5.x support case‑insensitive collations when explicitly set.

4. ALTER TABLE cannot modify multiple columns or indexes in one statement; attempting this yields “Unsupported multi schema change”.

5. TiDB 4.0 cannot add or drop primary keys unless alter-primary-key is enabled.

6. TiDB does not support changing a column to a broader type (e.g., INTEGER → VARCHAR) or from TIMESTAMP to DATETIME.

7. “Lossy” type changes (bigint → int, varchar(200) → varchar(100)) are prohibited.

8. Transaction limits: single KV ≤ 6 MB; mediumtext (up to 16 MB in MySQL) may exceed TiDB limits.

9. Default transaction size limit is 100 MB, configurable up to 10 GB via txn-total-size-limit.

See the official documentation for more details: https://docs.pingcap.com/zh/tidb/stable/tidb-limitations