table MySQL মধ্যে ডুপ্লিকেট সারি সরান




select duplicate records in mysql (16)

আমি নিম্নলিখিত ক্ষেত্রের সাথে একটি টেবিল আছে:

id (Unique)
url (Unique)
title
company
site_id

এখন, আমাকে একই title, company and site_id থাকা সারিগুলি সরাতে হবে। এটি করার এক উপায় একটি স্ক্রিপ্ট ( PHP ) সহ নিম্নলিখিত SQL ব্যবহার করা হবে:

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

এই প্রশ্নটি চালানোর পরে, আমি একটি সার্ভার পার্শ্ব স্ক্রিপ্ট ব্যবহার করে ডুপ্লিকেটগুলি সরাতে পারি।

কিন্তু, আমি জানতে চাই যে এটি শুধুমাত্র এসকিউএল ক্যোয়ারী ব্যবহার করে করা যেতে পারে।


আরেকটি সমাধান আছে:

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

DELETE JOIN স্টেটমেন্ট ব্যবহার করে ডুপ্লিকেট সারি মুছুন MySQL আপনাকে DELETE JOIN স্টেটমেন্ট সরবরাহ করে যা আপনি দ্রুত সদৃশ সারিগুলি মুছে ফেলতে ব্যবহার করতে পারেন।

নিম্নলিখিত বিবৃতি সদৃশ সারি মুছে ফেলে এবং সর্বোচ্চ আইডি রাখে:

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

এটি করার একটি সহজ উপায় হল 3 কলামগুলিতে একটি UNIQUE সূচক যোগ করা। যখন আপনি ALTER বিবৃতি লিখবেন, তখন IGNORE কীওয়ার্ডটি অন্তর্ভুক্ত করুন। তাই ভালো:

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

এই সমস্ত সদৃশ সারি ড্রপ হবে। একটি অতিরিক্ত সুবিধার হিসাবে, ভবিষ্যতে INSERTs গুলো সদৃশ হবে। সবসময় হিসাবে, আপনি এই মত কিছু চলমান আগে একটি ব্যাকআপ নিতে চান ...


যদি আপনার বিশাল সংখ্যক রেকর্ড সহ একটি বড় টেবিল থাকে তবে উপরে সমাধানগুলি কাজ করবে না বা খুব বেশি সময় নেবে না। তারপর আমরা একটি ভিন্ন সমাধান আছে

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

এই সমাধানটি ডুপ্লিকেটগুলিকে এক টেবিলের মধ্যে এবং অন্যগুলিতে অন্যগুলি সরানো হবে।

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

আমি একটি সহজ উপায় খুঁজে পাওয়া যায় নি। (সর্বশেষ রাখা)

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

আমি যে কোনও সময় এই পৃষ্ঠাটি দেখার সময় রাখি যখন আমি "আমার MySQL ফর্মটি অনুলিপি মুছে ফেলি" গুগল করি তবে আমার থিওনগর সমাধানগুলি কাজ করে না কারণ আমার একটি INODB MySQL সারণী আছে

এই কোড যে কোন সময় ভাল কাজ করে

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 = একটি অস্থায়ী টেবিল তৈরি এবং মুছে ফেলা হয়েছে


সব ক্ষেত্রে সহজ এবং দ্রুত:

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

একটি সমাধান যা সহজ এবং বোঝার জন্য কোন প্রাথমিক কী দিয়ে কাজ করে:

1) একটি নতুন বুলিয়ান কলাম যোগ করুন

alter table mytable add tokeep boolean;

2) সদৃশ কলাম এবং নতুন কলামে একটি সীমাবদ্ধতা যোগ করুন

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

3) সত্য থেকে বুলিয়ান কলাম সেট। এটি শুধুমাত্র নতুন সীমাবদ্ধতার কারণে সদৃশ সারির একটিতে সফল হবে

update ignore mytable set tokeep = true;

4) সারি মুছে দিন যা রক্ষণাবেক্ষণ হিসাবে চিহ্নিত করা হয় নি

delete from mytable where tokeep is null;

5) যোগ কলাম ড্রপ

alter table mytable drop tokeep;

আমি সুপারিশ করছি যে আপনি যে সীমাবদ্ধতাটি যোগ করেছেন সেটি রাখুন, যাতে ভবিষ্যতে নতুন সদৃশগুলি প্রতিরোধ করা হয়।


আপনি সহজেই এই কোড থেকে নকল রেকর্ড মুছে ফেলতে পারেন ..

$qry = mysql_query("SELECT * from cities");
while($qry_row = mysql_fetch_array($qry))
{
$qry2 = mysql_query("SELECT * from cities2 where city = '".$qry_row['city']."'");

if(mysql_num_rows($qry2) > 1){
    while($row = mysql_fetch_array($qry2)){
        $city_arry[] = $row;

        }

    $total = sizeof($city_arry) - 1;
        for($i=1; $i<=$total; $i++){


            mysql_query( "delete from cities2 where town_id = '".$city_arry[$i][0]."'");

            }
    }
    //exit;
}

দ্রুত উপায় একটি অস্থায়ী টেবিলের মধ্যে স্বতন্ত্র সারি সন্নিবেশ করা হয়। মুছে ফেলার মাধ্যমে, এটি আমাকে 8 মিলিয়ন সারির একটি টেবিল থেকে সদৃশগুলি সরানোর জন্য কয়েক ঘন্টা লেগেছে। সন্নিবেশ এবং স্বতন্ত্র ব্যবহার করে, এটি মাত্র 13 মিনিট সময় নেয়।

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;  

আপনি মুছে ফেলছেন টেবিল উল্লেখ সম্পর্কে 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);

কস্টানসসের মন্তব্য থেকে মন্তব্য করেছেন:
উপরের একমাত্র ধীর প্রশ্নটি DELETE, যেখানে আপনার কাছে একটি খুব বড় ডাটাবেস রয়েছে। এই প্রশ্নের দ্রুত হতে পারে:

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

আমি টেক্সট ক্ষেত্রের সাথে এটি করতে ছিল এবং সূচক উপর 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`);

আমার কাছে এসকিউএল সার্ভারের জন্য এই ক্যোয়ারী স্নিপেট আছে কিন্তু আমার মনে হয় এটি অন্য কিছুতে ডিবিএমএসগুলিতে ছোট পরিবর্তনগুলির সাথে ব্যবহার করা যেতে পারে:

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)

সংস্করণ 8.0 (2018) অনুসারে, মাইএসকিউএল অবশেষে উইন্ডো ফাংশনগুলিকে সমর্থন করে

উইন্ডো ফাংশন উভয় সহজ এবং দক্ষ। এখানে একটি সমাধান যা এই নিয়োগটি সমাধানের জন্য কীভাবে তাদের ব্যবহার করতে হয় তা প্রদর্শন করে।

উপধারায়, আমরা id দ্বারা column1/column2 গোষ্ঠীর মধ্যে টেবিলের প্রতিটি রেকর্ডের অবস্থান নির্ধারণ করতে ROW_NUMBER() ব্যবহার করতে পারি। যদি কোন সদৃশ থাকে তবে রেকর্ডটি সারি নম্বর 1 । যদি ডুপ্লিকেট বিদ্যমান থাকে, তারা আরোহী id ( 1 থেকে শুরু) দ্বারা গণনা করা হবে।

একবার রেকর্ডগুলি সাবquারিতে সঠিকভাবে গণনা করা হয়, বাইরের ক্যোয়ারী কেবলমাত্র সমস্ত রেকর্ড মুছে ফেলে যার সারি সংখ্যা 1 নয়।

প্রশ্ন :

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
)

MySQL টেবিলগুলিতে ডুপ্লিকেটগুলি মুছে ফেলা একটি সাধারণ সমস্যা, এটি স্বাভাবিকভাবেই একটি অনুপস্থিত সীমাবদ্ধতার ফল যা আগে ডুপ্লিকেটগুলি এড়াতে পারে। কিন্তু এই সাধারণ সমস্যা সাধারণত নির্দিষ্ট চাহিদা সঙ্গে আসে ... নির্দিষ্ট পন্থা প্রয়োজন। পদ্ধতির উপর নির্ভর করে পদ্ধতির আকার, উদাহরণস্বরূপ, ডুপ্লিকেটযুক্ত এন্ট্রি যা রাখা উচিত (সাধারণত প্রথম বা শেষটি), কিনা সূচী রাখতে হবে কিনা, অথবা আমরা কোন অতিরিক্ত কাজ করতে চাই কিনা সদৃশ তথ্য কর্ম।

মাইএসকিউএল নিজেই কিছু নির্দিষ্টকরণ রয়েছে, যেমন একটি টেবিল আপডেটিং (এটি মাইএসকিউএল ত্রুটি # 1093 বাড়াতে হবে) সঞ্চালনের সময় একটি ফরম থেকে একই টেবিলের উল্লেখ করতে সক্ষম নয়। এই সীমাবদ্ধতাটি একটি অস্থায়ী টেবিলের সাথে অভ্যন্তরীণ ক্যোয়ারী ব্যবহার করে অতিক্রম করা যেতে পারে (যেমন উপরে কিছু পদ্ধতিতে প্রস্তাবিত)। কিন্তু বৃহত্তর তথ্য উৎসের সাথে মোকাবিলা করার সময় এই অভ্যন্তরীণ ক্যোয়ারী বিশেষভাবে ভাল সঞ্চালন করবে না।

তবে, ডুপ্লিকেটগুলি অপসারণের জন্য একটি ভাল পদ্ধতি বিদ্যমান, এটি কার্যকরী এবং নির্ভরযোগ্য উভয় এবং এটি সহজেই বিভিন্ন চাহিদাগুলিতে অভিযোজিত করা যেতে পারে।

সাধারণ ধারণাটি হল একটি নতুন অস্থায়ী টেবিল তৈরি করা, যা সাধারণত ডুপ্লিকেটগুলি এড়ানোর জন্য একটি অনন্য সীমাবদ্ধতা যোগ করে এবং সদৃশগুলির যত্ন নেওয়ার সময় আপনার পূর্বের টেবিলে থাকা তথ্যটিকে নতুনতে সন্নিবেশ করান। এই পদ্ধতিটি সহজ MySQL INSERT ক্যোয়ারীগুলিতে নির্ভর করে, আরও সদৃশ এড়াতে নতুন সীমাবদ্ধতা তৈরি করে এবং ডুপ্লিকেটগুলি অনুসন্ধানের জন্য একটি অভ্যন্তরীণ ক্যোয়ারী এবং মেমরিতে থাকা উচিত এমন একটি অস্থায়ী টেবিল ব্যবহার করার প্রয়োজনীয়তা এড়িয়ে যায় (এভাবে বড় ডেটা উত্সগুলিও উপযুক্ত)।

এটি অর্জন করা যায় কিভাবে। নিচের কলামগুলির সাথে আমাদের একটি টেবিল কর্মচারী দেওয়া আছে:

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

একটি অনুলিপি এসএসএন কলাম সহ সারি মুছে ফেলার জন্য, এবং শুধুমাত্র প্রথম এন্ট্রি পাওয়া, নিম্নলিখিত প্রক্রিয়া অনুসরণ করা যেতে পারে:

-- 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.6 এম নিবন্ধক 200 সেকেন্ডেরও কম সময়ে 6k তে রূপান্তরিত হয়েছিল।

, এই প্রক্রিয়াটি অনুসরণ করে, আপনি দ্রুত এবং সহজেই আপনার সমস্ত সদৃশগুলি সরান এবং চলমান একটি অনন্য বাধা তৈরি করতে পারেন:

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;

অবশ্যই, ডুপ্লিকেটগুলি মুছে ফেলার সময় এই প্রক্রিয়াটি বিভিন্ন চাহিদাগুলির জন্য এটি মানিয়ে নিতে আরও সংশোধন করা যেতে পারে। কিছু উদাহরণ অনুসরণ করুন।

One প্রথমটির পরিবর্তে শেষ এন্ট্রি রাখার জন্য বৈচিত্র

কখনও কখনও আমরা প্রথম এক পরিবর্তে শেষ সদৃশ এন্ট্রি রাখা প্রয়োজন।

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 এ, আইডি ডিইএসসি ধারা দ্বারা অর্ডারটি শেষ আইডি এর বাকিদের অগ্রাধিকার পেতে দেয়

Dup ডুপ্লিকেটগুলিতে কিছু কাজ সম্পাদনের জন্য বৈচিত্র্য, উদাহরণস্বরূপ পাওয়া ডুপ্লিকেটগুলিতে গণনা রাখা

কখনও কখনও পাওয়া যায় এমন অনুলিপি করা এন্ট্রিগুলির উপর আমাদের আরও কিছু প্রক্রিয়া করতে হবে (যেমন ডুপ্লিকেটগুলির গণনা রাখা)।

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 ... অনুলিপি করুন মূল আপডেট ক্যোয়ারীটি যখন একটি সদৃশ খুঁজে পাওয়া যায় তখন একটি অতিরিক্ত আপডেট সঞ্চালনের জন্য ব্যবহৃত হয় (এই ক্ষেত্রে, একটি পাল্টা বৃদ্ধি করা হচ্ছে) INSERT ... অনুলিপি করুন মূল আপডেটের প্রশ্নটি হতে পারে পাওয়া duplicates জন্য বিভিন্ন ধরনের আপডেট সঞ্চালন ব্যবহৃত।

-স্বয়ংক্রিয় ক্রমবর্ধমান ক্ষেত্র আইডি পুনর্জন্মের জন্য বৈচিত্র

কখনও কখনও আমরা একটি স্বয়ংক্রিয় ক্রমবর্ধমান ক্ষেত্র ব্যবহার করি এবং, সূচকটি যতটা সম্ভব কম্প্যাক্ট রাখতে থাকি, আমরা নতুন অস্থায়ী টেবিলের স্বয়ংক্রিয়-বৃদ্ধিশীল ক্ষেত্রটি পুনরুত্থানের জন্য সদৃশগুলি মুছে ফেলার সুবিধা নিতে পারি।

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 এ, টেবিলে সমস্ত ক্ষেত্র নির্বাচন করার পরিবর্তে, আইডি ক্ষেত্রটি বাদ দেওয়া হয় যাতে ডিবি ইঞ্জিনটি স্বয়ংক্রিয়ভাবে একটি নতুন তৈরি করে।

✔ আরও বৈচিত্র

অনেক আরও পরিবর্তন পছন্দসই আচরণ উপর নির্ভর করে ডেমো হয়। উদাহরণস্বরূপ, নিম্নলিখিত প্রশ্নগুলি দ্বিতীয় দ্বিতীয় অস্থায়ী টেবিলে ব্যবহার করা হবে, 1) এর পরিবর্তে প্রথম এন্ট্রিটি রাখুন; এবং 2) পাওয়া duplicates একটি পাল্টা বৃদ্ধি; এছাড়াও 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