Upsert با INSERT ... ON DUPLICATE KEY UPDATE؛ و دام‌های REPLACE و INSERT IGNORE

«اگر هست به‌روز کن، اگر نیست اضافه کن»

سناریوی رایج: هر شب فایل موجودی انبار کارخانه (مثلاً خروجی اکسل) باید با جدول محصولات هماهنگ شود. کالاهای جدید اضافه و کالاهای موجود به‌روز شوند. راه ساده‌لوحانه «SELECT، بعد تصمیم، بعد INSERT یا UPDATE» است که زیر بار هم‌زمان شرایط رقابتی (race condition) می‌سازد: دو پردازه هم‌زمان می‌بینند ردیف وجود ندارد و هر دو INSERT می‌کنند. MySQL این کار را در یک دستور اتمی انجام می‌دهد، به شرط آنکه یک کلید UNIQUE یا PRIMARY KEY برخورد را تشخیص دهد.

-- sku در جدول carpets یکتاست
INSERT INTO carpets (sku, title, city, width_cm, length_cm, price, stock)
VALUES ('KSH-M700', 'فرش ماشینی ۷۰۰ شانه', 'کاشان', 250, 350, 69000000, 12)
AS new
ON DUPLICATE KEY UPDATE
  price = new.price,
  stock = carpets.stock + new.stock;

نحو AS new از نسخه‌ی 8.0.19 آمده و جایگزین تابع قدیمی VALUES(col) شده که منسوخ اعلام شده است. در MariaDB و MySQL قدیمی همان VALUES(price) را بنویسید.

معنی «rows affected»

ورود دسته‌ای از جدول موقت

INSERT INTO carpets (sku, title, city, width_cm, length_cm, price, stock)
SELECT sku, title, city, w, l, price, qty FROM import_batch
ON DUPLICATE KEY UPDATE
  price = VALUES(price),        -- در حالت INSERT ... SELECT هنوز رایج است
  stock = VALUES(stock);

-- گرفتن id ردیف، چه درج شده باشد چه به‌روز
INSERT INTO tags (name) VALUES ('ابریشم')
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();

REPLACE و INSERT IGNORE: دو میان‌بر خطرناک

REPLACE INTO ردیف قدیمی را حذف و ردیف تازه را درج می‌کند. یعنی: id عوض می‌شود، ستون‌هایی که نام نبرده‌اید به پیش‌فرض برمی‌گردند، Triggerهای DELETE اجرا می‌شوند و اگر کلید خارجی با ON DELETE CASCADE داشته باشید، ردیف‌های فرزند (مثلاً اقلام سفارش) هم پاک می‌شوند.

INSERT IGNORE فقط تکراری‌ها را نادیده نمی‌گیرد؛ همه‌ی خطاهای قابل‌تبدیل را به warning تبدیل می‌کند: متن بلند بریده می‌شود، NULL در ستون NOT NULL به صفر یا رشته‌ی خالی تبدیل می‌شود و تاریخ نامعتبر صفر می‌شود — حتی در حالت strict.

INSERT IGNORE INTO carpets (sku, title, city, width_cm, length_cm, price)
VALUES ('KSH-1001', 'تکراری', 'کاشان', 1, 1, 1);
SHOW WARNINGS;   -- Duplicate entry 'KSH-1001' for key 'carpets.sku'

نکته‌هایی که کمتر کسی می‌داند

• اگر جدول چند کلید UNIQUE داشته باشد و ردیف ورودی با دو ردیف مختلف برخورد کند، ODKU فقط یکی از آن‌ها را به‌روز می‌کند و کدام، قابل پیش‌بینی نیست؛ upsert را روی جدول‌هایی با یک کلید یکتای منطقی انجام دهید.
• هر ODKU که به UPDATE ختم شود هم یک عدد AUTO_INCREMENT مصرف می‌کند؛ جدولی که روزی میلیون‌ها upsert می‌خورد با INT معمولی سریع‌تر از آنچه فکر می‌کنید به سقف می‌رسد.
• ترتیب انتساب‌ها در ODKU مهم است: در SET a = new.a, b = a + 1 ستون b از مقدار جدید a استفاده می‌کند.
• برای «فقط اگر نیست درج کن» بدون خاموش کردن بقیه‌ی خطاها از ON DUPLICATE KEY UPDATE id = id استفاده کنید؛ امن‌تر از INSERT IGNORE است.
• MySQL برخلاف MariaDB و PostgreSQL عبارت RETURNING ندارد؛ ستون‌های محاسبه‌شده را با یک SELECT بعدی بخوانید.