SQL Statement for Renaming a Table in MySQL

When using MySQL, you will often encounter situations where a table name does not meet naming conventions or standards. If the table already contains a large amount of data, the most direct approach is not to recreate the table and re-import the data, but to rename the original table directly.

In MySQL 5.0 and later, you can use ALTER TABLE ... RENAME TO ... to rename a table:

ALTER TABLE table_name RENAME TO new_table_name;

For example, to rename the admin_user table to a_user:

ALTER TABLE admin_user RENAME TO a_user;

You can also use the RENAME TABLE statement to perform the same operation:

RENAME TABLE admin_user TO a_user;

Before executing the change, it is recommended to perform a few checks:

1. Confirm that the new table name does not already exist to avoid naming conflicts.
2. Check whether application code, views, stored procedures, scheduled tasks, and so on reference the old table name.
3. Back up the database before operating in production, or at least back up the relevant table.
4. If the table is large or the business is actively accessing it, try to schedule the operation during off-peak hours.

You can first use the following statements to confirm whether the tables exist:

SHOW TABLES LIKE 'admin_user';
SHOW TABLES LIKE 'a_user';

After renaming, verify it again:

SHOW TABLES LIKE 'a_user';
SELECT COUNT(*) FROM a_user;

This lets you change only the table name while keeping the original data intact.