mysql批量创建分表(0-99) ,使用存储过程
1.创建存储过程
CREATE PROCEDURE `createTablesWithIndex`()
BEGIN
DECLARE `@i` INT(11);
DECLARE `@createSql` VARCHAR(2560);
DECLARE `@createIndexSql1` VARCHAR(2560);
DECLARE `@createIndexSql2` VARCHAR(2560);
DECLARE `@createIndexSql3` VARCHAR(2560);
DECLARE `@j` VARCHAR(10);
SET `@i`=0;
WHILE `@i`< 100 DO
-- 如果需要00-99则加上下面的IF判断
-- IF `@i` < 10 THEN
-- SET `@j` = CONCAT(0,`@i`);
-- ELSE
SET `@j` = `@i`;
-- END IF;
-- `M_ID` bigint AUTO_INCREMENT PRIMARY KEY NOT NULL,
-- 创建表
SET @createSql = CONCAT('CREATE TABLE IF NOT EXISTS friend_',`@j`,"(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '记录 id',
`tid` bigint(20) unsigned NOT NULL COMMENT 'talk id',
`ftid` bigint(20) unsigned NOT NULL COMMENT '关系方的 talk id',
`status` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '关系状态: 0 好友申请 1 是好友 2 黑名单, 3 被拒绝了 ache 中用 bitmap 表示',
`bitmap` bigint(32) unsigned NOT NULL DEFAULT '0' COMMENT '方便扩展的 bitmap 位, cache 的落地存储, 冗余 status 字段',
`intimacy` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '申请好友的时候为兴趣度, 是好友的话为亲密度',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建记录时间',
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `tid_ftid` (`tid`,`ftid`),
KEY `tid` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='好友表(好友申请表)'"
);
PREPARE stmt FROM @createSql;
EXECUTE stmt;
SET `@i`= `@i`+1;
END WHILE;
END
2.调用存储过程
CALL createTablesWithIndex ()
3.批量创建成功
4.删除存储过程
DROP PROCEDURE `createTablesWithIndex`
5.如果添加错了如何批量删除
5.1 获取所有已添加的表
因为删除表没有 drop table like “friend_%”;这样的命令,所以要自己组拼接一下。
SHOW TABLES LIKE "friend_%";
5.2 批量处理
获取所有已添加的表,然后使用notepad++等编辑工具批量替换成如下格式:
DROP TABLE
friend_00,friend_01,friend_02,friend_03,friend_04,friend_05,friend_06,friend_07,friend_08,friend_09,
friend_10,friend_11,friend_12,friend_13,friend_14,friend_15,friend_16,friend_17,friend_18,friend_19,
friend_20,friend_21,friend_22,friend_23,friend_24,friend_25,friend_26,friend_27,friend_28,friend_29,
friend_30,friend_31,friend_32,friend_33,friend_34,friend_35,friend_36,friend_37,friend_38,friend_39,
friend_40,friend_41,friend_42,friend_43,friend_44,friend_45,friend_46,friend_47,friend_48,friend_49,
friend_50,friend_51,friend_52,friend_53,friend_54,friend_55,friend_56,friend_57,friend_58,friend_59,
friend_60,friend_61,friend_62,friend_63,friend_64,friend_65,friend_66,friend_67,friend_68,friend_69,
friend_70,friend_71,friend_72,friend_73,friend_74,friend_75,friend_76,friend_77,friend_78,friend_79,
friend_80,friend_81,friend_82,friend_83,friend_84,friend_85,friend_86,friend_87,friend_88,friend_89,
friend_90,friend_91,friend_92,friend_93,friend_94,friend_95,friend_96,friend_97,friend_98,friend_99