Skip to content

Instantly share code, notes, and snippets.

@zarv1k
Created February 6, 2016 09:20
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save zarv1k/da39c182c2e8f55c9820 to your computer and use it in GitHub Desktop.
Change prefix/suffix in MySQL tables
SET group_concat_max_len = 3072; -- increase for DB with many tables
SET @db = 'db_name';
SET @old_prefix = 'prefix_';
SET @old_suffix = '_suffix';
SET @new_prefix = '';
SET @new_suffix = '';
SELECT CONCAT('RENAME TABLE ', GROUP_CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`', @new_prefix, SUBSTRING(TABLE_NAME, LENGTH(@old_prefix) + 1, LENGTH(TABLE_NAME) - LENGTH(@old_suffix) - LENGTH(@old_prefix)), @new_suffix, '`')) AS rename_query
FROM `information_schema`.`Tables`
WHERE TABLE_SCHEMA=@db;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment