- 논리적 샤딩으로 한 인스턴스내 동일한 스키마를 가진 데이터베이스에 동일한 테이블에 대한 데이터 조회
- 프로시저로 생성하여 일괄적인 처리
- DB방 팬돌이님 제공해주신 소스에 약간 수정
CREATE PROCEDURE `test`.`sp_multi_select`()
BEGIN
DECLARE V_SchemaList TEXT;
DECLARE V_Schema TEXT;
DECLARE V_TableName TEXT;
DECLARE V_SQL TEXT;
SET SESSION group_concat_max_len = 10000;
SELECT GROUP_CONCAT(SCHEMA_NAME) INTO V_SchemaList FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN('information_schema', 'mysql', 'performance_schema', 'test');
SELECT CONCAT(V_SchemaList, ',') INTO V_SchemaList;
DROP TABLE IF EXISTS tmp_result;
CREATE TABLE tmp_result (`id` char(8) NOT NULL, `domain` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`));
SELECT 'tbl_home' INTO V_TableName;
WHILE (LOCATE(',', V_SchemaList) > 0)
DO
SET V_Schema = LEFT(V_SchemaList, LOCATE(',',V_SchemaList) - 1);
SET V_SchemaList = SUBSTRING(V_SchemaList, LOCATE(',', V_SchemaList) + 1);
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = V_TableName AND TABLE_SCHEMA = V_Schema ) THEN
SET @V_SQL = CONCAT('INSERT INTO tmp_result SELECT id, domain FROM ', V_Schema, '.', V_TableName);
PREPARE STMT FROM @V_SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END WHILE;
END
- 팬돌이님 부연 설명:
procedure 내에서 loop를 돌면서 처리하는 꼼수들 다들 있으실 텐데 .. 저는 , 로 연결된 거 loop마다 substring으로 덜어가면서 했었는데 그렇게 이해하시면 되고 (마지막에 ,가 있어야 끝까지 돌기 때문에 한번더 concat으로 마지막에 붙여주는 식의 처리를 했고요..)
SQL statement를 concat으로 조합해서 만들고, 그걸 prepare, execute 처리 하는 방식은 이전에 다른 분이 언급하신 내용에 대한 예이고요..
execute하는 것에 대해서 select statement만 돌아가는 것은 결과를 볼 수 없으니 그걸 담을 table을 미리 복사해서 만드는 식으로 해야할 것 같아,, CREATE TABLE tbl_result LIKE h001.tbl_A; 이렇게 하고 여기에 schema별로 돌면서 insert하는 식으로 했습니다. 이건 select 하려는 값의 column이 복수인 경우에 이와 비슷하게 처리 하셔야 할 것 같아서.. 그렇게 해본 것입니다..
CREATE PROCEDURE test.sp_multi_count()
BEGIN
DECLARE V_SchemaList TEXT;
DECLARE V_Schema TEXT;
DECLARE V_TableName TEXT;
DECLARE V_SQL TEXT;
SET SESSION group_concat_max_len = 10000;
SELECT GROUP_CONCAT(SCHEMA_NAME) INTO V_SchemaList FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN('information_schema', 'mysql', 'performance_schema', 'sys','test');
SELECT CONCAT(V_SchemaList, ',') INTO V_SchemaList;
DROP TABLE IF EXISTS tmp_count;
CREATE TABLE tmp_count (schema_name char(8) null, count INT UNSIGNED NULL);
SELECT 'tbl_home' INTO V_TableName;
WHILE (LOCATE(',', V_SchemaList) > 0)
DO
SET V_Schema = LEFT(V_SchemaList, LOCATE(',',V_SchemaList) - 1);
SET V_SchemaList = SUBSTRING(V_SchemaList, LOCATE(',', V_SchemaList) + 1);
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = V_TableName AND TABLE_SCHEMA = V_Schema ) THEN
SET @V_SQL = CONCAT('INSERT INTO tmp_count (count,schema_name) SELECT count(id),\'',V_Schema, '\' FROM ', V_Schema, '.', V_TableName);
PREPARE STMT2 FROM @V_SQL;
EXECUTE STMT2;
DEALLOCATE PREPARE STMT2;
END IF;
END WHILE;
END
- gruop_concat의 경우 기본 length가 1024 이기 때문에 더 긴 문장을 담으려면 다음과 같은 구문을 추가
SET SESSION group_concat_max_len = 10000;