مدیریت رکوردهای تکراری در MySQL – راهنمای جامع

به طور کلی جدول‌ها یا مجموعه نتایج حاصل از کوئری‌ها، گاهی اوقات شامل رکوردهای تکراری هستند. در اغلب موارد این وضعیت مجاز است، اما در پاره‌ای موارد نیز لازم است که رکوردهای تکراری حذف شوند. بدین ترتیب باید رکوردهای تکراری شناسایی شده و از جدول حذف شوند. در این بخش از سلسله مطالب راهنمای جامع MySQL به توصیف روش جلوگیری از رخداد رکوردهای مکرر در یک جدول و شیوه حذف رکوردهای از قبل موجود می‌پردازیم.

اجتناب از ایجاد رکوردهای مکرر در یک جدول

می‌توان از اندیس یکتا (UNIQUE) یا یک PRIMARY KEY روی جدول با فیلدهای مناسب برای جلوگیری از ایجاد رکوردهای تکراری استفاده کرد.

در ادامه مثالی را بررسی می‌کنیم. در جدول زیر هیچ اندیس یا PRIMARY KEY وجود ندارد و از این رو امکان ایجاد رکوردهای تکراری برای first_name و last_name وجود دارد.

CREATE TABLE person_tbl (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

برای جلوگیری از ایجاد رکوردهای چندگانه با نام و نام خانوادگی یکسان در این جدول می‌توان یک PRIMARY KEY به تعریف آن اضافه کرد. بدین ترتیب لازم است که ستون‌های اندیس‌دار غیر Null باشند، چون PRIMARY KEY امکان ایجاد مقادیر Null را نمی‌دهد.

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

با وجود یک اندیس یکتا در یک جدول در صورتی که رکوردی را در جدول درج کنید که نسخه تکراری از یک رکورد موجود در ستون یا ستون‌هایی که اندیس روی آن تعریف شده، باشد، به طور معمول منجر به یک خطا می‌شود.

دستور NSERT IGNORE

در چنین حالتی می‌توان از دستور INSERT IGNORE به جای دستور INSERT استفاده کرد. اگر یک رکورد، نسخه‌ای تکراری از رکورد از قبل موجود نباشد، در این صورت MySQL آن را به طور معمول درج می‌کند؛ اما اگر تکراری باشد، در این صورت کلیدواژه IGNORE به MySQL می‌گوید که آن را بدون صدور هیچ خطایی رد کند.

در مثال زیر هیچ خطایی ایجاد نمی‌شود و همزمان رکوردهای تکراری نیز در جدول درج نمی‌شوند.

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

دستور REPLCAE

می‌توان از دستور REPLCAE به جای دستور INSERT استفاده کرد. اگر رکورد جدید باشد، همانند زمان استفاده از دستور INSERT درج می‌شود؛ اما اگر تکراری باشد، رکورد جدید جایگزین رکورد قبلی می‌شود.

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

دستورهای INSERT IGNORE و REPLACE بسته به رفتاری که با رکوردهای تکراری می‌خواهیم داشته باشیم، باید انتخاب شوند. دستور INSERT IGNORE رکورد قبلی را نگه می‌دارد و رکورد جدید را نادیده می‌گیرد. دستور REPLCAE نسخه جدید تکراری را درج می‌کند و نسخه قدیمی را پاک می‌کند.

روش دیگر برای الزام یکتا بودن این است که به جای یک PRIMARY KE، یک اندیس UNIQUE به جدول اضافه کنیم:

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

شمارش و شناسایی موارد تکراری

در کوئری زیر تعداد رکوردهای تکراری که دارای نام و نام خانوادگی مشابهی هستند در یک جدول شمارش می‌شود:

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
   -> FROM person_tbl
   -> GROUP BY last_name, first_name
   -> HAVING repetitions > 1;

این کوئری لیستی از همه رکوردهای تکراری در جدول person_tbl بازمی‌گرداند. به طور کلی برای شناسایی مجموعه مقادیر تکراری باید از مراحل زیر استفاده کنید:

• تعیین این که کدام ستون شامل مقادیری است که ممکن است تکراری باشند.
• لیست کردن ستون‌هایی در لیست انتخاب ستون همراه با COUNT(*)
• باید ستون‌ها در بند GROUP BY نیز لیست شوند.
• یک بند HAVING اضافه می‌شود که مقادیر یکتا را با الزام تعداد گروه‌ها برای بزرگ‌تر از 1 بودن حذف می‌کند.

حذف موارد تکراری از نتیجه کوئری

می‌توانید از دستور DISTINCT همراه با عبارت SELECT برای یافتن رکوردهای یکتای موجود در یک جدول استفاده کنید.

mysql> SELECT DISTINCT last_name, first_name
   -> FROM person_tbl
   -> ORDER BY last_name;

جایگزین دیگر برای دستور DISTINCT، افزودن بند GROUP BY است که ستون‌های انتخابی را مشخص می‌کند. بدین ترتیب موارد تکراری حذف می‌شوند و تنها ترکیب‌های یکتای مقادیر در ستون‌های مشخص شده انتخاب می‌شوند:

mysql> SELECT last_name, first_name
   -> FROM person_tbl
   -> GROUP BY (last_name, first_name);

حذف موارد تکراری با استفاده از جایگزینی جدول

اگر رکوردهای تکراری در یک جدول وجود داشته باشند و بخواهیم همه رکوردهای تکراری را از جدول حذف کنیم، می‌توانیم از رویه زیر استفاده کنیم:

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   -> FROM person_tbl;
   -> GROUP BY (last_name, first_name);

mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

یک روش آسان برای حذف موارد تکراری از یک جدول ابن است که یک INDEX یا یک PRIMARY KEY به آن جدول اضافه کنیم. حتی اگر جدول از قبل تشکیل یافته باشد می‌توان از این تکنیک برای حذف رکوردهای تکراری و امن نگه‌داشتن آن در آینده استفاده کرد.

اگر این مطلب برایتان مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:

• مجموعه آموزش‌های پایگاه داده و سیستم های مدیریت اطلاعات
• جدول های موقت (Temporary Tables) در MySQL — راهنمای جامع
• مجموعه آموزش‌های دروس مهندسی کامپیوتر
• کلاستر چند گرهی MySQL روی اوبونتو ۱۸.۰۴ — به زبان ساده
• نصب MySQL — راهنمای گام‌ به‌ گام
• راهنمای نصب (Nginx ،MySQL ،PHP (LEMP روی اوبونتو – به زبان ساده

==