Nightmare的胡言亂語

資料來源：MySQL: Remove duplicate entries

Remove duplicate entries. Assume the following table and data.

CREATE TABLE IF NOT EXISTS dupTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)

);

insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
(1,5,4),(1,6,4);



mysql> select * from dupTest;
select * from dupTest;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 2 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 3 | 1 | 5 | 4 | 2004-04-16 10:55:35 |
| 4 | 1 | 6 | 4 | 2004-04-16 10:55:35 |
+------+------+------+------+---------------------+
4 rows in set (0.00 sec)

mysql>

Note, the first two rows contains duplicates in columns a and b. It contains
other duplicates; but, leaves the other duplicates alone.

mysql> ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);

mysql> select * from dupTest;
select * from dupTest;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 11:11:42 |
| 3 | 1 | 5 | 4 | 2004-04-16 11:11:42 |
| 4 | 1 | 6 | 4 | 2004-04-16 11:11:42 |
+------+------+------+------+---------------------+
3 rows in set (0.00 sec)

簡單來說就是 ALTER IGNORE TABLE [資料表名稱] ADD UNIQUE INDEX([欄位1],[欄位2]);，把資料表名稱跟幾個你想要拿來做Index的欄位名稱放在INDEX()裡面用逗號隔開即可，要注意的是資料型別為TEXT的欄位不能作為Index，另外我寫了一支比對檔案裡面有沒有重複資料行的php在檔案放進資料庫前先做檢查，算是用php做檔案的DISTINCT吧，處理CSV跟一些log檔案時可能會有用，隨便寫寫的，應該會有效率更好的寫法。

<?
//如果有POST檔案進來再做處理
if($_FILES['uploadedfile']['tmp_name']){
    $log_content=file_get_contents($_FILES['uploadedfile']['tmp_name']);
    //依照斷行分開
    $lines=split("\n",$log_content);
    //$match_arr是用來存放已經有的資料，未來有資料和裡面任一筆Record一樣就不處理
    $match_arr[0]=0;
    $arr_count=0;
    foreach($lines as $tmp){
        //檢查每行是否都有值且資料不重複才進行處理
        if($tmp&&!in_array($tmp,$match_arr)){
            //如果是新的資料，把新資料加進$match_arr，避免之後重複出現
            $match_arr[$arr_count]=$tmp;
            echo $match_arr[$arr_count]."<br>";
            $arr_count++;
        }
    }
}
?>
<form enctype="multipart/form-data" action="" method="POST">
Choose a file to upload: <input name="uploadedfile" type="file" /><br>
<input type="submit" value="Upload File" />
</form>