如何在单个查询中更改所有表前缀 [英] How to change all table prefix in a single query

问题描述

我在mysql上非常业余..请您告诉我如何在一个查询中更改整个数据库的表前缀...我可以手动进行，但是更改所有表前缀非常耗时.请帮帮我.就像isc_administrator_log到cus_administrator_log一样，也意味着isc_到cus _

I am pretty amateur in mysql..can you please tell me how can I change table prefixes of my whole database in a single query... I can do it manually, but its quite time consuming to change all the tables prefixes. Please help me out. Like isc_administrator_log to cus_administrator_log means isc_ to cus_

我找到了这两种解决方案，但都不了解它们中的任何一个.

I found these two solutions but do not understand either of them.

SELECT 
GROUP_CONCAT('RENAME TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', 
TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`;' SEPARATOR ' ')
FROM `TABLES` WHERE `TABLE_SCHEMA` = "test";

和

SELECT 
CONCAT('RENAME TABLE ', GROUP_CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME,
'` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`')) AS q
FROM 
`information_schema`.`Tables` WHERE TABLE_SCHEMA='test';

推荐答案

对于 MySQL 5.0.13 ，您可以进行动态SQL查询

delimiter // 
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
    SET @s = CONCAT('SELECT 'RENAME TABLE ', 
             GROUP_CONCAT('', TABLE_SCHEMA, ''.'', TABLE_NAME,
             ' TO ', TABLE_SCHEMA, ''='.prefix_''', TABLE_NAME, '')) AS q
             FROM 
             information_schema.Tables WHERE TABLE_SCHEMA='test'';;'
    PREPARE stmt FROM @s;
    EXECUTE stmt;
END
//
delimiter ;

从此处获得参考a>

这篇关于如何在单个查询中更改所有表前缀的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！