pingcap / pingcap/tidb

Support CRC32() Expressions or String-Based HASH Partitioning

Open
#63,057 1 comment 0 reactions 0 assignees View on GitHub
contribution first-time-contributor type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Feature Request

**Is your feature request related to a problem? Please describe:**
MySQL users migrating to TiDB encounter compatibility issues when attempting to replicate their sharding strategy. Their existing system uses Sharding-JDBC with UUID-type string fields (e.g., `store_id`) partitioned via `HASH(CRC32(store_id))`. TiDB's current HASH partitioning only supports integer columns and explicitly disallows the `CRC32()` function in partition expressions, blocking seamless migration.

**Describe the feature you'd like:**
Extend TiDB's partitioning syntax to support string-based hashing, either:
1. **Allow `CRC32()` in partition expressions**: Enable syntax like
```sql
PARTITION BY HASH(CRC32(store_id))
```
to convert strings to integers for partitioning.
2. **Native string column support (preferred)**: Directly accept string columns in HASH partitioning, e.g.,
```sql
PARTITION BY HASH(store_id)
```
where TiDB internally computes a consistent hash (e.g., using CRC32 or a non-cryptographic hash) without user-specified functions.

**Describe alternatives you've considered:**
| Alternative | Drawbacks |
|-------------|-----------|
| **Continue using Sharding-JDBC** | Loses TiDB's native partitioning optimizations (automatic data distribution, partition pruning). Increases operational complexity by maintaining middleware. |
| **Add redundant integer column** | Requires intrusive business logic changes:
- All writes must precompute/store `CRC32(store_id)`.
- All queries must explicitly reference the hash value, increasing code maintenance overhead. |

**Teachability, Documentation, Adoption, Migration Strategy:**
- **Beneficiaries**: MySQL→TiDB migrants using UUID-based sharding (e-commerce, IoT systems) and users requiring minimal code changes during migration.
- **Key Scenarios**:
- Shard migration: Preserve identical data distribution for UUID keys (e.g., `store_id`) when migrating from MySQL+ShardingJDBC.
- Query optimization: Enable partition pruning for `WHERE store_id = "uuid"` queries to avoid full-table scans.
- **Adoption**: Existing `PARTITION BY HASH` syntax remains unchanged for integers. New syntax for strings aligns with user expectations from MySQL-like systems.
- **Documentation**: Clearly specify supported hash functions and behavior for string columns (e.g., hash collision rates, compatibility notes).
- **Migration**: Users can directly replace Sharding-JDBC with TiDB partitions by altering DDL, avoiding application rewrites.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.