mysql - 在phpMyAdmin中删除数据库中所有表的公共(public)前缀

Question

我所有的表都有前缀bn_。例如，一个表将被命名为 bn_blast。

要运行的任何 SQL 命令都会删除所有表中的所有前缀吗？

Answer 1

这是一条线。

在控制台中:

mysql -u root -p -AN -e" select concat('RENAME TABLE ', concat(table_name, concat(' TO ', concat(substr(table_name,4 ), ';')))) from information_schema.tables where table_schema='db_name' " > renaming.sql

出现提示时输入密码。

打开“renaming.sql”文件，在第一行添加“use db_name;”然后保存。然后，执行以下操作:

mysql -u root -p < renaming.sql

出现提示时输入密码。

现在，检查你的表格:

show tables;

输出:之前

mysql> show tables;
+---------------------+
| Tables_in_STACK     |
+---------------------+
| db_answer           |
| db_answers          |
| db_circle           |
| db_fee              |
| db_housing          |
| db_im_originals     |
| db_im_savegroups    |
| db_im_savespecs     |
| db_location_share   |
| db_order1           |
| db_orderitems       |
| db_patientinfo      |
| db_quest            |
| db_share            |
| db_t2               |
| db_tbdatabaseerrors |
| db_tblchanges       |
| db_test             |
| db_test1            |
| db_test_fid         |
| db_test_table       |
| db_testing          |
| db_user             |
+---------------------+

输出:之后

mysql> show tables;
+-------------------+
| Tables_in_STACK   |
+-------------------+
| answer            |
| answers           |
| circle            |
| fee               |
| housing           |
| im_originals      |
| im_savegroups     |
| im_savespecs      |
| location_share    |
| order1            |
| orderitems        |
| patientinfo       |
| quest             |
| share             |
| t2                |
| tbdatabaseerrors  |
| tblchanges        |
| test              |
| test1             |
| test_fid          |
| test_table        |
| testing           |
| user              |
+-------------------+