Stored procedure for establishing a database on another database

[Trenton]

Is it possible to establish a stored dataset procedure at MySQL 5.5 on another database template (copulation of database structure)?

The establishment of a database, through a stored procedure, works at the end. 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 be the procedure for establishing a database on another database?

CALL CreateDbLike("template_db_name");

Only the structure needs to be copied.

PS: Do what is needed by SQL without calling mysqldump or other utilises.

[gionna]

I propose the following decision: to extract a list of tables from the system table information_schemafor each of the tables, form an operator CREATE TABLE For the new database, unfortunately, we can't use the operator. SHOW CREATE TABLE) List further received CREATE TABLE We are in the course and form the expression prepared (PREPAREWhich we do.

For convenience, form a support function columns_build()to accept the name of the database db, name of the table tbl and form a list of the columns and their attributes through the comma. I'll be right there's not a detailed solution, but the attributes are not copied entirely if you need absolute precision - this function will have to be refined.

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 ;

It is now possible to start developing the final function copy_database()which accepts as database first parameter olddband, as the second, the database newdb♪ Here, for brevity, we also cut corners a little while, suggesting that the moves of all the tables are the same and matches the default for the server, the servers are encoding 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 ;

The tables from one database can now be copied test Other newdb

CALL copy_database('test', 'newdb');