如何从mysql数据库中的每个表名中删除前缀名

2022-01-05 00:00:00 sql mysql phpmyadmin

我有一个 joomla mysql 数据库，在我的所有表名上都有一个表名前缀jos_".但我想从我所有的表中删除它.我知道如何重命名每个表，一次一个，但我有 600 个表.是否有一个易于运行的 sql 查询来执行此操作.

I have a joomla mysql database with a table name prefix of "jos_" on all of my table names. But I would like to remove it from all of my tables. I understand how to rename each table, one at a time, but I have 600 tables. Is there an easy to run a sql query to do this.

如果有人有解决方案，能否请您发布我可以使用的确切 sql 查询?

If someone has a solution, could you please post the exact sql query I can use?

推荐答案

您可以使用单个查询生成必要的语句:

You can generate the necessary statements with a single query:

select 'RENAME TABLE ' || table_name ||  ' TO ' || substr(table_name, 5) ||';'
from information_schema.tables

将该查询的输出保存到一个文件中，您就拥有了所需的所有语句.

Save the output of that query to a file and you have all the statements you need.

或者，如果返回 0s 和 1s 而不是语句，这里是使用 concat 的版本:

Or if that returns 0s and 1s rather the statemenets, here's the version using concat instead:

select concat('RENAME TABLE ', concat(table_name, concat(' TO ', concat(substr(table_name, 5), ';'))))
from information_schema.tables;

相关文章