Rename Table¶

In MatrixOne, the RENAME TABLE statement is used to change the name of a table.

Syntax description¶

In MatrixOne, the RENAME TABLE statement is used to change the name of a table. You can change the names of multiple tables at once.

Things to note:

RENAME TABLE is an atomic operation. If any rename fails, all rename operations are rolled back.
Cross-database RENAME TABLE is not supported. When cross-database syntax is used (e.g., db1.tbl TO db2.tbl), MatrixOne silently renames the table within the current database instead of raising an error. MySQL 8.0 supports cross-database RENAME TABLE. To move a table across databases, first copy the table to the target database and then delete the original table.
Before renaming a table, make sure there are no active transactions or locks using the table.

Grammar structure¶

> RENAME TABLE
    tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

Chained RENAME TABLE (Multi-Pair Atomic Rename)¶

RENAME TABLE supports multiple rename pairs in a single statement, executed atomically. All renames in the statement either succeed together or fail together. This enables the classic MySQL-style 3-way swap pattern for zero-downtime table rotation:

RENAME TABLE t_live TO t_tmp, t_shadow TO t_live, t_tmp TO t_shadow;

Each source table must exist at the start of the statement, and each target name must not exist at the start (unless the table is being renamed in the same statement). Renaming the same source table to two different targets in a single statement is an error.

Example¶

Basic Rename¶

DROP DATABASE IF EXISTS rename_demo;
CREATE DATABASE rename_demo;
USE rename_demo;

create table old_table1(n1 int);
create table old_table2(n1 int);
create table old_table3(n1 int);

RENAME TABLE old_table1 TO new_table1;
RENAME TABLE old_table2 TO new_table2,old_table3 TO new_table3;

mysql> show tables;
+-----------------------+
| Tables_in_rename_demo |
+-----------------------+
| new_table1            |
| new_table2            |
| new_table3            |
+-----------------------+
3 rows in set (0.00 sec)

mysql> DROP DATABASE rename_demo;

Chained Atomic Swap¶

DROP DATABASE IF EXISTS test_rename_chain;
CREATE DATABASE test_rename_chain;
USE test_rename_chain;

-- 3-way atomic swap: live <-> shadow
CREATE TABLE t_live (id INT);
CREATE TABLE t_shadow (id INT);
INSERT INTO t_live VALUES (100);
INSERT INTO t_shadow VALUES (200);

RENAME TABLE t_live TO t_tmp, t_shadow TO t_live, t_tmp TO t_shadow;

SELECT * FROM t_live;
SELECT * FROM t_shadow;

-- Multiple non-conflicting renames
CREATE TABLE a (id INT);
CREATE TABLE b (id INT);
INSERT INTO a VALUES (1);
INSERT INTO b VALUES (2);

RENAME TABLE a TO c, b TO a;

SELECT * FROM a;
SELECT * FROM c;

DROP DATABASE test_rename_chain;