Support CRC32() Expressions or String-Based HASH Partitioning
- 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
Assessment
This issue has not been assessed yet.