ستونهای JSON در MySQL و MariaDB
سیستمهای پایگاه داده رابطهای یا سنتی برای ذخیرهسازی اطلاعات از ساختارهای از پیشتعریف شده و جدولی استفاده میکنند. به این معنی که برای ذخیرهسازی هر نوع داده میبایست ساختار آن را به صورت ردیفها و ستونهای مشخص درآوریم.
از طرف دیگر در سیستمهای پایگاه دادهی جدید NoSQL، این محدودیتها وجود ندارد و برنامهنویس تنها اطلاعات را ذخیره میکند.
به دلیل استقبال زیادی که از مفاهیم جدید NoSQL میان برنامهنویسان به وجود آمده است، سیستمهای رابطهای قدیمی نیز در تلاش هستند تا ویژگیهای جدید را پیادهسازی کنند.
از آن جمله، MySQL و MariaDB فرمت جدید JSON را برای ستونهای جدول ایجاد کردهاند که با کمک آن میتوان هر نوع دادهای را با هر نوع ساختاری در این ستونها ذخیره سازی نمود.
جهت استفاده از ستونهای جدید JSON لازم است تا سرور MySQL نگارش ۵.۷ و MariaDB نگارش ۱۰.۲ به بعد را نصب و استفاده نمایید.
ایجاد ستونهای 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 استفاده نماییم.
بهروزرسانی اطلاعات 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 را حداقل به صورت موازی لازم نباشد.