Stored procedure for establishing a database on another database



  • 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.



  • 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');


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2