ستونهای JSON در MySQL و MariaDB
ستونهای JSON در MySQL 5.7 (۲۰۱۵) و MariaDB 10.2 معرفی شدند و گامی مهم در جهت پشتیبانی از دادههای نیمهساختاریافته در یک پایگاه داده رابطهای سنتی برداشتند. این ویژگی به توسعهدهندگان اجازه میدهد تا اسناد JSON را در یک ستون جدول ذخیره، پرسوجو و دستکاری کنند، در حالی که انطباق با ACID و ابزارهای قوی MySQL را حفظ میکنند. ستونهای JSON به ویژه برای سناریوهایی مفید هستند که در آنها دادههای پویا ذخیره میشوند مانند تنظیمات کاربر، تنظیمات پیکربندی، فراداده یا گزارشهای رویداد که ساختارهای سنتی و محدود پاسخگو نیستند یا کارایی لازم را ندارند.
قبل از پشتیبانی بومی JSON، توسعهدهندگان اغلب JSON را به صورت TEXT یا BLOB سریالی میکردند و توانایی پرسوجو یا اعتبارسنجی کارآمد ساختار داخلی را از دست میدادند. جایگزینهایی مانند مدلهای Entity-Attribute-Value (EAV) یا جداول جداگانه key-value انعطافپذیری را ارائه میدادند، اما پیچیدگی و سربار عملکرد را افزایش میدادند.
مزایای استفاده از JSON در MySQL
ستونهای JSON مزایای متعددی ارائه میدهند. اعتبارسنجی داخلی تضمین میکند که فقط JSON معتبر ذخیره میشود. ذخیرهسازی دودویی بهینهشده عملکرد را بهبود میبخشد. همچنین توابع قدرتمندی مثل JSON_EXTRACT، JSON_CONTAINS و ستونهای تولید شده، نمایهسازی و پرسوجوی کارآمد را ممکن میسازند. علاوه بر این، MySQL از ستونهای مجازی و ذخیرهشده تولید شده بر اساس ویژگیهای JSON پشتیبانی میکند و امکان ایجاد شاخصهای ثانویه روی دادههای JSON را فراهم میکند.
مقایسه با پایگاه دادههای NoSQL
در حالی که پایگاههای داده NoSQL (مانند MongoDB) از ابتدا برای ذخیرهسازی اسناد ساخته شدهاند و انعطافپذیری ساختار و مقیاسپذیری افقی بیشتری را ارائه میدهند، پشتیبانی MySQL از JSON شکاف را برای برنامههایی که به یکپارچگی رابطهای و انعطافپذیری سند نیاز دارند، پر میکند. این ویژگی به تیمها اجازه میدهد تا در بسیاری از ابزارهای جانبی بینیاز شوند و کار توسعه را آسانتر پیش ببرند.
ایجاد ستونهای JSON
ایجاد ستونهای JSON به صورت زیر قابل انجام است. همچنین در نرمافزارهای جدید گرافیکی مدیریت پایگاه داده این قابلیتها پیادهسازی شدهاند.
|
1 2 3 4 5 |
CREATE TABLE table_name ( -- ... json_column_name JSON, -- ... ); |
خواندن کل اطلاعات یک ستون JSON
خواندن اطلاعات ذخیرهشده در یک ستون JSON مانند سایر ستونها به انجام میرسد و اجرای دستورالعملهای خاصی مورد نیاز نیست. پس از دریافت کدهای JSON تبدیل این اطلاعات به اطلاعات ساختیافته توسط ORM یا کدهای برنامهنویسی میتواند به انجام برسد.
ذخیرهسازی اطلاعات JSON در پایگاه داده
برای ذخیرهسازی اطلاعات از دو روش میتوان استفاده نمود. در روش اول به شکل سنتی، اطلاعات ساختیافته در سمت ORM یا به صورت دستی به رشته JSON تبدیل میشوند و در پایگاه داده ذخیره میشوند:
|
1 2 |
INSERT INTO table_name ( json_column_name ) VALUES ( '{"name" : "roka"}' ); |
در شکل دوم با استفاده از دستورات جدید JSON_OBJECT و JSON_ARRAY میتوانیم رشتههای JSON را در سمت پایگاه داده ایجاد کنیم:
|
1 2 |
INSERT INTO table_name ( json_column_name ) VALUES ( JSON_OBJECT( 'name', 'roka', 'code', 11 ) ); |
دستور JSON_OBJECT تعداد زوجی از پارامترها را دریافت میکند و به ترتیب آنها را به عنوان کلید و مقدار برای ساخت شیء JSON استفاده میکند.
با استفاده از دستور JSON_ARRAY میتوانیم اشیایی از نوع آرایه JSON ایجاد کنیم و آن را به تنهایی یا در ترکیب JSON_OBJECT استفاده نماییم.
|
1 |
SELECT JSON_ARRAY('ROKA', 'Webdesign', 2015, 'Tehran') AS company_info; |
قطعه کد بالا یک سلول JSON در قابل آرایه برمیگرداند که محتوی اطلاعات یک شرکت است. این اطلاعات میتواند در یک ستون JSON مستقیم ذخیرهسازی شود.
بهروزرسانی اطلاعات JSON
بهروزرسانی اطلاعات JSON با سه دستور JSON_INSERT و JSON_REPLACE و JSON_SET قابل انجام میباشد:
|
1 2 3 4 5 6 7 |
UPDATE products SET `attributes` = JSON_INSERT( `attributes` , '$.brand' , 'SAMSUNG' ) WHERE product_id = 10; |
همان طور که مشاهده میکنید با استفاده از حرف $ میتوانیم به ریشه JSON اشاره کنیم و سپس با نقطه به متغیرهای داخلی دسترسی پیدا کنیم.
نمونه کد برای تابع JSON_REPLACE جهت تغییر محتوای یک ستون JSON
|
1 2 3 4 5 6 7 |
UPDATE products SET `attributes` = JSON_REPLACE( `attributes` , '$.brand' , 'hTC' ) WHERE product_id = 10; |
نمونه کد برای تابع JSON_SET
|
1 2 3 4 5 6 7 |
UPDATE products SET `attributes` = JSON_SET( `attributes` , '$.brand' , 'Nokia' ) WHERE product_id = 10; |
تفاوت سه تابع JSON_INSERT و JSON_REPLACE و JSON_SET
- تابع JSON_INSERT اگر مقدار موجود نباشد، آن را اضافه میکند.
- تابع JSON_REPLACE اگر موجود باشد مقدار آن را تغییر میدهد.
- تابع JSON_SET در صورت وجود یا عدم وجود، مقدار مورد نظر را ذخیره میکند.
عبارتهای شرطی روی ستونهای JSON
برای جستجو روی ستونهای JSON با کمک تابع JSON_EXTRACT میتوانیم مقدار ستون دلخواه از JSON را استخراج کنیم:
|
1 2 3 |
SELECT * FROM products WHERE category_id = 1 AND JSON_EXTRACT( attributes , '$.brand' ) = 'SAMSUNG' |
مقایسه با NoSQL
به نظر میرسد هدف اصلی از ایجاد ستونهای JSON در MySQL به میدان آمدن سیستمهای پایگاه داده NoSQL مثل MongoDB باشد. در این نوع جدید از پایگاههای داده، امکان ذخیرهسازی دادههای غیرساختارمند فراهم است و در نتیجه انعطاف بیشتری در هنگام ذخیره و بازیابی اطلاعات وجود دارد. ولی دو مزیت دیگر پایگاهدادههای NoSQL در فیلدهای JSON هنوز وجود ندارد.
- کارایی زیاد در هنگام بهکارگیری حجم زیاد اطلاعات( Big Data یا کلان داده )
- امکانات گسترده در پردازش و تغییر اطلاعات غیرساختارمند
با وجود دو مشکل بالا، وجود ستونهای JSON در MySQL میتواند برای برخی کاربردها مفید و قابل توجه باشد و باعث شود استفاده از پایگاه دادههای NoSQL را حداقل به صورت موازی لازم نباشد.