Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

This is often done during operation and maintenance: MySQL changes the table name, creates an identical table without data, and copies a few entries from the old table into it.

Change the name of the table：

RENAME TABLE old_table_name TO new_table_name;

This will convert the original tableold_table_nameRename tonew_table_name。

Create a table with the same structure but without data：

CREATE TABLE new_table LIKE old_table_name;

This will create a file callednew_tableThe new table, whose structure is based onold_table_name, but no data is copied.

Copy several records from the old table to the new table：

INSERT INTO new_table SELECT * FROM old_table_name LIMIT number_of_rows;

This will start fromold_table_nameBefore the selectionnumber_of_rowsstripData Insertionarrivenew_tableYou need tonumber_of_rowsReplace with the one you wantcopyRows.

Please make sure to back up your data before performing any of these operations to prevent any unforeseen problems. If you are unsure, you can firsttest environmentTry these commands.

Here is an example combining all of the above steps together:

-- 假设原来的表名为 orders
 -- 第一步：重命名原来的表
RENAME TABLE orders TO orders_backup; 
-- 第二步：创建一个新的空表，结构和原来的表一样
CREATE TABLE orders LIKE orders_backup; 
-- 第三步：复制原表的前10条数据到新表中
INSERT INTO orders SELECT * FROM orders_backup LIMIT 10;

These operations should be performed in the MySQL command line tool or any database management tool that provides an SQL interface. Make sure you have sufficient permissions to execute these commands.