INSERT، UPDATE و DELETE امن؛ safe updates و sql_mode

نوشتن داده بدون ترس

هر برنامه‌نویسی دست‌کم یک بار UPDATE بدون WHERE اجرا کرده است. در این درس الگوهایی را می‌بینیم که این اتفاق را تقریباً ناممکن می‌کنند.

INSERT

-- چند ردیف در یک دستور: ده‌ها برابر سریع‌تر از INSERTهای جدا
INSERT INTO carpets (sku, title, city, width_cm, length_cm, price, stock) VALUES
  ('KSH-M701', 'ماشینی ۱۲۰۰ شانه طرح افشان', 'کاشان', 200, 300, 98000000, 25),
  ('KSH-M702', 'ماشینی ۱۰۰۰ شانه طرح هریس',  'کاشان', 150, 225, 54000000, 18);

SELECT LAST_INSERT_ID();   -- id اولین ردیفِ آخرین INSERT همین اتصال

-- کپی از یک جدول دیگر (مثلاً جدول موقت ورود از اکسل)
INSERT INTO carpets (sku, title, city, width_cm, length_cm, price)
SELECT sku, title, city, w, l, price FROM import_batch WHERE valid = 1;

UPDATE و DELETE با روال سه‌مرحله‌ای

1. اول همان WHERE را با SELECT اجرا کنید و تعداد ردیف‌ها را ببینید.
2. تراکنش باز کنید، UPDATE یا DELETE را اجرا کنید و «rows affected» را با عدد مرحله‌ی ۱ مقایسه کنید.
3. اگر درست بود COMMIT، وگرنه ROLLBACK.

SELECT COUNT(*) FROM carpets WHERE city = 'کاشان' AND sku LIKE 'KSH-M%';   -- 3

START TRANSACTION;
UPDATE carpets SET price = ROUND(price * 1.15, -5)      -- ۱۵٪ افزایش، گرد به صد هزار ریال
WHERE city = 'کاشان' AND sku LIKE 'KSH-M%';
-- Query OK, 3 rows affected
COMMIT;

-- حذف دسته‌ای جدول بزرگ: تکه‌تکه تا قفل و undo log سنگین نشود
DELETE FROM audit_log WHERE created_at < '2024-01-01' ORDER BY id LIMIT 5000;

دستور آخر را در یک حلقه تا وقتی «0 rows affected» شود تکرار کنید. حذف میلیون‌ها ردیف در یک دستور، تراکنشی عظیم می‌سازد که Replica را عقب می‌اندازد و ROLLBACK آن ممکن است ساعت‌ها طول بکشد.

Safe updates

با SET sql_safe_updates = 1 (یا گزینه‌ی safe-updates کلاینت) هر UPDATE یا DELETE که در WHERE از ستون کلیددار استفاده نکند و LIMIT هم نداشته باشد، با ERROR 1175 رد می‌شود. MySQL Workbench این گزینه را به‌طور پیش‌فرض روشن دارد.

sql_mode: سخت‌گیر باشید

SET SESSION sql_mode = 'TRADITIONAL';
INSERT INTO carpets (sku, title, city, width_cm, length_cm, price)
VALUES ('X-1', REPEAT('ا', 300), 'کاشان', 100, 150, 1);
-- ERROR 1406: Data too long for column 'title'
-- بدون حالت strict: فقط یک warning و متن بی‌صدا بریده می‌شد!

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

• MySQL به‌طور پیش‌فرض «rows affected» را برای ردیف‌هایی که واقعاً تغییر کرده‌اند می‌شمارد، نه ردیف‌های منطبق؛ UPDATE که مقدار را به همان مقدار قبلی می‌برد «0 rows affected» می‌گوید. پیام «Rows matched» را ببینید.
• بعد از INSERT چندردیفی، LAST_INSERT_ID() شناسه‌ی اولین ردیف را می‌دهد، نه آخرین؛ و مخصوص همان اتصال است، پس بین کاربران هم‌زمان قاطی نمی‌شود.
• TRUNCATE روی جدولی که کلید خارجی به آن اشاره می‌کند، حتی اگر جدول فرزند خالی باشد، خطا می‌دهد.
• در UPDATE چندستونی، MySQL انتسابات را از چپ به راست اعمال می‌کند: SET a = b, b = a هر دو را برابر b می‌کند، نه جابه‌جا (برخلاف استاندارد SQL و PostgreSQL).
• عملیات روی جدول بزرگ را با LIMIT تکه‌تکه کنید و بین تکه‌ها یک مکث کوتاه بگذارید تا Replicaها و بقیه‌ی کاربران نفس بکشند.