نحوه استفاده از زمینه های داده JSON در پایگاه داده های MySQL

در مقاله من “SQL در مقابل NoSQL: تفاوتها” اشاره شد که مرز بین پایگاه داده های SQL و NoSQL به طور فزاینده ای تار شده است ، و هر اردوگاه از ویژگی های دیگری استفاده می کند. MySQL 5.7+ پایگاه داده های InnoDB و PostgreSQL 9.2+ هر دو به طور مستقیم از انواع سند JSON در یک زمینه واحد پشتیبانی می کنند. در این مقاله ، ما با جزئیات بیشتری پیاده سازی MySQL 8.0 JSON را بررسی خواهیم کرد.

توجه داشته باشید که هر پایگاه داده ای اسناد JSON را به عنوان یک لبه تک رشته ای قبول می کند. با این حال ، MySQL و PostgreSQL داده های JSON را به جای یک رشته اصلی ، در جفت های کلید / مقدار واقعی تأیید می کنند.

فقط به این دلیل که می توانید JSON را ذخیره کنید

… شما را دنبال نمی کند باید

نرمال سازی تکنیکی است که برای بهینه سازی ساختار پایگاه داده استفاده می شود. قانون اولین فرم عادی (1NF) حاکم است که هر ستون باید یک مقدار واحد داشته باشد – که با ذخیره سازی اسناد چند ارزشی JSON شکسته می شود.

اگر نیاز به داده های رابطه ای مشخص دارید ، از فیلدهای تک ارزش مناسب استفاده کنید. JSON باید به عنوان آخرین چاره کم مصرف شود. قسمتهای مقدار JSON قابل نمایه سازی نیستند ، بنابراین از استفاده از آن در ستونهایی که مرتباً به روز می شوند یا جستجو می شوند خودداری کنید. علاوه بر این ، برنامه های مشتری کمتری از JSON پشتیبانی می کنند و فناوری جدیدتر است ، بنابراین می تواند ثبات کمتری نسبت به انواع دیگر داشته باشد.

همانطور که گفته شد ، موارد استفاده خوب JSON برای داده های کم جمعیت یا ویژگی های سفارشی وجود دارد.

یک جدول با یک قسمت JSON ایجاد کنید

مغازه فروش کتاب را در نظر بگیرید. همه کتابها دارای شناسه ، شابک ، عنوان ، ناشر ، تعداد صفحات و سایر داده های رابطه ای واضح هستند. فرض کنید می خواهید هر تعداد برچسب دسته را به هر کتاب اضافه کنید. با استفاده از:

1. آ برچسب زدن جدولی که هر نام برچسب را با شناسه منحصر به فرد ذخیره می کند ، و
2. آ نقشه برچسب جدول دارای شناسه کتاب برای ثبت برچسب شناسه ها ، از بسیاری به بسیاری از سوابق

این کار می کند ، اما تلاش بی نظیر و قابل توجهی برای یک ویژگی جزئی است. بنابراین ، می توانید a را تعریف کنید برچسب ها قسمت JSON در پایگاه داده MySQL شما کتاب جدول:

CREATE TABLE `book` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

توجه داشته باشید که ستون های JSON نمی توانند مقدار پیش فرض داشته باشند ، به عنوان کلید اصلی استفاده شوند ، به عنوان کلید خارجی استفاده شوند و یا دارای فهرست باشند. شما می توانید ایجاد کنید شاخص های ثانویه در ستون های مجازی تولید شده، اما در صورت نیاز به نمایه ها ، حفظ یک مقدار در یک قسمت جداگانه آسان تر و عملی تر است.

افزودن داده های JSON

کلیه اسناد JSON قابل انتقال است INSERT یا به روز رسانی بیانیه. به عنوان مثال ، برچسب های کتاب ما می توانند به عنوان یک آرایه (درون یک رشته) منتقل شوند:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

JSON را می توان با این موارد نیز ایجاد کرد:

• تابع JSON_ARRAY ()، که آرایه ایجاد می کند. مثلا:

  
  SELECT JSON_ARRAY(1, 2, 'abc');
  

• تابع JSON_OBJECT ()، که اشیا ایجاد می کند. مثلا:

  
  SELECT JSON_OBJECT('a', 1, 'b', 2);
  

• عملکرد JSON_QUOTE ()، که یک رشته را به عنوان مقدار JSON نقل می کند. مثلا:

  
  SELECT JSON_QUOTE('[1, 2, "abc"]');
  

• یا شما می توانید (CAST anyValue AS JSON).

عملکرد JSON_TYPE () به شما امکان می دهد انواع مقدار JSON را بررسی کنید. این باید OBJECT ، ARRAY ، یک نوع اسکالر (INTEGER ، BOOLEAN و غیره) ، NULL یا یک خطا را برگرداند. مثلا:

SELECT JSON_TYPE('[1, 2, "abc"]');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

عملکرد JSON_VALID () اگر JSON معتبر باشد 1 یا در غیر این صورت 0 برمی گرداند:

SELECT JSON_TYPE('[1, 2, "abc"]');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

تلاش برای درج سند JSON نامعتبر خطایی ایجاد می کند و کل رکورد درج / به روز نمی شود.

جستجوی داده های JSON

عملکرد JSON_CONTAINS () سند JSON را در حال جستجو و دیگری را برای مقایسه در برابر می پذیرد. وقتی یک مسابقه پیدا شد 1 برمی گردد. مثلا:

SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

مشابه عملکرد JSON_SEARCH () مسیر را به مسابقه داده شده یا NULL بر می گرداند در صورت عدم مطابقت سند JSON در حال جستجو است ، 'one' برای پیدا کردن اولین مسابقه ، یا 'all' برای پیدا کردن همه موارد منطبق ، و یک رشته جستجو (در کجا) % با هر تعداد نویسه مطابقت دارد و _ با یک شخصیت به روشی یکسان مطابقت دارد LIKE) مثلا:

SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

مسیرهای JSON

یک مسیر JSON مقادیر را هدف قرار می دهد و می تواند برای استخراج یا اصلاح قسمتهایی از یک سند JSON مورد استفاده قرار گیرد. عملکرد JSON_EXTRACT () این را با استخراج یک یا چند مقدار نشان می دهد:

SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

تمام تعاریف مسیر با a شروع می شوند $ به دنبال انتخاب کنندگان دیگر:

• دوره ای به دنبال یک نام ، مانند $.website
• [N] که در آن N موقعیت در یک آرایه با شاخص صفر است
• .[*] wildcard تمام اعضای یک شی را ارزیابی می کند
• [*] wildcard تمام اعضای یک آرایه را ارزیابی می کند
• prefix**suffix wildcard تمام مسیرهایی را که با پیشوند نام برده شروع می شوند و با پسوند نامگذاری شده خاتمه می دهند ارزیابی می کند

مثالهای زیر به سند JSON زیر اشاره دارد:

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}

مسیرهای نمونه:

• $.a برمی گردد 1
• $.c برمی گردد [3, 4]
• $.c[1] برمی گردد 4
• $.d.e برمی گردد 5
• $**.e برمی گردد [5]

می توانید نام و اولین برچسب خود را استخراج کنید کتاب جدول با استفاده از پرس و جو:

SELECT
  title, tags->"$[0]" AS `tag1`
FROM `book`;

برای مثال پیچیده تر ، فرض کنید که یک کاربر جدول با داده های نمایه JSON. مثلا:

با استفاده از مسیر JSON می توانید نام توییتر را استخراج کنید. مثلا:

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;

شما می توانید از مسیر JSON در بند WHERE استفاده کنید تا فقط کاربران را با حساب Twitter بازگردانید:

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

اصلاح بخشی از سند JSON

چند وجود دارد توابع MySQL برای تغییر بخشهایی از یک سند JSON با استفاده از نمادگذاری مسیر. این شامل:

• JSON_SET(doc, path, val[, path, val]...): داده ها را در سند وارد یا به روز می کند
• JSON_INSERT(doc, path, val[, path, val]...): داده ها را در سند وارد می کند
• JSON_REPLACE(doc, path, val[, path, val]...): داده های موجود در سند را جایگزین می کند
• JSON_MERGE(doc, doc[, doc]...): دو یا چند سند را ادغام می کند
• JSON_ARRAY_APPEND(doc, path, val[, path, val]...): مقادیر را به انتهای آرایه اضافه می کند
• JSON_ARRAY_INSERT(doc, path, val[, path, val]...): آرایه ای را در سند وارد می کند
• JSON_REMOVE(doc, path[, path]...): داده ها را از سند حذف می کند

بنابراین می توانید برچسب “فنی” را به هر کتابی که قبلاً دارای برچسب “JavaScript” است اضافه کنید:

UPDATE `book`
  SET tags = JSON_MERGE(tags, '["technical"]')
WHERE
  JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

اطلاعات بیشتر

کتابچه راهنمای MySQL اطلاعات بیشتری در مورد نوع داده JSON و توابع JSON مرتبط.

باز هم ، من از شما می خواهم که از JSON استفاده نکنید ، مگر اینکه کاملاً ضروری باشد. شما می توانید از یک پایگاه داده NoSQL مستند گرا در MySQL الگوبرداری کنید ، اما بسیاری از مزایای SQL را نفی می کند و همچنین می توانید به یک سیستم NoSQL واقعی بروید! گفته شد ، انواع داده های JSON ممکن است باعث صرفه جویی در نیازهای مبهم داده های درون برنامه SQL شود.