从数据库的所有mysql表名称中删除前缀 [英] Remove prefix from all mysql table names of a database

问题描述

*我想从MySQL中所有表名中删除前缀

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

我有一个包含超过100个表的 test 数据库，所以我不想经历手动过程或重命名每个表.

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.

例如 ci_categories ci_products

预期输出: 类别 产品

我想从所有表中删除前缀，即 ci _

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

是否存在MySQL查询来实现这一目标?

Is there a MySQL query to achieve this?

谢谢 阿米特(Amit)

Thanks Amit

推荐答案

我已经编写了一个mysql存储过程来更改表名，因为没有其他直接方法可以通过单个查询来更改所有表名.

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:

1. 我假设您数据库中的所有表名都有一个前缀 ci _ .
2. 您必须将数据库名称放入TABLE_SCHEMA = "YOUR_DATABASE_NAME"子句.
3. 请注意， + 3 代表新表名，其中前三个 字符将被跳过(如果您的旧表名称为"ci_old_table" 那么您的新表名称将为"old_table".请注意，首先使用"ci_" 新表名中已跳过了三个字符.)
4. 创建过程后，您只需按其名称进行调用. 语法为Call ChangeTableNameProcedure();
5. 请确保在创建过程时不要忽略 距离表//

1. I've assumed that all the table names in your database has a prefix ci_.
2. You have to put your database name in TABLE_SCHEMA= 'YOUR_DATABASE_NAME' clause.
3. 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.)
4. After creating the procedure you need to call it simply by its name. The syntax is Call ChangeTableNameProcedure();
5. Please make sure while creating the procedure you are not ignoring the delimeter //

这篇关于从数据库的所有mysql表名称中删除前缀的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！