12
I have a Mysql database with several tables and am migrating to another database.
I have Client and must stay Cliente_pe
I have product and should stay
How can I accomplish this without however stopping to rename each of the 50 tables?
12
I have a Mysql database with several tables and am migrating to another database.
I have Client and must stay Cliente_pe
I have product and should stay
How can I accomplish this without however stopping to rename each of the 50 tables?
19
To rename a table, you use the command
RENAME TABLE nomeOriginal TO nomeNovo
As there are many tables, you can use this query to generate all RENAME
s automatically:
SELECT
CONCAT(
'RENAME TABLE `', table_schema, '`.`', table_name,
'` TO `', table_schema, '`.`', table_name, '_PE`;' )
FROM
INFORMATION_SCHEMA.TABLES WHERE table_schema="NomeDoDb";
Output example:
RENAME TABLE `NomeDoDb`.`eventos` TO `NomeDoDb`.`eventos_PE`;
RENAME TABLE `NomeDoDb`.`fotos` TO `NomeDoDb`.`fotos_PE`;
RENAME TABLE `NomeDoDb`.`usuarios` TO `NomeDoDb`.`usuarios_PE`;
Then just take the result and run as a new query, either via copy & Paste, redirecting to an SQL script, etc.
Perks:
WHERE
or manually;WHERE
;PROCEDURE
, and use whenever necessary, example: AdicionarSufixo( 'MeuDb', '_PE' );
13
The easy way, since 50 is a not so great number of lines:
RENAME TABLE
Cliente TO Cliente_PE,
Produto TO Produto_PE,
TabelaX TO TabelaX_PE
See the sql manual.
You can also do this using the phpmyadmin
, but then you will be adding a prefix to the tables (Pe_client, etc):
Check All
.Add prefix to table
.prefix_
, in your case, PE_
.Interesting this phpmyadmin function
By the way, it would be a good suggestion for phpmyadmin devs to have a suffix option as well. It would probably just be a minimal fit in the prefix function.
0
In phpmyadmin (or adminer), go to "SQL" and type the command:
RENAME TABLE current name_to new_name
Click on Run. After that, give F4 to update the list of tables in the database!
Browser other questions tagged php mysql sql
You are not signed in. Login or sign up in order to post.
Are the tables already populated? In two steps it’s simple, you select to get the table name, and generate an output with
concat( 'rename table ', nome, ' to ', nome, '_PE' )
. Then just take this output and run as a query.– Bacco
You want to add the posfixo in the table this?
– gmsantos
That’s right...a postfix on the tables!
– alexjosesilva