www.simplesharebuttons.comShare it!!! 1000

To rename a table in MySQL you just need to run a command named RENAME TABLE, the syntax is very easy to use,

RENAME TABLE tb1 TO tb2;

The RENAME TABLE command will rename the table atomically, which means your table will be locked during the command.
You can also rename more than one table in one command:

RENAME TABLE tb1 TO tb2, tb3 TO tb4;

To rename the table you will need ALTER and DROP privileges on the old table and CREATE and INSERT on the new one.

You just need to be careful if you use triggers, on the bellow example, after a INSERT on table Cities, I count +1 to NOfCities on the iserted Countries, what happens if I rename the table Contries?

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: PlusOneCities
               Event: INSERT
               Table: Cities
           Statement: UPDATE Countries SET NOfCities = NOfCities + 1 WHERE Code = NEW.CountryCode
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> INSERT INTO Cities SET Name = 'City1', CountryCode = 'BRA', District = 'Brasilia', Population = 10000;
Query OK, 1 row affected (0.04 sec)

mysql> RENAME TABLE Countries TO Country;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO Cities SET Name = 'City2', CountryCode = 'BRA', District = 'Brasilia', Population = 10000;
ERROR 1146 (42S02): Table 'world.Countries' doesn't exist

See, we need to manualy change all triggers.

You can find more information on MySQL Documentation

www.simplesharebuttons.comShare it!!! 1000