How to Copy Data When Converting a Regular Table to a Partitioned Table

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

Original topic: 普通表改成分区表,数据如何拷贝

| username: tidb126

How to quickly migrate the data from the original table to the partitioned table when converting a regular table to a partitioned table?

| username: hey-hoho | Original post link

Export the original table data for backup → Create a new partitioned table → Import the backup data into the new partitioned table → (Depending on the actual situation, decide whether to rename the table)

| username: tidb126 | Original post link

Is it faster to use “insert … select” directly or to import and export when the number of tables is in the tens of millions?

| username: 考试没答案 | Original post link

Millions of levels are very fast. Write good scripts and build good indexes. Insert, select, where.

| username: 考试没答案 | Original post link

Performing an alter on the original table, creating a new table, running scripts, and updating the latest data. This might cause the business to be unable to write data.

| username: Billmay表妹 | Original post link

To convert a regular TiDB table to a partitioned table, follow these steps:

  1. Create a partitioned table: Use the CREATE TABLE statement to create a new partitioned table, specifying the partition key and the number of partitions. For example:

    CREATE TABLE t1 (
      id INT NOT NULL,
      name VARCHAR(20),
      age INT,
      PRIMARY KEY (id)
    ) PARTITION BY RANGE (age) (
      PARTITION p0 VALUES LESS THAN (18),
      PARTITION p1 VALUES LESS THAN (30),
      PARTITION p2 VALUES LESS THAN (MAXVALUE)
    );
    

    Here, a partitioned table named t1 is created using the RANGE partitioning method, partitioned by the age field into three partitions: p0, p1, and p2.

  2. Copy data from the regular table to the partitioned table: Use the INSERT INTO SELECT statement to copy data from the regular table to the new partitioned table. For example:

    INSERT INTO t1 (id, name, age)
    SELECT id, name, age FROM t0;
    

    Here, data from the regular table t0 is copied to the new partitioned table t1.

  3. Verify the data: Use the SELECT statement to verify that the data in the new partitioned table is correct. For example:

    SELECT * FROM t1;
    

    Here, all data in the new partitioned table t1 is queried.

Note that if the data in the regular table is large, the data copying process may be time-consuming. Additionally, if the data in the regular table has been modified, the data copied to the partitioned table may be inconsistent with the original data. Therefore, it is recommended to back up the data in the regular table before copying, so that it can be restored in case of any issues.

| username: tidb菜鸟一只 | Original post link

It is recommended to export and import for tens of millions of records.

| username: Kongdom | Original post link

Is it possible to import and export by partition?

| username: xingzhenxiang | Original post link

For tens of millions of records, it is recommended to use export and import. The statement INSERT INTO t1 (id, name, age) SELECT id, name, age FROM t0; will not execute successfully.

| username: hey-hoho | Original post link

For tables with tens of millions of rows, using “insert into select” has a high possibility of causing an OOM error. It is recommended to use Dumpling + Lightning for export and import.

| username: tidb126 | Original post link

The speed is quite good when using insert into select for batch operations.

| username: Hacker007 | Original post link

For large amounts of data, it is recommended to use dumpling + lightning. Using insert select may cause memory overflow and data loss.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.