Is it possible to create a stored procedure for creating a database in MySQL 5.5 using a template from another database (copying the database structure)?

Creating a database through a stored procedure works with a bang. Example:

DELIMITER // CREATE PROCEDURE CreateDB() BEGIN CREATE DATABASE `template_test` CHARACTER SET utf8 COLLATE utf8_unicode_ci; CREATE TABLE `template_test`.`table_name` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `template_test`.`table_name` (`id`,`name`) VALUES (1, "Главная"); END // DELIMITER ;

What should the procedure for creating a database from another database template look like?

 CALL CreateDbLike("template_db_name");

Copy need only structure.

PS: you need to do it by SQL, without calling mysqldump or other utilities.

  • SHOW CREATE TABLE table_name; - table structure in the format "CREATE TABLE" - splash58
  • @ splash58 SHOW CREATE TABLE saves AUTO_INCREMENT. You have to manually reset it. Is there a way to immediately get AUTO_INCREMENT = 1? - atom-22
  • To reset the auto-increment, you can issue the ALTER TABLE tbl AUTO_INCREMENT = 1; or TRUNCATE TABLE table_name; - Alexey Ukolov

1 answer 1

I propose the following solution: extract the list of tables from the system table information_schema , create a CREATE TABLE statement for each table for the new database immediately (unfortunately, we will not be able to use the SHOW CREATE TABLE statement). Next, the resulting list of CREATE TABLE strings is traversed by the cursor and the prepared expression ( PREPARE ) is generated, which we execute.

For convenience, we will create an auxiliary function columns_build() , which will take the database name db , the table name tbl and form a list of columns and their attributes separated by commas. At once I will make a reservation, this is not a detailed solution, the attributes are not copied in detail, if absolute accuracy is needed - this function will have to be improved.

 DELIMITER // DROP FUNCTION IF EXISTS columns_build// CREATE FUNCTION columns_build ( `db` TINYTEXT, `tbl` TINYTEXT ) RETURNS TEXT BEGIN SET @returns = ''; SELECT GROUP_CONCAT( CONCAT( c.COLUMN_NAME, ' ', c.COLUMN_TYPE, ' ', IF(c.IS_NULLABLE, ' NULL ', ' NOT NULL '), IF(c.EXTRA = 'auto_increment', 'AUTO_INCREMENT PRIMARY KEY', '') ) ORDER BY c.ORDINAL_POSITION SEPARATOR ', ' ) AS total INTO @returns FROM information_schema.COLUMNS AS c WHERE TABLE_SCHEMA = db AND TABLE_NAME = tbl ORDER BY ORDINAL_POSITION; IF(SUBSTRING(@returns, -1) = ',') THEN RETURN SUBSTRING(@returns, 1, CHAR_LENGTH(@returns) - 1); ELSE RETURN @returns; END IF; END// DELIMITER ;

Now you can begin to develop the final function copy_database() , which takes the source database olddb as the first parameter and the destination newdb as the second newdb . Here, for the sake of brevity, we also cut corners a little, assuming that the engine of all tables is the same and coincides with the default for the server, while the server settings specify the encoding of databases and tables.

 DELIMITER // DROP PROCEDURE IF EXISTS copy_database// CREATE PROCEDURE copy_database( IN olddb TINYTEXT, IN newdb TINYTEXT) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE querystr TEXT DEFAULT ''; DECLARE curr CURSOR FOR SELECT CONCAT( 'CREATE TABLE ', newdb, '.', t.TABLE_NAME, '(', columns_build(olddb, t.TABLE_NAME), ')') AS create_table FROM information_schema.TABLES AS t WHERE TABLE_SCHEMA = olddb; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN curr; read_loop: LOOP FETCH curr INTO querystr; IF done THEN LEAVE read_loop; END IF; SET @str = querystr; -- SELECT @str; PREPARE stmt FROM @str; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; CLOSE curr; END// DELIMITER ;

Now you can copy tables from one test database to another newdb

 CALL copy_database('test', 'newdb');
  • Just wow! I did not think that this task will be released in two different procedures. Mysql obviously lacks CREATE DATABASE `dbName` LIKE `templateDb`; - atom-22