今天有个新的需求，需求内容是将系统中部分用户所产生的数据提取出来。
得到需求后进行了一下头脑风暴，得出了解决方案。
首先要使用的技术是mysql存储过程，因为这是提取数据最快的解决方案。
然后就是以用户表为数据源，先将所需提取的用户uid等信息提取到单独的user_mapping表中。
然后就是遍历数据库中的所有表，然后再遍历每个表中的所有字段，如果包含uid则通过uid过滤结果。否则就将当前表的所有数据提取出来。
（当然中间还可以加入一些其它的过滤条件）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 |
-- ---------------------------- -- Procedure structure for checkCloum -- ---------------------------- DROP PROCEDURE IF EXISTS `checkCloum`; DELIMITER ;; CREATE PROCEDURE `checkCloum`(IN `TableSchema` varchar(50),IN `TableName` varchar(50)) BEGIN DECLARE Done INT DEFAULT 0; DECLARE Col VARCHAR(50); DECLARE Str VARCHAR(255); /* 声明游标 */ DECLARE rs CURSOR FOR SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_schema = TableSchema AND table_name=TableName AND COLUMN_NAME IN ('uid', 'schoolid', 'classid','cuid', 'puid'); /* 异常处理 */ DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; /* 打开游标 */ OPEN rs; /* 是否跳过滤表 */ IF (TableName='message_question') THEN SELECT CONCAT('Break Create Table:', TableName); ELSE /* 复制表结构 */ SET @Str = CONCAT('DROP TABLE IF EXISTS ', TableName); PREPARE stmt FROM @Str; EXECUTE stmt; SET @Str = CONCAT('CREATE TABLE IF NOT EXISTS ', TableName, ' LIKE ', TableSchema, '.', TableName); PREPARE stmt FROM @Str; EXECUTE stmt; FETCH NEXT FROM rs INTO Col; REPEAT IF NOT Done THEN IF (Col='classid') THEN SET @Str = CONCAT('REPLACE INTO ', TableName, ' SELECT * FROM ', TableSchema, '.', TableName, ' WHERE ', Col, ' IN(SELECT id FROM ` classinfo`)'); ELSE SET @Str = CONCAT('REPLACE INTO ', TableName, ' SELECT * FROM ', TableSchema, '.', TableName, ' WHERE ', Col, ' IN(SELECT ', Col, ' FROM `user_mapping`)'); END IF; ELSE /* 单独处理 schoolinfo */ IF (TableName=' schoolinfo') THEN SET @Str = CONCAT('REPLACE INTO ', TableName, ' SELECT * FROM ', TableSchema, '.', TableName, ' WHERE id IN(SELECT schoolid FROM `user_mapping`)'); /* 过滤习题选项 */ ELSEIF (TableName='exercises_option') THEN SET @Str = CONCAT('REPLACE INTO ', TableName, ' SELECT * FROM ', TableSchema, '.', TableName, ' WHERE xtid IN(SELECT id FROM ', TableSchema, '.te_exercises', ' WHERE uid IN (SELECT uid FROM `user_mapping`))'); ELSE SET @Str = CONCAT('REPLACE INTO ', TableName, ' SELECT * FROM ', TableSchema, '.', TableName); END IF; END IF; PREPARE stmt FROM @str; EXECUTE stmt; FETCH NEXT FROM rs INTO Col; UNTIL Done END REPEAT; END IF; /* 关闭游标 */ CLOSE rs; END ;; DELIMITER ; -- ---------------------------- -- Procedure structure for sync_xue -- ---------------------------- DROP PROCEDURE IF EXISTS `sync_xue`; DELIMITER ;; CREATE PROCEDURE `sync_xue`() BEGIN DECLARE Done INT DEFAULT 0; DECLARE TableSchema VARCHAR(50); DECLARE TableName VARCHAR(50); DECLARE Str VARCHAR(255); /* 声明游标 */ DECLARE rs CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'source1_db' AND TABLE_NAME NOT IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'source1_db' AND ( LOCATE('_log', TABLE_NAME) OR LOCATE('_copy', TABLE_NAME) OR LOCATE('_bak', TABLE_NAME) ) ) AND LOCATE('te_', TABLE_NAME); /* 异常处理 */ DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; /* 打开游标 */ OPEN rs; /* 逐个取出当前记录TABLE_NAME字段的值 */ FETCH NEXT FROM rs INTO TableName; /* 遍历数据表 */ REPEAT IF NOT Done THEN IF (TableName='te_area'||TableName='card_answer') THEN SELECT CONCAT('Break Table:', TableName); ELSE CALL checkCloum('source1_db', TableName); SELECT TableName; END IF; END IF; FETCH NEXT FROM rs INTO TableName; UNTIL Done END REPEAT; /* 关闭游标 */ CLOSE rs; END ;; DELIMITER ; -- ---------------------------- -- Procedure structure for sync_user -- ---------------------------- DROP PROCEDURE IF EXISTS `sync_user`; DELIMITER ;; CREATE PROCEDURE `sync_user`() BEGIN DECLARE Done INT DEFAULT 0; DECLARE TableSchema VARCHAR(50) DEFAULT 'source2_db'; DECLARE TableName VARCHAR(50); DECLARE Str VARCHAR(255); /* 声明游标 */ DECLARE rs CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'source2_db' AND TABLE_NAME NOT IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'source2_db' AND ( LOCATE('_log', TABLE_NAME) OR LOCATE('_copy', TABLE_NAME) ) ); /* 异常处理 */ DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; /* 创建用户映射表 */ SET @Str = 'DROP TABLE IF EXISTS `user_mapping`'; PREPARE stmt FROM @Str; EXECUTE stmt; SET @Str = 'CREATE TABLE `user_mapping` (`uid` bigint(20) NOT NULL,`schoolid` bigint(20) DEFAULT NULL,`cuid` bigint(20) DEFAULT NULL,`puid` bigint(20) DEFAULT NULL,PRIMARY KEY (`uid`),KEY `schoolid` (`schoolid`) USING BTREE) ENGINE=MyISAM DEFAULT CHARSET=utf8'; PREPARE stmt FROM @Str; EXECUTE stmt; /* 将处理用户数据加入到当前表中 */ SET @Str = 'REPLACE INTO `user_mapping` SELECT u.uid,IF(s.schoolid, s.schoolid, -1) as schoolid,u.uid as cuid,u.uid as puid FROM source2_db. user u LEFT JOIN source2_db. user_school s ON u.uid = s.uid WHERE u.sys_code = 1'; PREPARE stmt FROM @Str; EXECUTE stmt; /* 处理classinfo表 */ /* 复制表结构 */ SET @Str = 'DROP TABLE IF EXISTS ` classinfo`'; PREPARE stmt FROM @Str; EXECUTE stmt; SET @Str = CONCAT('CREATE TABLE IF NOT EXISTS ` classinfo` LIKE ', TableSchema, '. classinfo'); PREPARE stmt FROM @Str; EXECUTE stmt; SET @Str = CONCAT('REPLACE INTO ` classinfo` SELECT * FROM ', TableSchema, '. classinfo WHERE schoolid IN(SELECT schoolid FROM `user_mapping`)'); PREPARE stmt FROM @Str; EXECUTE stmt; /* 打开游标 */ OPEN rs; /* 逐个取出当前记录TABLE_NAME字段的值 */ FETCH NEXT FROM rs INTO TableName; /* 遍历数据表 */ REPEAT IF NOT Done THEN IF (TableName=' classinfo') THEN SELECT CONCAT('Break Table:', TableName); ELSE CALL checkCloum('source2_db', TableName); SELECT TableName; END IF; END IF; FETCH NEXT FROM rs INTO TableName; UNTIL Done END REPEAT; /* 关闭游标 */ CLOSE rs; CALL sync_xue(); END ;; DELIMITER ; |
