如何从mysql数据库中的每个表名称中删除前缀名称 [英] How to remove a prefix name from every table name in a mysql database
问题描述
我有一个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.
或者如果返回的是0
和1
而不是状态菜单,则此版本使用concat
代替:
Or if that returns 0
s and 1
s 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;
这篇关于如何从mysql数据库中的每个表名称中删除前缀名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!