JSON و Full-Text Search

۹.۱ JSON در MySQL – چه زمانی استفاده کنیم؟

MySQL از 5.7 نوع JSON بومی دارد. اما این به معنی استفاده از آن برای همه‌چیز نیست.

JSON مناسب است وقتی:

• داده schema-less است (مثل ویژگی‌های متفاوت محصول‌ها در دسته‌های مختلف)
• تنظیمات کاربر یا config اپلیکیشن
• متادیتای flexible
• API response cache

JSON مناسب نیست وقتی:

• داده schema ثابت دارد (نام، ایمیل، قیمت)
• روی فیلدها زیاد JOIN می‌شود
• پرکوئری روی فیلد خاص (مگر با Generated Column + Index)

۹.۲ ساخت و درج JSON

SQL📋 کپی
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    attributes JSON,
    INDEX idx_title (title)
);

-- درج
INSERT INTO products (title, attributes) VALUES
('گوشی موبایل سامسونگ',
 '{"brand": "Samsung", "ram": 8, "storage": 256, "colors": ["مشکی", "نقره‌ای"], "price_history": [{"date": "2026-01-01", "price": 25000000}]}'),

('فرش دستباف کاشان',
 '{"material": "ابریشم", "size": "۲×۳", "knot_count": 600, "origin": "کاشان"}');

-- ساخت با تابع‌ها
INSERT INTO products (title, attributes) VALUES (
    'لپ‌تاپ',
    JSON_OBJECT(
        'brand', 'Asus',
        'cpu', 'Intel i7',
        'ram', 16,
        'features', JSON_ARRAY('SSD', 'Backlit Keyboard', 'Touchscreen')
    )
);

۹.۳ خواندن از JSON

SQL📋 کپی
-- JSON_EXTRACT - شکل کامل
SELECT
    title,
    JSON_EXTRACT(attributes, '$.brand') AS brand,
    JSON_EXTRACT(attributes, '$.ram') AS ram
FROM products;

-- اپراتور -> (مساوی JSON_EXTRACT)
SELECT title, attributes->'$.brand' AS brand FROM products;

-- اپراتور ->> (مساوی JSON_UNQUOTE(JSON_EXTRACT(...)))
-- خروجی بدون "" دور رشته
SELECT title, attributes->>'$.brand' AS brand FROM products;
-- "Samsung"  vs  Samsung

-- مسیر آرایه
SELECT title, attributes->>'$.colors[0]' AS first_color FROM products;

-- مسیر nested
SELECT
    title,
    attributes->>'$.price_history[0].price' AS first_price
FROM products;

-- WHERE روی JSON
SELECT * FROM products WHERE attributes->>'$.brand' = 'Samsung';
SELECT * FROM products WHERE attributes->'$.ram' >= 8;
SELECT * FROM products WHERE JSON_CONTAINS(attributes->'$.colors', '"مشکی"');

۹.۴ تغییر JSON

SQL📋 کپی
-- JSON_SET: تنظیم یا اضافه کردن
UPDATE products
SET attributes = JSON_SET(attributes, '$.warranty', '۱۸ ماه')
WHERE id = 1;

-- JSON_INSERT: فقط اگر key وجود نداشته باشد
UPDATE products
SET attributes = JSON_INSERT(attributes, '$.weight', 200)
WHERE id = 1;

-- JSON_REPLACE: فقط اگر key وجود داشته باشد
UPDATE products
SET attributes = JSON_REPLACE(attributes, '$.ram', 16)
WHERE id = 1;

-- JSON_REMOVE: حذف key
UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.warranty')
WHERE id = 1;

-- اضافه کردن به آرایه
UPDATE products
SET attributes = JSON_ARRAY_APPEND(attributes, '$.colors', '"طلایی"')
WHERE id = 1;

-- ادغام دو JSON
UPDATE products
SET attributes = JSON_MERGE_PATCH(attributes, '{"new_field": "value"}')
WHERE id = 1;

۹.۵ ایندکس‌گذاری JSON با Generated Column

JSON به‌تنهایی قابل index نیست. اما می‌توانیم یک ستون مجازی یا STORED بسازیم و آن را index کنیم:

SQL📋 کپی
-- روش ۱: Generated Column STORED + Index
ALTER TABLE products
  ADD COLUMN brand VARCHAR(50) AS (attributes->>'$.brand') STORED,
  ADD INDEX idx_brand (brand);

-- حالا این Query سریع است:
SELECT * FROM products WHERE brand = 'Samsung';

-- روش ۲: MySQL 8.0+ Functional Index مستقیم
CREATE INDEX idx_brand_func ON products ((CAST(attributes->>'$.brand' AS CHAR(50))));

-- روش ۳: Multi-Valued Index برای آرایه (MySQL 8.0+)
CREATE INDEX idx_colors ON products ((CAST(attributes->'$.colors' AS CHAR(50) ARRAY)));

-- این Query از ایندکس استفاده می‌کند:
SELECT * FROM products WHERE 'مشکی' MEMBER OF (attributes->'$.colors');

۹.۶ JSON_TABLE – تبدیل JSON به جدول رابطه‌ای

یکی از قدرتمندترین قابلیت‌های JSON در MySQL 8:

SQL📋 کپی
-- فرض: ستون orders.items_json حاوی آرایه آیتم‌ها است
SELECT
    o.id AS order_id,
    items.product_id,
    items.qty,
    items.price
FROM orders o,
JSON_TABLE(
    o.items_json,
    '$[*]' COLUMNS (
        product_id INT PATH '$.product_id',
        qty INT PATH '$.qty',
        price BIGINT PATH '$.price'
    )
) AS items;

-- نتیجه: یک ردیف برای هر آیتم در آرایه
-- مثل LEFT JOIN با child table

۹.۷ Full-Text Search – جستجوی متنی

برای جستجوی کلمات در متن طولانی (مثل عنوان مقاله، توضیحات محصول):

SQL📋 کپی
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content MEDIUMTEXT,
    FULLTEXT INDEX ft_search (title, content)
) ENGINE=InnoDB;

-- Natural Language Mode (پیش‌فرض)
SELECT id, title FROM articles
WHERE MATCH(title, content) AGAINST('فرش دستباف کاشان');

-- Boolean Mode (دقیق‌تر، پشتیبانی operator)
SELECT id, title FROM articles
WHERE MATCH(title, content)
      AGAINST('+فرش +دستباف -ماشینی' IN BOOLEAN MODE);
-- + اجباری، - منع، * wildcard، "..." عبارت دقیق

-- مرتب‌سازی بر اساس امتیاز ربط
SELECT
    id, title,
    MATCH(title, content) AGAINST('فرش دستباف') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('فرش دستباف')
ORDER BY score DESC
LIMIT 20;

-- Query Expansion (پیدا کردن مرتبط‌ها)
SELECT id, title FROM articles
WHERE MATCH(title, content)
      AGAINST('فرش' WITH QUERY EXPANSION);

۹.۸ Full-Text فارسی با ngram Parser

Full-Text پیش‌فرض MySQL برای زبان‌های با فاصله بین کلمات (انگلیسی) طراحی شده. برای فارسی، عربی و… پارسر ngram بهتر است:

SQL📋 کپی
-- بررسی پارسرهای موجود
SHOW PLUGINS;

-- ساخت ایندکس با ngram
CREATE TABLE persian_articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content MEDIUMTEXT,
    FULLTEXT INDEX ft_search (title, content) WITH PARSER ngram
) ENGINE=InnoDB;

-- اندازه ngram (پیش‌فرض ۲)
SHOW VARIABLES LIKE 'ngram_token_size';
-- در my.cnf:
-- ngram_token_size = 2

-- جستجو
SELECT * FROM persian_articles
WHERE MATCH(title, content) AGAINST('فرش' IN BOOLEAN MODE);

چرا ngram برای فارسی بهتر است؟

• کلمات را به ۲-حرف‌های متوالی می‌شکند: «فرش» → «فر»، «رش»
• Stop word‌های انگلیسی روی متن فارسی اعمال نمی‌شوند
• پشتیبانی از substring search

۹.۹ ترکیب Full-Text با شرایط دیگر

SQL📋 کپی
-- جستجو + فیلتر دسته‌بندی + قیمت
SELECT
    id, title, price,
    MATCH(title, description) AGAINST('فرش ابریشم') AS relevance
FROM products
WHERE MATCH(title, description) AGAINST('فرش ابریشم' IN BOOLEAN MODE)
  AND category_id = 5
  AND price BETWEEN 1000000 AND 10000000
  AND is_active = 1
ORDER BY relevance DESC, price ASC
LIMIT 24;

-- نکته: ایندکس Full-Text روی title,description
-- ایندکس B-Tree روی (category_id, is_active, price)
-- MySQL هر دو را استفاده می‌کند

تنظیمات مهم Full-Text

SQL📋 کپی
-- حداقل طول کلمه (پیش‌فرض ۳)
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
-- در my.cnf:
-- innodb_ft_min_token_size = 2

-- حداکثر طول کلمه
SHOW VARIABLES LIKE 'innodb_ft_max_token_size';

-- لیست stop word‌ها
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

-- تغییر تنظیمات نیاز به rebuild ایندکس دارد:
-- ALTER TABLE ... DROP INDEX ft_search; ADD FULLTEXT INDEX ...;

۹.۱۰ مثال جامع: محصولات با ویژگی‌های دینامیک

SQL📋 کپی
-- جدول محصولات با attributes JSON
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) UNIQUE,
    title VARCHAR(255),
    description MEDIUMTEXT,
    category_id INT UNSIGNED,
    price BIGINT UNSIGNED,
    attributes JSON,
    -- استخراج فیلدهای پرکاربرد به عنوان generated
    brand VARCHAR(50) AS (attributes->>'$.brand') STORED,
    color VARCHAR(30) AS (attributes->>'$.color') STORED,
    INDEX idx_cat_brand (category_id, brand),
    INDEX idx_color (color),
    FULLTEXT INDEX ft_search (title, description) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- جستجوی کامل
SELECT id, title, price, attributes
FROM products
WHERE MATCH(title, description) AGAINST('فرش ابریشم' IN BOOLEAN MODE)
  AND brand = 'کاشان'
  AND price BETWEEN 5000000 AND 50000000
  AND JSON_CONTAINS_PATH(attributes, 'one', '$.knot_count')
  AND CAST(attributes->>'$.knot_count' AS UNSIGNED) >= 500
ORDER BY MATCH(title, description) AGAINST('فرش ابریشم') DESC
LIMIT 24;

۹.۱۱ خلاصه فصل

• JSON برای داده‌های schema-less مناسب است، نه جایگزین جدول‌بندی
• اپراتور ->> برای استخراج بدون quote
• برای ایندکس روی JSON از Generated Column STORED استفاده کنید
• JSON_TABLE برای تبدیل آرایه JSON به ردیف‌های رابطه‌ای
• Full-Text با MATCH AGAINST، Boolean Mode انعطاف بیشتری می‌دهد
• برای فارسی، پارسر ngram با token_size=2 توصیه می‌شود
• برای پروژه‌های جستجومحور بزرگ، Elasticsearch جایگزین بهتر است