一键创建mycat分片表

使用储存过程创建mycat分片表

在数据库开发中，我们经常需要创建分片表来存储大量数据。为了简化这一过程，我们可以使用储存过程来自动创建分片表。本文将介绍三个储存过程，它们可以帮助我们创建分片表。

1. 创建储存过程 “creatingAFragmentTable”

这个储存过程用于遍历一个表的所有分片，并调用另外两个储存过程来生成创建表的SQL语句，并执行创建表操作。首先，我们声明了一些变量，如表名、游标和结束处理程序。然后，我们使用游标遍历目标表的所有分片，并调用 “generate_create_table” 储存过程来生成创建表的SQL语句。最后，我们调用 “create_shard_tables” 储存过程来执行创建表的操作。下面是 “creatingAFragmentTable” 储存过程的代码：

CREATE DEFINER=`root`@`%` PROCEDURE `creatingAFragmentTable`(in db_name VARCHAR(50))
BEGIN
     DECLARE t_name VARCHAR(64);
     DECLARE done INT DEFAULT FALSE;
     DECLARE cur CURSOR FOR SELECT name FROM mycat_table;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

     OPEN cur;
     read_loop: LOOP
          FETCH cur INTO t_name;
          IF done THEN
               LEAVE read_loop;
          END IF;
          -- 获取创建SQL
          CALL generate_create_table(db_name, t_name, @create_sql);
          CALL create_shard_tables(t_name, @create_sql);
     END LOOP;

     CLOSE cur;
END

2. 创建储存过程 “generate_create_table”

这个储存过程用于生成创建表的SQL语句。它使用游标遍历目标表的所有字段，并构建字段部分。然后，它再使用游标遍历目标表的所有索引，并构建索引部分。最后，它将字段和索引部分合并为完整的创建表的SQL语句，并将其存储在一个输出参数中。下面是 “generate_create_table” 储存过程的代码：

CREATE DEFINER=`root`@`%` PROCEDURE `generate_create_table`(IN db_name VARCHAR(100), IN tbl_name VARCHAR(100),out create_sql VARCHAR(5000))
BEGIN
    -- 声明变量
    DECLARE col_name VARCHAR(100); -- 字段名称
    DECLARE col_type VARCHAR(100); -- 字段类型
    DECLARE col_index VARCHAR(500); -- 索引信息
    DECLARE col_nullable VARCHAR(50); -- 字段是否可为空
    DECLARE col_comment VARCHAR(255); -- 字段注释
		DECLARE col_default VARCHAR(50); -- 字段默认值
    DECLARE col_extra VARCHAR(50); -- 额外属性
    DECLARE done INT DEFAULT FALSE; -- 游标结束标志
    DECLARE create_table_sql TEXT DEFAULT ''; -- 存储创建表的SQL语句

    -- 使用游标遍历字段信息，构建字段部分
    DECLARE cur CURSOR FOR SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT, COLUMN_DEFAULT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = db_name AND TABLE_NAME = tbl_name;

-- 使用游标遍历索引信息，构建索引部分
DECLARE cur2 CURSOR FOR SELECT CONCAT( IF( NON_UNIQUE = 0, 'UNIQUE', IF( INDEX_TYPE LIKE 'FULLTEXT%', 'FULLTEXT KEY', 'KEY' ) ), ' `', INDEX_NAME, '` (', GROUP_CONCAT('`', COLUMN_NAME, '`'), ')', IF( INDEX_TYPE NOT LIKE 'FULLTEXT%', CONCAT(' USING ', INDEX_TYPE), '' ) ) AS INDEX_INFO FROM INFORMATION_SCHEMA.STATISTICS WHERE INDEX_NAME != 'PRIMARY' and TABLE_SCHEMA = db_name AND TABLE_NAME = tbl_name GROUP BY INDEX_NAME, INDEX_TYPE;

-- 设置游标结束处理程序
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 开始构建创建表的SQL语句(A完整创建SQL)
    SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS `', db_name, '`.`', tbl_name, '` (');

OPEN cur;

-- 检查并设置默认值和额外属性
read_loop: LOOP
             FETCH cur INTO col_name, col_type, col_nullable, col_comment, col_default, col_extra;
        IF done THEN
            LEAVE read_loop;
END IF;

        IF col_nullable = 'YES' THEN
            SET col_nullable = 'DEFAULT NULL';
ELSE
            SET col_nullable = 'NOT NULL';
END IF;

        IF col_comment IS NOT NULL and col_comment!=''  THEN
            SET col_comment = CONCAT(' COMMENT ', QUOTE(col_comment));
ELSE
            SET col_comment = '';
END IF;

        IF col_default IS NOT NULL THEN
						SET col_nullable = 'DEFAULT ';
            SET col_default = col_default;
ELSE
            SET col_default = '';
END IF;

				-- id 去掉自增
        IF col_extra IS NOT NULL and col_name != 'id'  THEN
            SET col_extra = CONCAT(col_extra, ' ');
ELSE
            SET col_extra = '';
END IF;
				-- 数据库编码可能导致col_comment字段异常，如果异常删除掉
				-- 包含字段注释
        SET create_table_sql = CONCAT(create_table_sql, '`', col_name, '` ', col_type, ' ', col_nullable, col_default, ' ', col_extra, col_comment, ',');
				-- 不包含字段注释
				-- SET create_table_sql = CONCAT(create_table_sql, '`', col_name, '` ', col_type, ' ', col_nullable, col_default, ' ', col_extra ,',');
END LOOP;
CLOSE cur;

SET create_table_sql = CONCAT(create_table_sql,'KEY `idx_id` (`id`) USING BTREE,');
OPEN cur2;
SET done = 0;

    read_loop2: LOOP
        FETCH cur2 INTO col_index;
        IF done THEN
            LEAVE read_loop2;
END IF;
        SET create_table_sql = CONCAT(create_table_sql,  col_index,',');
END LOOP;
CLOSE cur2;

-- 移除最后一个逗号和空格
SET create_table_sql = TRIM(TRAILING ',' FROM create_table_sql);

    SET create_table_sql = CONCAT(@sql, create_table_sql, ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');

    -- 返回创建表的SQL语句
    SET create_sql = create_table_sql;
END

3. 创建储存过程 “create_shard_tables”

这个储存过程用于根据生成的创建表的SQL语句来创建分片表。它使用一个循环来创建多个分片表，循环的次数由一个变量控制。每次循环，它会根据分片表的序号来生成一个新的创建表的SQL语句，并执行创建表操作。

下面是 “create_shard_tables” 储存过程的代码：

CREATE DEFINER=`root`@`%` PROCEDURE `create_shard_tables`(in tablename VARCHAR(50),in createsql VARCHAR(5000))
BEGIN
     DECLARE `@i` int(11);
     DECLARE `@createSql` VARCHAR(5000);

     set `@i`=1;
     WHILE  `@i`< 33 DO

         -- `M_ID` bigint AUTO_INCREMENT PRIMARY KEY NOT NULL,
         -- 创建表
         SET @createSql = CONCAT('CREATE TABLE IF NOT EXISTS ',tablename,`@i`,createsql);
         prepare stmt from @createSql;
         execute stmt;

         SET `@i`= `@i`+1;
     END WHILE;
END

使用这三个储存过程，我们可以轻松地创建分片表。只需调用 “creatingAFragmentTable” 储存过程，并传入目标数据库的名称，即可自动创建所有分片表。

上篇windows环境下mycat服务启动报错1067

下篇储存过程实现show create table 获取表的创建SQL