במדריך זה נלמד כיצד להסיר שורות כפולות במסד נתונים של MySQL כדי להקטין את גודל מסד הנתונים ולעזור להגדיל את ביצועי השרת.
לפני שנמשיך, אנו מניחים:
- מותקנת ופועלת מערכת MySQL במערכת שלך
- יש לך גישה שורשית למסד הנתונים.
- יש לך גישה למסד נתונים לצורך ניסויים או בדיקות
הערה : אם אתה צריך מסד נתונים לדוגמא כדי לנסות את המושגים המסופקים במדריך זה, אנא שקול את מסד הנתונים של Sakila או הורד עותק של מסד הנתונים המשמש במדריך זה.
המשאבים ניתנים להלן:
שימוש בסיסי
לפני שנתחיל, ניצור בכוונה טבלה המכילה ערכים כפולים למטרות בדיקה. להלן שאילתות SQL לביצוע פעולה זו:
להשתמש עוֹלָם;יְרִידָה שולחן אם קיים משתמשים;
לִיצוֹר שולחן משתמשים(תְעוּדַת זֶהוּת INT מפתח ראשי לֹא ריק AUTO_INCREMENT ,שם משתמש VARCHAR (10) לֹא ריק ,שם מלא VARCHAR (עשרים),אימייל VARCHAR (255) לֹא ריק );
לְהַכנִיס לְתוֹך משתמשים(שם משתמש,שם מלא,אימייל) ערכים
('מַזַל בְּתוּלָה', 'קלוד מ' מורי ', '[מוגן בדוא'ל]'),
('ללחוץ', 'טיפאני ג'יי ביילי', '[מוגן בדוא'ל]'),
('רָקֵטָה', 'כריסטופר ס פייטון', '[מוגן בדוא'ל]'),
('חומר אפל', 'פטרישיה ג'יי פוקס', '[מוגן בדוא'ל]'),
('נושא', 'פיי הארטלי', '[מוגן בדוא'ל]'),
('חומר אפל', 'פטרישיה ג'יי פוקס', '[מוגן בדוא'ל]'),
('רָקֵטָה', 'כריסטופר ס פייטון', '[מוגן בדוא'ל]'),
('ארטמיס', 'וסלי סי דילארד', '[מוגן בדוא'ל]');
אל תהסס לשנות את השאילתה למעלה כך שתתאים לצרכיך. עליך גם לוודא שיש לך את מסד הנתונים (העולם) שנוצר כדי למנוע טעויות.
כעת, אם נקבל את כל הנתונים בתוך הטבלה ובסדר לפי שם משתמש, נראה את הכפילויות שיש לנו כפי שמוצג:
mysql> להשתמש עוֹלָם;מאגר מידע השתנה
mysql> בחר * מ משתמשים מיין לפי שם משתמש;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|תְעוּדַת זֶהוּת|שם משתמש|שם מלא|אימייל|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |ארטמיס|וסלי סי דילארד|[מוגן בדוא'ל]|
| 4 |חומר אפל|פטרישיה ג'יי פוקס|[מוגן בדוא'ל]|
| 6 |חומר אפל|פטרישיה ג'יי פוקס|[מוגן בדוא'ל]|
| 2 |ללחוץ|טיפאני ג'יי ביילי|[מוגן בדוא'ל]|
| 5 |נושא|פיי הארטלי|[מוגן בדוא'ל]|
| 3 |רָקֵטָה|כריסטופר ס פייטון|[מוגן בדוא'ל]|
| 7 |רָקֵטָה|כריסטופר ס פייטון|[מוגן בדוא'ל]|
| 1 |מַזַל בְּתוּלָה|קלוד מ 'מורי|[מוגן בדוא'ל]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
כפי שאתה יכול לראות מהטבלה למעלה, יש לנו שני ערכים כפולים שהופכים את מסד הנתונים לגדול יותר ללא סיבה וגורמים למהירויות איטיות.
הבה נלמד כעת כיצד אנו יכולים להסיר ערכים אלה.
#1 - מחק הצטרפות
אחת הדרכים להסיר שורות כפולות במסד נתונים היא שימוש במשפט MySQL DELETE JOIN. אולם השאילתה משתמשת במזהים להסרת ערכים כפולים.
לדוגמה, כדי להסיר את הערכים הכפולים בטבלת המשתמשים למעלה, נוכל להזין:
לִמְחוֹק שולחן 1 מ טבלת משתמשים 1 פְּנִימִי לְהִצְטַרֵף טבלת משתמשים 2 איפה table1.id<table2.id וגם שולחן 1. דואר אלקטרוני=table2.email;לאחר שתבצע את השאילתה למעלה, תסיר את הערכים הכפולים כפי שמוצג בפלט שלהלן:
mysql> לִמְחוֹק שולחן 1 מ טבלת משתמשים 1 פְּנִימִי לְהִצְטַרֵף טבלת משתמשים 2 איפה table1.id<table2.id וגם שולחן 1. דואר אלקטרוני=table2.email;השאילתה בסדר, 2שורות מושפעות(0.01שניות)
mysql> בחר * מ משתמשים מיין לפי שם משתמש;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|תְעוּדַת זֶהוּת|שם משתמש|שם מלא|אימייל|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |ארטמיס|וסלי סי דילארד|[מוגן בדוא'ל]|
| 6 |חומר אפל|פטרישיה ג'יי פוקס|[מוגן בדוא'ל]|
| 2 |ללחוץ|טיפאני ג'יי ביילי|[מוגן בדוא'ל]|
| 5 |נושא|פיי הארטלי|[מוגן בדוא'ל]|
| 7 |רָקֵטָה|כריסטופר ס פייטון|[מוגן בדוא'ל]|
| 1 |מַזַל בְּתוּלָה|קלוד מ 'מורי|[מוגן בדוא'ל]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
מספר 2 - פונקציה Row_Number ()
השיטה השנייה שאנו יכולים ליישם היא להשתמש בפונקציה MySQL row_number (). פונקציה זו נתמכת בגרסת MySQL 8 ומעלה.
הוא פועל על ידי הקצאת ערך int רציף לכל שורה, כאשר שורות המכילות ערכים כפולים מקבלים ערך גבוה מ -1.
למידע נוסף על פונקציה זו, השתמש במשאב המצורף להלן:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
שקול את השאילתה למטה המחזירה את מזהה השורות עם ערכים כפולים:
בחר תְעוּדַת זֶהוּת מ ( בחר תְעוּדַת זֶהוּת,ROW_NUMBER()על( חֲלוּקָה לפי שם משתמש מיין לפי שם משתמש) כפי ש row_var מ משתמשים)t1 איפה row_var> 1;לאחר שתבצע את השאילתה לעיל, אתה אמור לקבל את רשימת המזהים כפי שמוצג בפלט שלהלן:
+ ---- +|תְעוּדַת זֶהוּת|
+ ---- +
| 6 |
| 7 |
+ ---- +
2שורות ב מַעֲרֶכֶת (0.01שניות)
אם ברצונך להסיר את הערכים, פשוט החלף את משפט SELECT במשפט DELETE כפי שמוצג להלן:
לִמְחוֹק מ משתמשים איפה תְעוּדַת זֶהוּת IN ( בחר תְעוּדַת זֶהוּת מ ( בחר תְעוּדַת זֶהוּת,ROW_NUMBER()על( חֲלוּקָה לפי שם משתמש מיין לפי שם משתמש) כפי ש row_var מ משתמשים)t1 איפה row_var> 1);לבסוף, תוכל לאמת שהערכים הכפולים מוסרים באמצעות משפט SELECT.
mysql> בחר * מ משתמשים מיין לפי שם משתמש;+ ---- + ------------ + ----------------------- + ------- ---------------- +
|תְעוּדַת זֶהוּת|שם משתמש|שם מלא|אימייל|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |ארטמיס|וסלי סי דילארד|[מוגן בדוא'ל]|
| 4 |חומר אפל|פטרישיה ג'יי פוקס|[מוגן בדוא'ל]|
| 2 |ללחוץ|טיפאני ג'יי ביילי|[מוגן בדוא'ל]|
| 5 |נושא|פיי הארטלי|[מוגן בדוא'ל]|
| 3 |רָקֵטָה|כריסטופר ס פייטון|[מוגן בדוא'ל]|
| 1 |מַזַל בְּתוּלָה|קלוד מ 'מורי|[מוגן בדוא'ל]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
סיכום
במדריך זה דנו בשתי השיטות להסרת ערכים כפולים ממאגר נתונים. מאגרי מידע גדולים, במיוחד אלה הנמצאים בשימוש נפוץ, עשויים להכיל ערכים כפולים רבים מיבוא חיצוני ושגיאות אחרות. לכן, יש צורך לשמור על טיהור ערכים כפולים כדי להבטיח שהיישומים יבצעו בצורה מיטבית.