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