تاریخ و زمان، JSON و ENUM: انتخاب‌هایی با دام‌های پنهان

DATETIME یا TIMESTAMP؟

مسئله‌ی 2038: TIMESTAMP عدد ثانیه از 1970 در ۳۲ بیت است و در 19 ژانویه‌ی 2038 سرریز می‌شود. قراردادهای اجاره‌ی ۱۵ساله یا اقساط بلندمدت همین حالا به آن برخورد می‌کنند. توصیه‌ی عملی: DATETIME استفاده کنید و قرار بگذارید همه‌ی مقادیر به UTC (یا همه به وقت تهران) ذخیره شوند؛ جنگو با USE_TZ = True خودش UTC ذخیره می‌کند.

CREATE TABLE orders (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                          ON UPDATE CURRENT_TIMESTAMP(3),
  deliver_on  DATE NULL
);

SET time_zone = '+00:00';  SELECT NOW();
SET time_zone = '+03:30';  SELECT NOW();   -- NOW تحت تأثیر time_zone اتصال است

JSON: انعطاف با حساب و کتاب

نوع JSON در MySQL به شکل دودویی و اعتبارسنجی‌شده ذخیره می‌شود. برای ویژگی‌های متغیر محصول (رنگ زمینه، رنگ حاشیه، نوع نخ) که برای هر دسته فرق دارد مناسب است؛ برای داده‌ای که در WHERE و JOIN مرتب استفاده می‌شود، ستون معمولی بهتر است.

ALTER TABLE carpets ADD attrs JSON NULL;
UPDATE carpets SET attrs = JSON_OBJECT('field_color', 'لاکی', 'yarn', 'پشم', 'colors', 8)
WHERE sku = 'KSH-1001';

SELECT sku, attrs->>'$.field_color' AS field_color      -- ->> یعنی مقدار بدون کوتیشن
FROM carpets WHERE attrs->>'$.yarn' = 'پشم';

-- ایندکس روی یک کلید JSON با ستون تولیدشده
ALTER TABLE carpets
  ADD field_color VARCHAR(30) AS (attrs->>'$.field_color') VIRTUAL,
  ADD INDEX ix_field_color (field_color);

ENUM: راحت، اما سفت

ENUM('draft','paid','shipped') فضای کمی می‌گیرد و مقدار نامعتبر را (در حالت strict) رد می‌کند، اما:

• درونی به‌صورت شماره‌ی ترتیب ذخیره می‌شود و ORDER BY status بر اساس ترتیب تعریف مرتب می‌کند، نه الفبا.
• افزودن مقدار به انتهای فهرست سریع است، اما درج در وسط یا تغییر نام، کل جدول را بازسازی می‌کند.
• در حالت غیر strict مقدار نامعتبر به رشته‌ی خالی با اندیس 0 تبدیل می‌شود.

جایگزین منعطف‌تر: VARCHAR(20) با CHECK (status IN (...)) یا یک جدول مرجع کوچک با کلید خارجی.

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

• برای TIMESTAMP با نام منطقه (مثل 'Asia/Tehran') باید جدول‌های timezone در دیتابیس mysql بارگذاری شده باشد: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql. image رسمی داکر این کار را هنگام راه‌اندازی اول خودش انجام می‌دهد.
• از 8.0.19 می‌توانید در لیترال زمان، offset بدهید: '2025-03-21 10:00:00+03:30'؛ MySQL آن را به time_zone اتصال تبدیل می‌کند.
• مقایسه‌ی attrs->'$.colors' = 8 با attrs->>'$.colors' = '8' فرق دارد؛ اولی مقایسه‌ی JSON و دومی مقایسه‌ی رشته است. در ایندکس و WHERE یکی را انتخاب و همه‌جا رعایت کنید.
• ایندکس چندمقداری (8.0.17) روی آرایه‌ی JSON: INDEX ((CAST(attrs->'$.tags' AS CHAR(30) ARRAY))) و جست‌وجو با MEMBER OF یا JSON_CONTAINS.
• در MariaDB نوع JSON فقط LONGTEXT با CHECK اعتبارسنجی است و عملگر ->> ندارد؛ کد مشترک باید از JSON_UNQUOTE(JSON_EXTRACT(...)) استفاده کند.