在代码中查看mysql数据库表前缀,从数据库的所有mysql表名称中删除前缀

*I want to remove prefix from all table names in MySQL

I have a test database with more than 100 tables, so i don't want to go through a manual process or renaming each table.

e.g

ci_categories

ci_products

expected output:

categories

products

i want to remove prefix from all table, that is ci_

Is there a MySQL query to achieve this?

Thanks

Amit

解决方案

I've written a mysql stored procedure in order to change the table names since there's no other straight forward way to change all the table names through a single query.

delimiter //

CREATE PROCEDURE `ChangeTableNameProcedure`()

BEGIN

DECLARE int_val INT DEFAULT 0;

DECLARE my_outer_cursor_done INT DEFAULT FALSE;

DECLARE my_oldTable VARCHAR(100);

DECLARE my_newTable VARCHAR(100);

DECLARE tableNameCursor CURSOR FOR SELECT TABLE_NAME oldName,

CONCAT(SUBSTRING(TABLE_NAME,POSITION('ci_' IN TABLE_NAME) + 3)) newName

FROM information_schema.tables

WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME LIKE 'ci_%';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET my_outer_cursor_done = TRUE;

OPEN tableNameCursor;

OUTER_CURSOR_LOOP: LOOP

FETCH FROM tableNameCursor INTO my_oldTable,my_newTable;

IF my_outer_cursor_done THEN

CLOSE tableNameCursor;

LEAVE OUTER_CURSOR_LOOP;

END IF;

SET @old = my_oldTable;

SET @new = my_newTable;

SET @statement = CONCAT('RENAME TABLE ',@old,' TO ',@new);

PREPARE stmt FROM @statement;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END LOOP OUTER_CURSOR_LOOP;

END//

N:B:

I've assumed that all the table names in your database has a prefix

ci_.

You have to put your database name in TABLE_SCHEMA=

'YOUR_DATABASE_NAME' clause.

Note that + 3 stands for the new table name where first three

characters will be skipped (if your old table name is 'ci_old_table'

then your new table name would be 'old_table'. Note that 'ci_' first

three characters have been skipped in new table name.)

After creating the procedure you need to call it simply by its name.

The syntax is Call ChangeTableNameProcedure();

Please make sure while creating the procedure you are not ignoring

the delimeter //