CODEWITHSUNDEEP
mysqlsql
Bian Jiaping
Bian Jiaping

Reputation: 966

How to create tables with a prefix in a more maintainable way

I need to maintain a SQL script (for MySQL) which creates many tables with same prefix, like wp_parent, wp_teacher.

I don't want to write CREATE TABLE wp_parent. In that case, if I need to change the prefix, I have to modify many lines.

I have tried to use MySQL user variable, but it didn't work.

Is there any way to make prefix be a variable and then use the variable in other statement?

Or is there any other method to make the script more maintainable?

Upvotes: 0

Views: 682

Answers (2)

RamThakur
RamThakur

Reputation: 100

mysql> drop database if exists prefixdb;
Query OK, 4 rows affected (0.01 sec)

mysql> create database prefixdb;
Query OK, 1 row affected (0.00 sec)

mysql> use prefixdb
Database changed
mysql> create table tab1 (num int) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> create table tab2 like tab1;
Query OK, 0 rows affected (0.05 sec)

mysql> create table tab3 like tab1;
Query OK, 0 rows affected (0.05 sec)

mysql> create table tab4 like tab1;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+--------------------+
| Tables_in_prefixdb |
+--------------------+
| tab1               |
| tab2               |
| tab3               |
| tab4               |
+--------------------+
4 rows in set (0.00 sec)
The query to generate it would be

select
    concat('alter table ',db,'.',tb,' rename ',db,'.',prfx,tb,';')
from
    (select table_schema db,table_name tb
    from information_schema.tables where
    table_schema='prefixdb') A,
    (SELECT 'dr_' prfx) B
;
Running it at the command line I get this:

mysql> select concat('alter table ',db,'.',tb,' rename ',db,'.',prfx,tb,';') from (select table_schema db,table_name tb from information_schema.tables where table_schema='prefixdb') A,(SELECT 'dr_' prfx) B;
+----------------------------------------------------------------+
| concat('alter table ',db,'.',tb,' rename ',db,'.',prfx,tb,';') |
+----------------------------------------------------------------+
| alter table prefixdb.tab1 rename prefixdb.dr_tab1;             |
| alter table prefixdb.tab2 rename prefixdb.dr_tab2;             |
| alter table prefixdb.tab3 rename prefixdb.dr_tab3;             |
| alter table prefixdb.tab4 rename prefixdb.dr_tab4;             |
+----------------------------------------------------------------+
4 rows in set (0.00 sec)
Pass the result back into mysql like this:

mysql -hhostip -uuser -pass -AN -e"select concat('alter table ',db,'.',tb,' rename ',db,'.',prfx,tb,';') from (select table_schema db,table_name tb from information_schema.tables where table_schema='prefixdb') A,(SELECT 'dr_' prfx) B" | mysql -hhostip -uuser -ppass -AN
With regard to your question if you want to rename all tables, do not touch information_schema and mysql databases. Use this query instead:

select
    concat('alter table ',db,'.',tb,' rename ',db,'.',prfx,tb,';')
from
    (select table_schema db,table_name tb
    from information_schema.tables where
    table_schema not in ('information_schema','mysql')) A,
    (SELECT 'dr_' prfx) B ;

Upvotes: 0

RamThakur
RamThakur

Reputation: 100

Your can use this command to add the prefix to the tables in database

SELECT Concat('ALTER TABLE ', TABLE_NAME, ' RENAME TO dr_', TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES;

Give it a Try !!!

Upvotes: 1

Related Questions