Does the new version of TiDB support converting a regular table to a partitioned table?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 新版的tidb支持普通表转分区表吗？

| username: codingBoyYe

Does the new version currently support converting a regular table to a partitioned table?

Something like ALTER TABLE table PARTITION by range?

2
| username: ShawnYan | Original post link

Not supported, please refer to the alter table syntax ALTER TABLE | PingCAP 文档中心

3
| username: tidb狂热爱好者 | Original post link

When creating a table, think it through
ALTER TABLE is not supported
You can create the table first, then SELECT INTO it, and rename the table. There are more solutions than difficulties.

MySQL Compatibility

The ALTER TABLE syntax in TiDB has the following limitations:

  • Multiple changes in a single ALTER TABLE statement are not supported.
  • Changes of the Reorg-Data type on primary key columns are not supported.
  • Column type changes on partitioned tables are not supported.
  • Column type changes on generated columns are not supported.
  • Changes of certain data types (e.g., some time types, Bit, Set, Enum, JSON, etc.) are not supported due to compatibility issues between the CAST function in TiDB and MySQL.
  • Spatial data types are not supported.
  • ALTER TABLE t CACHE | NOCACHE is not standard MySQL syntax but an extended feature in TiDB. Refer to cached tables.
4
| username: xuexiaogang | Original post link

My personal suggestion is to create another partitioned table and insert the data into it. The table design should be determined at the beginning.

5
| username: ShawnYan | Original post link

The latest version 7.4.0 is already supported.

mysql> show create table t2;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table t2 partition by range (a) (   PARTITION p0 VALUES LESS THAN (18),   PARTITION p1 VALUES LESS THAN (30),   PARTITION p2 VALUES LESS THAN (MAXVALUE) );
Query OK, 0 rows affected, 1 warning (0.50 sec)

*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (`a`)
(PARTITION `p0` VALUES LESS THAN (18),
 PARTITION `p1` VALUES LESS THAN (30),
 PARTITION `p2` VALUES LESS THAN (MAXVALUE))
1 row in set (0.00 sec)
6
| username: 像风一样的男子 | Original post link

Currently not supported.

7
| username: zhanggame1 | Original post link

Not supported. If you need it quickly, you can use Dumpling to back up and restore the table to readjust the table structure.

8
| username: 路在何chu | Original post link

I don’t understand TiDB. What is the reason for using partitioned tables?

9
| username: zhanggame1 | Original post link

Deleting data is convenient without partition tables, but you can only use delete, which is slow. You can’t delete too much data at once, or it will cause OOM. After deletion, disk space is not released, and space usage will quickly increase during the deletion process. With partition tables, you can use drop partition to delete, which is very fast and can release disk space.