Xóa dữ liệu trùng trong mysql

Tôi có một bảng với các trường sau:

id (Unique)
url (Unique)
title
company
site_id

Bây giờ, tôi cần xóa các hàng có cùng title, company and site_id. Một cách để làm điều đó là sử dụng SQL sau cùng với tập lệnh (PHP):

SELECT title, site_id, location, id, count( * ) 
FROM jobs
GROUP BY site_id, company, title, location
HAVING count( * ) >1

Sau khi chạy truy vấn này, tôi có thể loại bỏ các bản sao bằng cách sử dụng tập lệnh phía máy chủ.

Nhưng, tôi muốn biết nếu điều này có thể được thực hiện chỉ bằng cách sử dụng truy vấn SQL.

Một cách thực sự dễ dàng để làm điều này là thêm chỉ mục UNIQUE trên 3 cột. Khi bạn viết câu lệnh ALTER, hãy bao gồm từ khóa IGNORE. Thích như vậy:

ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (site_id, title, company);

Điều này sẽ thả tất cả các hàng trùng lặp. Là một lợi ích bổ sung, tương lai INSERTs là các bản sao sẽ bị lỗi. Như mọi khi, bạn có thể muốn sao lưu trước khi chạy một cái gì đó như thế này ...

The most convenient and reliable file storage service

Receive your personal cloud storage with 2Gb of space for free

Nếu bạn không muốn thay đổi các thuộc tính cột, thì bạn có thể sử dụng truy vấn bên dưới.

Vì bạn có một cột có ID duy nhất (ví dụ: cột auto_increment), bạn có thể sử dụng cột đó để xóa các bản sao:

DELETE `a`
FROM
    `jobs` AS `a`,
    `jobs` AS `b`
WHERE
    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`ID` < `b`.`ID`

    -- Any duplicates you want to check for
    AND (`a`.`title` = `b`.`title` OR `a`.`title` IS NULL AND `b`.`title` IS NULL)
    AND (`a`.`company` = `b`.`company` OR `a`.`company` IS NULL AND `b`.`company` IS NULL)
    AND (`a`.`site_id` = `b`.`site_id` OR `a`.`site_id` IS NULL AND `b`.`site_id` IS NULL);

Trong MySQL, bạn có thể đơn giản hóa nó nhiều hơn với toán tử bằng NULL-safe (aka "toán tử tàu vũ trụ" ):

DELETE `a`
FROM
    `jobs` AS `a`,
    `jobs` AS `b`
WHERE
    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`ID` < `b`.`ID`

    -- Any duplicates you want to check for
    AND `a`.`title` <=> `b`.`title`
    AND `a`.`company` <=> `b`.`company`
    AND `a`.`site_id` <=> `b`.`site_id`;

MySQL có các hạn chế về việc tham khảo bảng bạn đang xóa. Bạn có thể làm việc xung quanh đó với một bảng tạm thời, như:

create temporary table tmpTable (id int);

insert  tmpTable
        (id)
select  id
from    YourTable yt
where   exists
        (
        select  *
        from    YourTabe yt2
        where   yt2.title = yt.title
                and yt2.company = yt.company
                and yt2.site_id = yt.site_id
                and yt2.id > yt.id
        );

delete  
from    YourTable
where   ID in (select id from tmpTable);

Từ đề xuất của Kostanos trong các bình luận:
Truy vấn chậm duy nhất ở trên là XÓA, đối với trường hợp bạn có cơ sở dữ liệu rất lớn. Truy vấn này có thể nhanh hơn: 

DELETE FROM YourTable USING YourTable, tmpTable WHERE YourTable.id=tmpTable.id

Nếu câu lệnh IGNORE không hoạt động như trong trường hợp của tôi, bạn có thể sử dụng câu lệnh sau:

CREATE TABLE your_table_deduped like your_table;
INSERT your_table_deduped SELECT * FROM your_table GROUP BY index1_id, index2_id;
RENAME TABLE your_table TO your_table_with_dupes;
RENAME TABLE your_table_deduped TO your_table;
#OPTIONAL
ALTER TABLE `your_table` ADD UNIQUE `unique_index` (`index1_id`, `index2_id`);
#OPTIONAL
DROP TABLE your_table_with_dupes;

Có một giải pháp khác:

DELETE t1 FROM my_table t1, my_table t2 WHERE t1.id < t2.id AND t1.my_field = t2.my_field AND t1.my_field_2 = t2.my_field_2 AND ...

Flexible, reliable and affordable cloud hosting

Sign up and get $50 bonus within 30-day!

Xóa các bản sao trên các bảng MySQL là một vấn đề phổ biến, đó là kết quả của một ràng buộc bị thiếu để tránh các bản sao đó trước khi xử lý. Nhưng vấn đề phổ biến này thường đi kèm với các nhu cầu cụ thể ... đòi hỏi phải có các phương pháp cụ thể. Cách tiếp cận nên khác nhau tùy thuộc vào, ví dụ, kích thước của dữ liệu, mục nhập trùng lặp nên được giữ (nói chung là đầu tiên hoặc cuối cùng), cho dù có giữ các chỉ mục hay chúng tôi muốn thực hiện bất kỳ bổ sung nào hành động trên dữ liệu trùng lặp.

Ngoài ra còn có một số đặc điểm cụ thể trên MySQL, chẳng hạn như không thể tham chiếu cùng một bảng với nguyên nhân TỪ khi thực hiện bảng CẬP NHẬT (nó sẽ gây ra lỗi MySQL # 1093). Hạn chế này có thể được khắc phục bằng cách sử dụng truy vấn bên trong với bảng tạm thời (như được đề xuất trên một số phương pháp ở trên). Nhưng truy vấn bên trong này sẽ không thực hiện đặc biệt tốt khi xử lý các nguồn dữ liệu lớn.

Tuy nhiên, một cách tiếp cận tốt hơn tồn tại để loại bỏ các bản sao, điều đó vừa hiệu quả vừa đáng tin cậy và có thể dễ dàng thích nghi với các nhu cầu khác nhau.

Ý tưởng chung là tạo một bảng tạm thời mới, thường thêm một ràng buộc duy nhất để tránh trùng lặp thêm và để XÁC NHẬN dữ liệu từ bảng cũ của bạn sang bảng mới, trong khi chăm sóc các bản sao. Cách tiếp cận này dựa trên các truy vấn MySQL INSERT đơn giản, tạo ra một ràng buộc mới để tránh trùng lặp thêm và bỏ qua nhu cầu sử dụng truy vấn bên trong để tìm kiếm các bản sao và bảng tạm thời nên được giữ trong bộ nhớ (do đó cũng phù hợp với các nguồn dữ liệu lớn).

Đây là cách nó có thể đạt được. Cho rằng chúng ta có một bảng staff, với các cột sau:

employee (id, first_name, last_name, start_date, ssn)

Để xóa các hàng có cột ssn trùng lặp và chỉ giữ lại mục nhập đầu tiên được tìm thấy, có thể thực hiện quy trình sau:

-- create a new tmp_eployee table
CREATE TABLE tmp_employee LIKE employee;

-- add a unique constraint
ALTER TABLE tmp_employee ADD UNIQUE(ssn);

-- scan over the employee table to insert employee entries
INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id;

-- rename tables
RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

Giải thích kỹ thuật

  • Dòng # 1 tạo bảng tmp_eployee mới với cấu trúc chính xác giống như bảng staff
  • Dòng # 2 thêm một ràng buộc ĐỘC ĐÁO vào bảng tmp_eployee mới để tránh mọi sự trùng lặp khác
  • Dòng số 3 quét qua bảng staff ban đầu theo id, chèn các mục nhập nhân viên mới vào bảng tmp_eployee mới, trong khi bỏ qua các mục trùng lặp
  • Dòng số 4 đổi tên các bảng, để bảng worker mới giữ tất cả các mục nhập mà không trùng lặp và một bản sao lưu dữ liệu cũ được giữ trên bảng backup_employee

⇒ Sử dụng phương pháp này, các thanh ghi 1.6M đã được chuyển đổi thành 6k trong vòng chưa đầy 200 giây.

Chetan , theo quy trình này, bạn có thể nhanh chóng và dễ dàng xóa tất cả các bản sao của mình và tạo một ràng buộc ĐỘC ĐÁO bằng cách chạy:

CREATE TABLE tmp_jobs LIKE jobs;

ALTER TABLE tmp_jobs ADD UNIQUE(site_id, title, company);

INSERT IGNORE INTO tmp_jobs SELECT * FROM jobs ORDER BY id;

RENAME TABLE jobs TO backup_jobs, tmp_jobs TO jobs;

Tất nhiên, quá trình này có thể được sửa đổi thêm để điều chỉnh nó cho các nhu cầu khác nhau khi xóa các bản sao. Một số ví dụ sau đây.

✔ Biến thể để giữ mục cuối cùng thay vì mục đầu tiên

Đôi khi chúng ta cần giữ mục trùng lặp cuối cùng thay vì mục đầu tiên.

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id DESC;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;
  • Trên dòng số 3, mệnh đề ORDER BY id DESC làm cho ID cuối cùng được ưu tiên hơn phần còn lại

✔ Biến thể để thực hiện một số tác vụ trên các bản sao, ví dụ: giữ số lượng trên các bản sao được tìm thấy

Đôi khi chúng ta cần thực hiện một số xử lý tiếp theo đối với các mục trùng lặp được tìm thấy (chẳng hạn như giữ một số lượng trùng lặp).

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0;

INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;
  • Trên dòng số 3, một cột mới n_d repeatates được tạo
  • Trên dòng số 4, truy vấn INSERT INTO ... ON DUPLICATE KEY UPDATE được sử dụng để thực hiện cập nhật bổ sung khi tìm thấy bản sao (trong trường hợp này, tăng bộ đếm) INSERT INTO. .. Có thể sử dụng truy vấn ON DUPLICATE KEY để thực hiện các loại cập nhật khác nhau cho các bản sao được tìm thấy. 

✔ Biến thể để tạo lại id trường tăng tự động

Đôi khi chúng tôi sử dụng trường tăng tự động và để giữ cho chỉ số càng nhỏ gọn càng tốt, chúng tôi có thể tận dụng việc xóa các bản sao để tạo lại trường tăng tự động trong bảng tạm thời mới.

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

INSERT IGNORE INTO tmp_employee SELECT (first_name, last_name, start_date, ssn) FROM employee ORDER BY id;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;
  • Trên dòng số 3, thay vì chọn tất cả các trường trên bảng, trường id được bỏ qua để công cụ DB tự động tạo một trường mới

✔ Các biến thể khác

Nhiều sửa đổi hơn nữa cũng có thể thực hiện được tùy thuộc vào hành vi mong muốn. Ví dụ: các truy vấn sau sẽ sử dụng bảng tạm thời thứ hai để, ngoài 1) giữ mục nhập cuối cùng thay vì mục đầu tiên; và 2) tăng bộ đếm trên các bản sao được tìm thấy; cũng 3) tạo lại id trường tăng tự động trong khi vẫn giữ thứ tự nhập như trên dữ liệu cũ.

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0;

INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id DESC ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1;

CREATE TABLE tmp_employee2 LIKE tmp_employee;

INSERT INTO tmp_employee2 SELECT (first_name, last_name, start_date, ssn) FROM tmp_employee ORDER BY id;

DROP TABLE tmp_employee;

RENAME TABLE employee TO backup_employee, tmp_employee2 TO employee;

Tôi có đoạn mã truy vấn này cho SQLServer nhưng tôi nghĩ nó có thể được sử dụng trong các DBMS khác với ít thay đổi:

DELETE
FROM Table
WHERE Table.idTable IN  (  
    SELECT MAX(idTable)
    FROM idTable
    GROUP BY field1, field2, field3
    HAVING COUNT(*) > 1)

Tôi quên nói với bạn rằng truy vấn này không xóa hàng có id thấp nhất trong các hàng trùng lặp. Nếu điều này làm việc cho bạn hãy thử truy vấn này: 

DELETE
FROM jobs
WHERE jobs.id IN  (  
    SELECT MAX(id)
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING COUNT(*) > 1)

Đơn giản và nhanh chóng cho mọi trường hợp:

CREATE TEMPORARY TABLE IF NOT EXISTS _temp_duplicates AS (SELECT dub.id FROM table_with_duplications dub GROUP BY dub.field_must_be_uniq_1, dub.field_must_be_uniq_2 HAVING COUNT(*)  > 1);

DELETE FROM table_with_duplications WHERE id IN (SELECT id FROM _temp_duplicates);

Cách nhanh hơn là chèn các hàng riêng biệt vào một bảng tạm thời. Sử dụng xóa, tôi mất vài giờ để xóa các bản sao khỏi bảng 8 triệu hàng. Sử dụng chèn và khác biệt, chỉ mất 13 phút. 

CREATE TABLE tempTableName LIKE tableName;  
CREATE INDEX ix_all_id ON tableName(cellId,attributeId,entityRowId,value);  
INSERT INTO tempTableName(cellId,attributeId,entityRowId,value) SELECT DISTINCT cellId,attributeId,entityRowId,value FROM tableName;  
TRUNCATE TABLE tableName;
INSERT INTO tableName SELECT * FROM tempTableName; 
DROP TABLE tempTableName;  

Tôi tiếp tục truy cập trang này bất cứ khi nào tôi google "loại bỏ trùng lặp mẫu mysql" nhưng đối với các giải pháp của chúng tôi không hoạt động vì tôi có bảng mysql của InnoDB

mã này hoạt động tốt hơn bất cứ lúc nào

CREATE TABLE tableToclean_temp LIKE tableToclean;
ALTER TABLE tableToclean_temp ADD UNIQUE INDEX (fontsinuse_id);
INSERT IGNORE INTO tableToclean_temp SELECT * FROM tableToclean;
DROP TABLE tableToclean;
RENAME TABLE tableToclean_temp TO tableToclean;

tableToclean = tên của bảng bạn cần xóa

tableToclean_temp = một bảng tạm thời được tạo và xóa

The most convenient and reliable file storage service

Receive your personal cloud storage with 2Gb of space for free

Một giải pháp đơn giản để hiểu và hoạt động không có khóa chính: 

1) thêm một cột boolean mới

alter table mytable add tokeep boolean;

2) thêm một ràng buộc trên các cột trùng lặp VÀ cột mới

alter table mytable add constraint preventdupe unique (mycol1, mycol2, tokeep);

3) đặt cột boolean thành true. Điều này sẽ chỉ thành công trên một trong các hàng trùng lặp vì ràng buộc mới

update ignore mytable set tokeep = true;

4) xóa các hàng chưa được đánh dấu là tokeep

delete from mytable where tokeep is null;

5) thả cột đã thêm

alter table mytable drop tokeep;

Tôi đề nghị bạn nên giữ các ràng buộc mà bạn đã thêm, để ngăn chặn các bản sao mới trong tương lai.

Giải pháp này sẽ di chuyển các bản sao vào một bảng uniqu vào bảng khác .

-- speed up creating uniques table if dealing with many rows
CREATE INDEX temp_idx ON jobs(site_id, company, title, location);

-- create the table with unique rows
INSERT jobs_uniques SELECT * FROM
    (
    SELECT * 
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count(1) > 1
    UNION
    SELECT *
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count(1) = 1
) x

-- create the table with duplicate rows
INSERT jobs_dupes 
SELECT * 
FROM jobs
WHERE id NOT IN
(SELECT id FROM jobs_uniques)

-- confirm the difference between uniques and dupes tables
SELECT COUNT(1)
AS jobs, 
(SELECT COUNT(1) FROM jobs_dupes) + (SELECT COUNT(1) FROM jobs_uniques)
AS sum
FROM jobs

nếu bạn có một bảng lớn với số lượng hồ sơ khổng lồ thì các giải pháp trên sẽ không hoạt động hoặc mất quá nhiều thời gian. Sau đó, chúng tôi có một giải pháp khác nhau 

-- Create temporary table

CREATE TABLE temp_table LIKE table1;

-- Add constraint
ALTER TABLE temp_table ADD UNIQUE(title, company,site_id);

-- Copy data
INSERT IGNORE INTO temp_table SELECT * FROM table1;

-- Rename and drop
RENAME TABLE table1 TO old_table1, temp_table TO table1;
DROP TABLE old_table1;

Xóa các hàng trùng lặp bằng cách sử dụng câu lệnh XÓA THAM GIA.

Câu lệnh sau sẽ xóa các hàng trùng lặp và giữ id cao nhất:

DELETE t1 FROM contacts t1
    INNER JOIN
contacts t2 WHERE
t1.id < t2.id AND t1.email = t2.email;

Tôi tìm thấy một cách đơn giản. (giữ mới nhất)

DELETE t1 FROM tablename t1 INNER JOIN tablename t2 
WHERE t1.id < t2.id AND t1.column1 = t2.column1 AND t1.column2 = t2.column2;

Flexible, reliable and affordable cloud hosting

Sign up and get $50 bonus within 30-day!

Kể từ phiên bản 8.0 (2018), cuối cùng MySQL cũng hỗ trợ các chức năng window . 

Các chức năng của cửa sổ đều tiện dụng và hiệu quả. Dưới đây là một giải pháp cho thấy cách sử dụng chúng để giải quyết nhiệm vụ này.

Trong truy vấn con, chúng ta có thể sử dụng ROW_NUMBER() để gán một vị trí cho mỗi bản ghi trong bảng trong các nhóm column1/column2, được sắp xếp theo id. Nếu không có trùng lặp, bản ghi sẽ nhận được số hàng 1. Nếu trùng lặp tồn tại, chúng sẽ được đánh số bằng cách tăng dần id (bắt đầu từ 1).

Khi các bản ghi được đánh số đúng trong truy vấn con, truy vấn bên ngoài sẽ xóa tất cả các bản ghi có số hàng không phải là 1.

Truy vấn :

DELETE FROM tablename
WHERE id IN (
    SELECT id
    FROM (
        SELECT 
            id, 
            ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id) rn
        FROM output
    ) t
    WHERE rn > 1
)

Để sao chép các bản ghi với các cột duy nhất, ví dụ: Không nên sao chép COL1, COL2, COL3 (giả sử chúng ta đã bỏ lỡ 3 cột duy nhất trong cấu trúc bảng và nhiều mục trùng lặp đã được thực hiện trong bảng)

DROP TABLE TABLE_NAME_copy;
CREATE TABLE TABLE_NAME_copy LIKE TABLE_NAME;
INSERT INTO TABLE_NAME_copy
SELECT * FROM TABLE_NAME
GROUP BY COLUMN1, COLUMN2, COLUMN3; 
DROP TABLE TABLE_NAME;
ALTER TABLE TABLE_NAME_copy RENAME TO TABLE_NAME;

Hy vọng sẽ giúp dev.

TL; TR;

Một hướng dẫn được mô tả rất nhiều để giải quyết vấn đề này có thể được tìm thấy tại mysqltutorial.orgsite:

Cách xóa các hàng trùng lặp trong MySQL

Nó được hiển thị rất rõ cách xóa các hàng trùng lặp theo ba cách khác nhau:

A)Sử dụng câu lệnh DELETE JOIN

B)Sử dụng bảng trung gian

C)Sử dụng chức năng ROW_NUMBER()

Tôi hy vọng rằng nó sẽ giúp được ai đó.

Để xóa bản ghi trùng lặp trong một bảng.

delete from job s 
where rowid < any 
(select rowid from job k 
where s.site_id = k.site_id and 
s.title = k.title and 
s.company = k.company);

hoặc là

delete from job s 
where rowid not in 
(select max(rowid) from job k 
where s.site_id = k.site_id and
s.title = k.title and 
s.company = k.company);

-- Here is what I used, and it works:
create table temp_table like my_table;
-- t_id is my unique column
insert into temp_table (id) select id from my_table GROUP by t_id;
delete from my_table where id not in (select id from temp_table);
drop table temp_table;