5

I have a Master DB. I want to make duplicate DB of the Master DB but not all tables just specified.

I have those table names in comma separated string. tbl1, tbl2, tbl7, tbl10 etc.

I'm trying to make a stored procedure passing it new DB name That creates the DB and replicate the given table to it. So far i have 2 thing, but i don't know how to loop through it. I'm new to DB so please guide how to achieve this.

(Note) I haven't found if an array can be used to loop through, the table names could also be stored in array variable, because the table names are hard coded.

The following is the sudo code i desire.

CREATE DATABASE %param_db%;

@tables = 'tbl1, tbl2, tbl7, tbl10';
loop through @tables as table_name
    CREATE TABLE %param_db.table_name% LIKE Master.%table_name%;
End loop
6

Here are the steps to layout in the Stored Procedure

  • Create the Target database
  • Determine how many elements are in the list of tables
  • Loop through each element of the list of tables using the ELT() function
  • Take each element and form SQL Statement to create the new table in Target DB

Here is that Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`TableCookieCutter` $$
CREATE PROCEDURE `test`.`TableCookieCutter`
(
    SourceDB VARCHAR(64),
    TargetDB VARCHAR(64),
    TableList VARCHAR(1024)
)
BEGIN

    # Create the Target DB
    SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',TargetDB);
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

    # Strip Table List of Blanks
    # Enclose each table in quotes
    SET @table_list = CONCAT('''',REPLACE(REPLACE(TableList ,' ',''),',',QUOTE(',')),'''');

    # Count the number of tables in the list
    SET @table_stub = REPLACE(@table_list,',','');
    SET @array_count = LENGTH(@table_list) - LENGTH(@table_stub) + 1;

    # Loop through list of tables, creating each table
    SET @x = 0;
    WHILE @x < @array_count DO
        SET @x = @x + 1;
        SET @sql = CONCAT('SELECT ELT(',@x,',',@table_list,') INTO @tb');
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
        SET @sql = CONCAT('CREATE TABLE ',TargetDB,'.',@tb,' LIKE ',SourceDB,'.',@tb);
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END WHILE;

END $$

DELIMITER ;
2

@RolandoMySQLDBA post it first, but I have another way.

Using this to split comma string separated and this for the number of occurrences, I've made this Stored Procedure:

STORED PROCEDURE:

CREATE DEFINER=`root`@`localhost` PROCEDURE `spCreateRplDB`(
IN iNewDB VARCHAR(100),
IN iOldDB VARCHAR(100),
IN iTables VARCHAR(150))
BEGIN
# Comma separated string
SET @repldb=iNewDB;
SET @masterdb=iOldDB;
SET @tbls=TRIM(iTables);
SET @counter=(SELECT ROUND((LENGTH(@tbls) - LENGTH( REPLACE (@tbls, ",", ""))) / LENGTH(",")));
SET @loop_num=0;
# Splitting comma separated string
IF (@counter>0 && @repldb<>'' && @masterdb <> '') THEN
    SET @counter=@counter+1; # [ ADDING +1 BECAUSE @counter JUST COUNT THE NUMBER OF OCCURENCES OF A STRING INSIDE @tbls ]
    # Creating DB if not exists
    SET @database_create=CONCAT(" CREATE DATABASE IF NOT EXISTS ",@repldb,";");
    PREPARE database_create FROM @database_create;
    EXECUTE database_create;
    DEALLOCATE PREPARE database_create;

    WHILE @loop_num<>@counter DO
        SET @loop_num=@loop_num+1; 
        SET @rownumber=0; # Row Number
        SET @tbl='';
        SELECT 
            v.value 
        INTO @tbl
        FROM (SELECT 
                    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.value, ',', n.n), ',', -1)) AS value,
                    @rownumber:=@rownumber+1 AS rownumber
                FROM (SELECT @tbls AS value) AS t 
                CROSS JOIN (SELECT a.N + b.N * 10 + 1 n
                            FROM 
                            (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
                           ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
                                ORDER BY n
                            ) n
                WHERE n.n <= 1 + (LENGTH(t.value) - LENGTH(REPLACE(t.value, ',', ''))) 
                HAVING rownumber=@loop_num) AS v;
        # Creating Table
        SET @drop_table=CONCAT(" DROP TABLE IF EXISTS ",@repldb,".",@tbl,";");
        SET @create_table=CONCAT(" CREATE TABLE ",@repldb,".",@tbl," LIKE ",@masterdb,".",@tbl,";");
        # Preparing STMT for @drop_table
        PREPARE droptable FROM @drop_table;
        EXECUTE droptable;
        DEALLOCATE PREPARE droptable;
        # Preparing STMT for @create_table
        PREPARE create_table FROM @create_table;
        EXECUTE create_table;
        DEALLOCATE PREPARE create_table;
    END WHILE;
END IF;
END

PROCESSES:

  1. @counter, number of occurrences of , (tables delimiter).
  2. If there's more than 0 occurrences and iNewDB and iOldDB is not null or empty then continue.
  3. Create the new database (iNewDB).
  4. Creating new tables with the number of inputs in the iTables parameter.

TEST:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test_old           |
+--------------------+
5 rows in set (0.00 sec)

mysql> SHOW TABLES IN test_old;
+--------------------+
| Tables_in_test_old |
+--------------------+
| test1              |
| test2              |
| test3              |
| test4              |
+--------------------+
4 rows in set (0.00 sec)

mysql> 
mysql> CALL test.spCreateRplDB(
    -> 'test_dev', # New DB
    -> 'test_old', # Master DB
    -> 'test1,test2,test4');
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test_dev           |
| test_old           |
+--------------------+
6 rows in set (0.00 sec)

mysql> SHOW TABLES IN test_dev;
+--------------------+
| Tables_in_test_dev |
+--------------------+
| test1              |
| test2              |
| test4              |
+--------------------+
3 rows in set (0.00 sec)

mysql> 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.