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;