mysql-列合併成行方式

markzy5201190發表於2013-06-25
MySql
背景:近來總有玩家反饋少領取獎勵的情形，所有待發獎勵的玩家被彙總到某張的表的欄位中,通過查詢歷史資料,發現在
insert到表時，資料有可能被擷取啦，mysql沒有設定sql_mode,沒有錯誤或警告日誌可以查詢,同時,存放資料欄位型別text,不
管怎樣，也許是資料太長導致,故開發同學，要求DB段寫個sp，按照每行1w資料行，分多行存放玩家資料，以免後續再
次可能發生。

表A:
Role_Id
 11111
 2222
 3333
 4444
 5555
 ......
 .....
表B:.
轉換成：
id
1  1111,2222,3333....
2  444,55555,.....


如下通過遊標方式實現轉換過程:
CREATE TABLE B (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水Id',
  `Role_Id` VARCHAR(8000),
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=xxx';


DROP PROCEDURE IF EXISTS sp_test_rows;
CREATE PROCEDURE ` sp_test_rows`()
BEGIN
    DECLARE i INT;  #一行多少字元關聯
    DECLARE vRoleId INT; #臨時存放遊標中roleid
    DECLARE tRoleId VARCHAR(8000);#臨時存放insert到中間表中的字串
    DECLARE done INT;
    DECLARE cur CURSOR FOR SELECT Role_Id FROM A; #遊標遍歷資料
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    SET i=0;

    OPEN cur;
    label_loop:LOOP
    SET i=i+1;
      FETCH cur INTO vRoleId;
        IF done THEN
            LEAVE label_loop;
        END IF;
        SET tRoleId=CONCAT_WS(',',tRoleId,vRoleId);
       
        IF MOD(i,1000)=0 THEN   #一行中有1k字元儲存表中;
            INSERT INTO B(Role_Id) SELECT tRoleId;   #開始存放轉換資料行;
            SET tRoleId=NULL;
         END IF;
 END LOOP label_loop;
     CLOSE cur;
      IF tRoleId IS NULL then
          select '';
      ELSE
           INSERT INTO B(Role_Id) SELECT tRoleId;  #存放超過1k之後，資料行
      END IF;
END;

call sp_test_rows();

方法有些囉嗦，若有其他方法，還請分享下,^_^

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26855487/viewspace-764765/，如需轉載，請註明出處，否則將追究法律責任。

相關文章