How to remove a prefix name from every table name in a mysql database

I have a joomla mysql databa开发者_如何学Gose 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.

If someone has a solution, could you please post the exact sql query I can use?

1. In phpmyadmin select all tables of your database.
   
2. From the dropdown 'With selected:' choose 'Replace table prefix'
   
3. Set from->to replacement.
   
4. DONE

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.

Or if that returns 0s and 1s 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;

You can create your own stored procedure to rename your tables, with that you don't need to open an external editor everything will be done on the server:

delimiter //
CREATE PROCEDURE rename_tables( IN db CHAR(255), IN srch CHAR(255), IN rplc CHAR(255) )
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE from_table CHAR(255);
  DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA=db;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;

  read_loop: LOOP
    IF done THEN
      LEAVE read_loop;
    END IF;

    FETCH cur1 INTO from_table;
    SET @to_table = REPLACE(from_table, srch, rplc);

    IF from_table != @to_table THEN
      SET @rename_query = CONCAT('RENAME TABLE ', db, '.', from_table, ' TO ', @to_table, ';');

      PREPARE stmt FROM @rename_query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
    END IF;

  END LOOP;

  CLOSE cur1;
END//
delimiter ;

Usage:

CALL rename_tables('test', 'jos_', '');

Update: This was my first MySQL stored procedure and I ran into the 6 years old bug #5967 which was quite annoying, your variable names must be different from the field names, because if they aren't you'll get NULL values in your variables.

So be aware of that if you decide to write a MySQL stored procedure.