טיפ mySQL: עדכון מונה בטבלה

אחד הלקוחות שלי ביקש לדעת מה מספר הצפיות בתכנים שהוא מעלה. מכיוון שמספר הצפיות בכל אחד מהתכנים אצלו יכול להגיע למאות אלפים רציתי פתרון פשוט שלא יהיה כרוך בניהול של טבלה ענקית שבה כל שורה מתעדת צפייה אחת באחד התכנים. הפתרון בו בחרתי הוא של מונה שמתעדכן בכל פעם שמישהו נכנס לדף.

זה מבנה הטבלה שמאחסנת את הנתונים:

CREATE TABLE `posts_views` (
   `post_id` int(11) UNSIGNED DEFAULT 0,
   `views` int(11) UNSIGNED DEFAULT 0,
   `created_at` datetime DEFAULT NOW(),
   `updated_at` datetime DEFAULT NULL ON UPDATE NOW()
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

ALTER TABLE `posts_views`
  ADD PRIMARY KEY (`post_id`);

ALTER TABLE `posts_views`
  MODIFY `post_id` int(11) UNSIGNED NOT NULL;

• העמודה post_id היא PRIMARY KEY, וזה חיוני לפתרון כפי שמיד אסביר.
• מספר הצפיות מתועד בעמודה מספרית ששמה views.

הדרך הרגילה לעדכן את מספר הצפיות היא באמצעות ניסיון לשלוף את הרשומה עבור post_id, ואם הוא קיים להוסיף 1 ולעדכן את הרשומה עם UPDATE, אבל אם אינו קיים להשתמש ב-INSERT כדי ליצור רשומה חדשה. הדרך הזו מייגעת כי היא דורשת כתיבת 3 שאילתות אבל אני רציתי שאילתה אחת שתעשה את העבודה.

הדרך שמצאתי משתמשת ב-INSERT בשילוב עם ON DUPLICATE KEY UPDATE שיוצרת רשומה חדשה אך במצב בו הרשומה כבר קיימת היא מעדכנת אותה.

איך השאילתה יודעת אם רשומה קיימת? זה בגלל שהיא בודקת האם אינדקס מסוג UNIQUE או PRIMARY KEY קיים, ובמקרה שהאינדקס קיים היא מעדכנת את הרשומה במקום לנסות ליצור חדשה.

כך נראית השאילתה:

INSERT INTO posts_views (post_id, views, created_at, updated_at) 
VALUES (1, 1, NOW(), NOW()) 
ON DUPLICATE KEY UPDATE views = views + 1;

במילים: תרשום צפייה 1 למאמר שהמזהה הייחודי שלו, ה-id שלו, בטבלת הפוסטים הוא 1.

אחרי הפעם הראשונה בה הרצתי את השאילתה קיבלתי:

מספר הצפיות 1.

הרצתי שוב את אותה השאילתה בדיוק.

קיבלתי 2 צפיות כי מונה הצפיות מתעדכן באופן אוטומטי.

פעם שלישית עם אותה השאילתה בדיוק:

3 צפיות.

הוספתי צפייה 1 למאמר אחר שה-id שלו בטבלת הפוסטים הוא 95:

INSERT INTO posts_views (post_id, views, created_at, updated_at) 
VALUES (95, 1, NOW(), NOW()) 
ON DUPLICATE KEY UPDATE views = views + 1;

וראיתי שנוספה רשומה חדשה עם צפייה 1 למאמר שמספרו 95.

אחרי שסיימתי את העבודה הלקוח היה כל כך מרוצה שהוא ביקש ליישם את המונה על כל סוגי התכנים (מאמרים, מדריכים, פוסטים בבלוג), אבל הטבלה לעיל לא יכולה לתת מענה מכיוון שסוגי התכנים מפוזרים בין טבלאות שונות (טבלה למדריכים, טבלה לפוסטים וטבלה לבלוגים), ובטבלאות שונות יכולים להיות תכנים עם אותו מספר אינדקס. הפתרון שמצאתי היה להוסיף עמודה מסוג enum שתכיל את סוג המאמר ולעשות אינדקס משותף ייחודי UNIQUE על שתי העמודות, סוג המאמר ומספר האינדקס שלו.

זה מבנה הטבלה לאחר השינוי:

CREATE TABLE `articles_views` (
  `id` int(11) UNSIGNED NOT NULL,
  `article_type` enum('news','tutorial', 'blog') NOT NULL DEFAULT 'news',
  `article_type_id` int(10) NOT NULL DEFAULT '0',
  `views` int(11) UNSIGNED DEFAULT '0',
  `created_at` datetime DEFAULT NOW(),
  `updated_at` datetime DEFAULT NULL ON UPDATE NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `articles_views`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `articles_views`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

• העמודה article_type היא בהתאם לסוג המאמר.
• העמודה article_type_id מצביעה על הרשומה בטבלה המקורית.

בנוסף, הגדרתי אינדקס ייחודי על שתי העמודות:

ALTER TABLE `articles_views`
 ADD UNIQUE KEY `article_type_and_id` (`article_type`,`article_type_id`);

ניתן להגדיר אינדקס על יותר מעמודה אחת. הטכניקה נקראת composite unique index. בגלל שהוא ייחודי לא ניתן ליצור יותר מרשומה אחת המכילה את אותו סוג המאמר והמספר.

הכנסתי רשומה למאמר מסוג news שמספרו 101.

INSERT INTO `articles_views`
VALUES (NULL, 'news', 101, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE views = views + 1;

המונה הראה 1.

הרצתי את אותה שאילתה בדיוק בפעם השנייה:

למונה נוסף 1 באופן אוטומטי, וכך עלה מספר הצפיות ל- 2.

הכנסתי צפייה לבלוג אחר שמספרו 42.

INSERT INTO `articles_views` 
VALUES (NULL, 'blog', 42, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE views = views + 1;

והתוצאה:

הרשומה החדשה נוצרה עם צפייה אחת עבור הבלוג. בדיוק מה שרציתי!

מעניין אבל האינדקס של הטבלה (עמודת id) קפץ מ-1 ל-3. הסיבה לפער קשורה בדרך שבה השאילתה עובדת. בכל פעם ש-mySQL עושה INSERT באופן אוטומטי מתווסף 1 לעמודת ה-auto increment, בין אם האופרציה מצליחה או נכשלת. במקרה של כשלון, נכנס ה-UPDATE לפעולה אבל התוספת לאינדקס כבר נעשתה, ומכאן הפער בספירה.

לכל מדריכי ה- mySQL

אהבתם? לא אהבתם? דרגו!

0 הצבעות, ממוצע 0 מתוך 5 כוכבים