next up previous contents
Next: MySQL 匯入更新 Up: 匯入優化 Previous: XML 資料讀取優化   Contents

MySQL 匯入判斷

  1. 因選課資料匯入時間長,因各種原因中途中斷後重新匯入的可能性很高。重新再匯入必定會有部分資料已匯入,若不做判斷,直接再匯入,就會有重複資料產生。因此,必須於匯入時判斷,若該筆資料已存在就不寫入。最簡單的方法是以 INSERT IGNORE INTO 資料限制只能一筆,不能重複。但此方法若其中某一欄位的值不同,就不能視為相同。例如:時間變數 timecreated, timemodified 不一樣,無法視為相同,故還是會新增一筆。
    /usr/bin/mysql -uroot -prootpw << EOF
    use moodle23;
    set @tem1=(select id from mdl_enrol where courseid="$id" and enrol="manual");
    set @tem2=(select id from mdl_user where username="$username");
    /*select @tem1;select @tem2;*/
    insert INGORE into mdl_user_enrolments (enrolid,userid,timestart,timeend,modifierid,timecreated,timemodified)
    values (@tem1,@tem2,"$timestart","","2","$timecreated","$timemodified");
    
    set @cont=(select id from mdl_context where instanceid="$id" and contextlevel='50');
    insert INGORE into mdl_role_assignments (roleid,contextid,userid,timemodified,modifierid)
    values ('5',@cont,@tem2,"$timemodified","2");
    EOF
    
  2. 使用 MySQL 判斷資料表是否存在且限制只能一筆,判斷兩種狀況:
    1. 變數不是空的。
      WHERE @tem1 IS NOT NULL AND @tem2 IS NOT NULL;
      
    2. 資料限制只能一筆,不能重複。
      WHERE NOT EXISTS (SELECT id FROM mdl_user_enrolments WHERE enrolid=@tem1 and userid=@tem2) LIMIT 1;
      
  3. 以腳本寫入資料庫。
    /usr/bin/mysql -uroot -prootpw << EOF
    use moodle23;
    set @tem1=(select id from mdl_enrol where courseid="$id" and enrol="manual");
    set @tem2=(select id from mdl_user where username="$username");
    /*select @tem1;select @tem2;*/
    insert into mdl_user_enrolments (enrolid,userid,timestart,timeend,modifierid,timecreated,timemodified)
    select @tem1,@tem2,"$timestart","","2","$timecreated","$timemodified" from DUAL
    WHERE @tem1 IS NOT NULL AND @tem2 IS NOT NULL AND NOT EXISTS 
    (SELECT id FROM mdl_user_enrolments WHERE enrolid=@tem1 and userid=@tem2) LIMIT 1;
    
    set @cont=(select id from mdl_context where instanceid="$id" and contextlevel='50');
    insert into mdl_role_assignments (roleid,contextid,userid,timemodified,modifierid)
    select '5',@cont,@tem2,"$timemodified","2" FROM DUAL
    WHERE @cont IS NOT NULL AND @tem2 IS NOT NULL AND NOT EXISTS 
    (SELECT id FROM mdl_role_assignments WHERE contextid=@cont and userid=@tem2) LIMIT 1;
    EOF
    



2018-08-14