أزل الصفوف المكررة في MySQL




duplicates (10)

لدي جدول يحتوي على الحقول التالية:

id (Unique)
url (Unique)
title
company
site_id

الآن ، أحتاج إلى إزالة صفوف لها نفس title, company and site_id . إحدى الطرق للقيام بذلك سيتم استخدام SQL التالي مع برنامج نصي ( PHP ):

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

بعد تشغيل هذا الاستعلام ، يمكنني إزالة التكرارات باستخدام نص برمجي من جانب الخادم.

ولكن ، أريد أن أعرف ما إذا كان يمكن القيام بذلك فقط باستخدام استعلام SQL.


أحب أن أكون أكثر تحديدًا فيما يتعلق بالسجلات التي أحذفها حتى هنا هي الحل الخاص بي:

delete
from jobs c1
where not c1.location = 'Paris'
and  c1.site_id > 64218
and exists 
(  
select * from jobs c2 
where c2.site_id = c1.site_id
and   c2.company = c1.company
and   c2.location = c1.location
and   c2.title = c1.title
and   c2.site_id > 63412
and   c2.site_id < 64219
)

أستمر في زيارة هذه الصفحة في أي وقت أنا google "إزالة مكررة شكل mysql" ولكن بالنسبة لي لا تعمل حلول myIGNORE لأن لدي جداول mysql InnoDB

هذا الرمز يعمل بشكل أفضل في أي وقت

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 = اسم الجدول الذي تحتاج لتنظيفه

tableToclean_temp = جدول مؤقت تم إنشاؤه وحذفه


إذا كان بيان IGNORE لن يعمل كما هو الحال في حالتي ، فيمكنك استخدام:

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;

إذا كنت لا ترغب في تغيير خصائص العمود ، فيمكنك استخدام الاستعلام أدناه.

نظرًا لأن لديك عمودًا به معرّفات فريدة (على سبيل المثال ، أعمدة auto_increment ) ، يمكنك استخدامه لإزالة التكرارات:

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);

في MySQL ، يمكنك تبسيطها بشكل أكبر مع مشغل NULL-safe equal (الذي يعرف أيضًا باسم "مشغّل سفينة الفضاء" ):

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`;

حل سهل الفهم ويعمل بدون مفتاح أساسي:

1) إضافة عمود منطقي جديد

alter table mytable add tokeep boolean;

2) إضافة قيد على الأعمدة المتكررة والعمود الجديد

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

3) ضبط العمود المنطقي إلى true. سينجح هذا فقط على أحد الصفوف المكررة بسبب القيد الجديد

update ignore mytable set tokeep = true;

4) حذف الصفوف التي لم يتم وضع علامة عليها كمحفظة

delete from mytable where tokeep is null;

5) اسقاط العمود المضافة

alter table mytable drop tokeep;

أقترح أن تحافظ على القيود التي قمت بإضافتها ، بحيث يتم منع التكرارات الجديدة في المستقبل.


سيعمل هذا الحل على نقل التكرارات إلى جدول واحد والتفوق في جدول آخر .

-- 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

كان علي أن أفعل ذلك مع حقول النص ، وعبر الحد 100 بايت على المؤشر.

أنا حل هذا عن طريق إضافة عمود ، والقيام تجزئة md5 من الحقول ، والقيام بالتغيير.

ALTER TABLE table ADD `merged` VARCHAR( 40 ) NOT NULL ;
UPDATE TABLE SET merged` = MD5(CONCAT(`col1`, `col2`, `col3`))
ALTER IGNORE TABLE table ADD UNIQUE INDEX idx_name (`merged`);

لدي هذا snipet الاستعلام ل SQLServer ولكن أعتقد أنه يمكن استخدامه في DBMS الآخرين مع تغييرات طفيفة:

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

لقد نسيت إخبارك بأن هذا الاستعلام لا يزيل الصف الذي يحتوي على أدنى معرف للصفوف المكررة. إذا كان هذا مفيدًا ، فجرّب هذا الاستعلام:

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

يحتوي MySQL على قيود حول الإشارة إلى الجدول الذي تقوم بحذفه منه. يمكنك حل هذا الأمر باستخدام جدول مؤقت ، مثل:

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);

من اقتراح Kostanos في التعليقات:
الاستعلام البطيء الوحيد أعلاه هو DELETE ، للحالات التي تحتوي على قاعدة بيانات كبيرة جدًا. قد يكون هذا الاستعلام أسرع:

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

يعتبر حذف التكرارات على جداول MySQL مشكلة شائعة ، وهذا نتيجة لقيود مفقودة لتجنب تلك التكرارات قبل اليد. لكن هذه القضية المشتركة عادة ما تأتي باحتياجات محددة ... والتي تتطلب مقاربات محددة. يجب أن يكون النهج مختلفًا ، على سبيل المثال ، حجم البيانات ، والمدخل المكرر الذي يجب الاحتفاظ به (بشكل عام الأول أو الأخير) ، وما إذا كانت هناك فهارس يجب حفظها ، أو ما إذا كنا نرغب في إجراء أي عمليات إضافية إجراء على البيانات المكررة.

هناك أيضًا بعض الخصائص على MySQL نفسها ، مثل عدم القدرة على الإشارة إلى نفس الجدول على سبب FROM عند تنفيذ جدول UPDATE (سوف يرفع خطأ MySQL # 1093). يمكن التغلب على هذا القيد باستخدام استعلام داخلي مع جدول مؤقت (كما هو مقترح على بعض الطرق أعلاه). لكن هذا الاستعلام الداخلي لن يؤدي بشكل جيد عند التعامل مع مصادر البيانات الكبيرة.

ومع ذلك ، هناك نهج أفضل لإزالة النسخ المكررة ، وهذا فعال وموثوق به ، ويمكن تكييفه بسهولة مع الاحتياجات المختلفة.

الفكرة العامة هي إنشاء جدول مؤقت جديد ، عادةً ما يضيف قيدًا فريدًا لتجنب المزيد من التكرارات ، ولإدراج البيانات من الجدول السابق في الجدول الجديد ، مع الاهتمام بالنسخ المكررة. يعتمد هذا الأسلوب على استعلامات MySQL INSERT بسيطة ، وينشئ قيدًا جديدًا لتجنب المزيد من التكرار ، ويتخطى الحاجة إلى استخدام استعلام داخلي للبحث عن التكرارات ، وجدول مؤقت يجب الاحتفاظ به في الذاكرة (وبالتالي ملائمة مصادر البيانات الكبيرة أيضًا).

هكذا يمكن تحقيقه. بالنظر إلى أن لدينا موظفًا في جدول ، مع الأعمدة التالية:

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

لحذف الصفوف التي تحتوي على عمود ss مكرر ، وحفظ الإدخال الأول فقط ، يمكن اتباع العملية التالية:

-- 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;

شرح فني

  • ينشئ السطر # 1 جدول tmp_eployee جديد مع بنية نفس تماماً مثل الجدول الموظف
  • يضيف السطر # 2 قيد فريد إلى جدول tmp_eployee جديد لتجنب أي تكرارات إضافية
  • يفحص الخط رقم 3 جدول الموظف الأصلي حسب المعرّف ، ويدرج إدخالًا جديدًا للموظفين في جدول tmp_eployee الجديد ، بينما يتجاهل الإدخالات المتكررة
  • يعيد السطر # 4 تسمية الجداول ، بحيث يحتفظ جدول الموظف الجديد بجميع الإدخالات بدون التكرارات ، ويتم الاحتفاظ بنسخة احتياطية من البيانات السابقة على جدول backup_employee

باستخدام هذا النهج ، تم تحويل تسجيلات 1.6M إلى 6k في أقل من 200 ثانية.

، باتباع هذه العملية ، يمكنك بسرعة وسهولة إزالة جميع التكرارات الخاصة بك وإنشاء قيد فريد من خلال تشغيل:

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;

وبطبيعة الحال ، يمكن تعديل هذه العملية بشكل أكبر لتكييفها لاحتياجات مختلفة عند حذف التكرارات. بعض الأمثلة يتبع.

✔ الاختلاف للحفاظ على الإدخال الأخير بدلاً من الإدخال الأول

نحتاج أحيانًا إلى الاحتفاظ بآخر إدخال مكرر بدلاً من الإدخال الأول.

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;
  • على السطر # 3 ، يجعل البند ORDER BY ID DESC آخر هوية للحصول على الأولوية على الباقي

✔ التباين لأداء بعض المهام على التكرارات ، على سبيل المثال الاحتفاظ بالعد على التكرارات الموجودة

في بعض الأحيان نحتاج إلى إجراء المزيد من المعالجة على الإدخالات المتكررة التي يتم العثور عليها (مثل الاحتفاظ بعدد التكرارات).

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;
  • في السطر 3 ، يتم إنشاء عمود جديد n_duplicates
  • على السطر # 4 ، يتم استخدام الاستعلام INSERT INTO ... ON DUPLICATE KEY UPDATE لإجراء تحديث إضافي عند العثور على نسخة مكررة (في هذه الحالة ، زيادة العداد) يمكن أن يكون الاستعلام INSERT INTO ... ON DUPLICATE KEY UPDATE تستخدم لأداء أنواع مختلفة من التحديثات للنسخ المكررة الموجودة.

✔ تباين لتجديد معرف الحقل التزايدي التلقائي

في بعض الأحيان ، نستخدم حقلًا تزايديًا تلقائيًا ، ومن أجل الحفاظ على الفهرس مضغوطًا قدر الإمكان ، يمكننا الاستفادة من حذف التكرارات لإعادة إنشاء الحقل التزايدي التلقائي في الجدول المؤقت الجديد.

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;
  • في السطر 3 ، بدلاً من تحديد كافة الحقول الموجودة في الجدول ، يتم تخطي حقل معرف بحيث يقوم محرك DB بإنشاء محرك جديد تلقائيًا

✔ مزيد من الاختلافات

كما يمكن إجراء العديد من التعديلات الإضافية بناءً على السلوك المطلوب. كمثال ، ستستخدم الاستعلامات التالية جدول مؤقت ثاني إلى جانب 1) الاحتفاظ الإدخال الأخير بدلاً من الأول؛ و 2) زيادة عداد على التكرارات الموجودة ؛ أيضا 3) إعادة توليد معرف الحقل المتزايد التلقائي مع الحفاظ على ترتيب الدخول كما كان على البيانات السابقة.

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;






duplicates