記錄一次mysql批量修改大量資料

需求：資料庫欄位point儲存經緯度：116,39，逗號隔開，現在要拆分開  分別存入lon  lat

 

方式1：使用java 程式碼 

全部讀入記憶體-----split(",")-------setLon  setLat    ---------updateById   

經過測試，11W資料，耗時3小時以上

 

方式2：使用mysql儲存過程

-- 建立儲存過程之前需判斷該儲存過程是否已存在，若存在則刪除
DROP PROCEDURE IF EXISTS init_reportUrl; 
-- 建立儲存過程
CREATE PROCEDURE init_reportUrl()
BEGIN
    -- 定義變數
    DECLARE s int DEFAULT 0;
    DECLARE temp_id int(10);    -- 這個變數是用來放遊標裡的變數資料的
    DECLARE temp_point varchar(256); -- 這個變數是用來放遊標裡的變數資料的
    DECLARE a1 VARCHAR(122);
    DECLARE a2 VARCHAR(122);
    -- 定義遊標，並將sql結果集賦值到遊標中
    DECLARE report CURSOR FOR select id,point  from good_food; -- 定義遊標去拿哪些欄位資料
    -- 宣告當遊標遍歷完後將標誌變數置成某個值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
    -- 開啟遊標
    open report;
        -- 將遊標中的值賦值給變數，注意：變數名不要和返回的列名同名，變數順序要和sql結果列的順序一致
        fetch report into temp_id,temp_point;  -- 遊標拿到一條，給上面宣告的變數賦值
        -- 當s不等於1，也就是未遍歷完時，會一直迴圈
        while s<>1 do
            -- 執行業務邏輯
            SELECT SUBSTRING_INDEX(temp_point,',',1) into a1; -- 將函式結果 寫入a1  
            SELECT SUBSTRING_INDEX(temp_point,',',-1) into a2;-- 將函式結果 寫入 a2
            update good_food set lon = a1,lat = a2 where id=temp_id; --  修改lon=a1  lat =a2  條件是id=temp_id ,temp_id是遊標當前拿到的那一條資料的id 
            -- 將遊標中的值再賦值給變數，供下次迴圈使用    
            fetch report into temp_id,temp_point;
        -- 當s等於1時表明遍歷以完成，退出迴圈
        end while;
    -- 關閉遊標
    close report;
END;

-- 呼叫儲存過程  不呼叫不會執行
call init_reportUrl()

 

經過測試 ：12W資料，修改耗時30s

 

方式3：使用老式jdbc  ,通過遊標獲取next  的方式 

還沒測試

 

 

總結：遊標為最終使用方式   快捷很多