JSON به SQL: دستورهای INSERT، طراحی جدول و تله‌های نگاشت نوع

جمع‌بندی: پیش از طراحی جدول‌ها تصمیم بگیرید یک سطر نماینده کدام موجودیت کسب‌وکار است، سپس VALUES صریح چندسطری را درون یک تراکنش بنویسید؛ نام‌گذاری فیلدها و نگاشت نوع را پیش از ورود تثبیت کنید نه اینکه در SQL وصله بزنید.

JSON شکل را توصیف می‌کند؛ SQL قیدها را هم توصیف می‌کند. وقتی اولی را به دومی منتقل می‌کنید، هر قیدی که ننوشته‌اید بعداً به‌صورت داده کثیف برمی‌گردد.

۱. نخست تصمیم بگیرید: یک جدول یا یک رکورد برای هر سطر JSON

پیش از طراحی هر جدولی به بیرونی‌ترین لایه داده نگاه کنید. این تصمیم را اشتباه بگیرید و هر INSERT بعدی باید بازنویسی شود.

تنها یک معیار وجود دارد: یک سطر نماینده کدام موجودیت کسب‌وکار است؟ اگر یک سطر یک سفارش است، اقلام باید در جدول خودشان زندگی کنند. اگر یک سطر یک کاربر است، tagها به جدول فرزند یا ستون JSON تعلق دارند، نه به یک رشته با ویرگول به هم پیوسته.

۲. از آرایه به INSERT: کوچک‌ترین قالب قابل اجرا

امن‌ترین شکل همیشه نام ستون‌ها را صریح فهرست می‌کند و هرگز به ترتیب ستون‌ها تکیه نمی‌کند:

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

سه جزئیات اهمیت دارد:

• هر نام ستون را بنویسید. وقتی schema بعداً ستونی می‌گیرد، چیزی به فیلد اشتباه جابه‌جا نمی‌شود.
• VALUES چندسطری در SQLite، PostgreSQL و MySQL کار می‌کند و در مقایسه با حلقه INSERTهای تک‌سطری، رفت‌وبرگشت‌های زیادی صرفه‌جویی می‌کند.
• NULL یک کلمه کلیدی است؛ آن را در گیومه نگذارید. نوشتن 'NULL' یک رشته چهارکاراکتری ذخیره می‌کند.

اگر کلیدهای JSON با نام ستون‌ها نمی‌خوانند (userId در برابر user_id)، یک لایه نگاشت بیفزایید. با COALESCE درون SQL حدس نزنید. اصلاح نام فیلدها در لایه داده بسیار ارزان‌تر از وصله زدن در پرس‌وجوهاست.

۳. objectهای تودرتو: جدول والد به‌همراه جدول فرزند

سفارش‌ها مورد کلاسیک یک‌به‌چندند:

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

این معادل JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]} است.

نکات کلیدی:

• طول آرایه items تعداد سطرهای فرزند را تعیین می‌کند؛ جدول والد دقیقاً یک سطر می‌گیرد.
• پول را به‌صورت سنت صحیح (total_cents) ذخیره کنید، هرگز به‌صورت FLOAT.
• به جدول فرزند یک کلید خارجی order_id بدهید و هر دو insert را در یک تراکنش ثبت کنید، وگرنه والد می‌تواند وجود داشته باشد در حالی که اقلامش غایب‌اند.
• اگر داده فقط‌خواندنی است و کسی بر اساس قلم فیلتر نمی‌کند، ستون JSON قابل قبول است (PostgreSQL jsonb، MySQL JSON یا SQLite TEXT با توابع json1)، اما هزینه را بپذیرید: بدون ایندکس و پرس‌وجوهای دشوار.

objectهای تودرتوی ثابت مانند user.address را در ستون‌ها تخت کنید (address_city، address_zip). فقط وقتی به جدول بشکنید که تودرتویی بتواند مدام رشد کند؛ شکستن یک شکل ثابت فقط JOIN اضافه می‌کند.

۴. NULL در برابر فیلد غایب: SQL «کلید غایب» ندارد

JSON اجازه می‌دهد یک کلید کاملاً غایب باشد. ستون‌های SQL ثابت‌اند. پس از ورود هر دو حالت یکسان به نظر می‌رسند — NULL — هرچند می‌توانند معنای متفاوتی داشته باشند:

دو اشتباه رایج:

۱. استفاده از رشته خالی به‌جای NULL. بعداً WHERE email IS NULL آن سطرها را از دست می‌دهد و شاخص «کاربران بدون ایمیل» شما بی‌صدا کم‌شماری می‌کند. ۲. آزمون با col = NULL. در منطق سه‌مقداری SQL، NULL = NULL به NULL ارزیابی می‌شود نه TRUE. باید IS NULL بنویسید:

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

اگر باید حفظ کنید که آیا کلید اصلاً وجود داشته یا نه، به‌جای استنتاج از NULL یک ستون نشانگر بولین بیفزایید. قرارداد API هم باید همین را بگوید؛ طراحی API JSON و نام‌گذاری فیلد را ببینید.

۵. نقل‌قول و escape: هرگز رشته‌های SQL را به هم نچسبانید

رشته‌های JSON می‌توانند تک‌گیومه، گیومه دوتایی، بک‌اسلش، خط جدید و حتی \u0000 داشته باشند. با رشته‌های دست‌ساز، یک escape جاافتاده بقیه دستور را جابه‌جا می‌کند و در بدترین حالت به تزریق SQL تبدیل می‌شود.

در SQL استاندارد، تک‌گیومه با دو برابر کردن escape می‌شود:

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

کد محیط عملیاتی باید از اتصال پارامتر استفاده کند و بگذارد درایور escape و نوع‌ها را مدیریت کند:

复制// PostgreSQL uses $1/$2, MySQL uses ?; this example uses node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

فقط وقتی باید یک فایل .sql صادر کنید دستی escape کنید و آن هم حداقل:

• ' را با '' جایگزین کنید؛
• اگر خطوط جدید حفظ می‌شوند، تأیید کنید پایگاه‌داده هدف لیترال‌های رشته چندخطی را می‌پذیرد؛
• \u0000 را حذف کنید، چون بیشتر پایگاه‌داده‌ها NUL را درون رشته‌ها رد می‌کنند؛
• MySQL به‌طور پیش‌فرض بک‌اسلش را کاراکتر escape می‌داند، پس \ باید \\ شود یا به حالت استاندارد تغییر دهید.

گیومه‌های دوتایی JSON درون رشته تک‌گیومه‌ای SQL نیازی به escape ندارند. افزودن یک لایه escape اضافه همان راهی است که بک‌اسلش‌های سرگردان در داده شما ظاهر می‌شوند.

۶. نگاشت نوع: شش مقدار JSON کجای SQL فرود می‌آیند

JSON فقط رشته، عدد، بولین، null، object و array دارد. SQL نظام نوع بسیار ریزتری دارد. اشتباهات معمولاً در دقت و معنا رخ می‌دهند نه در نحو:

بولین‌ها ظریف‌ترین باگ را پنهان می‌کنند: false در JSON در TINYINT(1) MySQL به ۰ تبدیل می‌شود، رفت‌وبرگشت درست به نظر می‌رسد و کسی متوجه نمی‌شود. اما لحظه‌ای که کدی if (value) را روی رشته "false" اجرا کند، آن را truthy ارزیابی می‌کند. اسکریپت‌های تبدیل باید صریحاً Boolean(value) را صدا بزنند.

JSONB در PostgreSQL کلیدها را تجزیه و یکتا می‌کند و آخرین تکرار را نگه می‌دارد، که برای پرس‌وجو مناسب است؛ JSON متن اصلی و ترتیب کلیدها را حفظ می‌کند. JSON در SQLite فقط TEXT به‌همراه مجموعه‌ای از توابع است، بدون قید نوع واقعی. پیش از انتخاب بپرسید آیا این جدول به فیلتر روی فیلدهای درون JSON نیاز دارد.

۷. درج انبوه و تراکنش: دسته‌بندی، commit و قابل‌اجرای دوباره کنید

با رشد حجم، سه چیز تعیین می‌کند ورود داده قابل کنترل بماند یا نه:

۱. دسته‌بندی. حلقه‌ای از N دستور INSERT تک‌سطری N رفت‌وبرگشت شبکه هزینه دارد. ۵۰۰ تا ۱۰۰۰ سطر را در یک VALUES چندسطری ترکیب کنید یا از بارگذار انبوه خود پایگاه‌داده استفاده کنید (COPY در PostgreSQL، LOAD DATA در MySQL، insert چندسطری درون یک تراکنش SQLite). ۲. از تراکنش استفاده کنید. همه یا هیچ، تا جدول نیمه‌بار‌شده هرگز پس از خطا زنده نماند:

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

۳. آن را idempotent کنید. اسکریپت‌های ورود مدام دوباره اجرا می‌شوند. به‌جای حذف-سپس-درج از upsert استفاده کنید:

ON CONFLICT به یک قید یکتا یا کلید اصلی نیاز دارد، وگرنه هرگز فعال نمی‌شود. وقتی کلید طبیعی نیست، روی یک کلید کسب‌وکار مانند email ایندکس یکتا بسازید. وقتی هیچ کلیدی نیست، در سمت JSON یک id پایدار بیفزایید — هرگز برای idempotency به id خودافزا تکیه نکنید، چون اجرای دوباره رکوردهای تکراری درج می‌کند.

۸. تفاوت گویش‌ها در یک نگاه

برای اینکه یک فایل SQL در هر سه کار کند، حداقل از ON CONFLICT، بک‌تیک و SERIAL پرهیز کنید. برای پروژه‌های چندپایگاه‌داده‌ای، schema و دستورهای upsert را جداگانه تولید کنید؛ انتظار نداشته باشید یک اسکریپت به همه بخورد.

۹. سه بررسی نمونه پس از ورود

پیش از حذف فایل منبع سه چیز را تأیید کنید:

۱. تعداد سطر. SELECT COUNT(*) FROM users; باید برابر طول آرایه JSON باشد. یک سطر جاافتاده معمولاً یعنی رکورد آخر بریده شده است. ۲. ستون با بالاترین نرخ NULL. این به نام‌های کلید ناهمگون یا تودرتویی گسترش‌نیافته اشاره می‌کند. برگردید و ببینید منبع هم userId و هم user_id استفاده می‌کند. ۳. پول و شناسه‌های بلند. بیشترین و کمترین مقدار را بردارید و تأیید کنید نه تبدیل ممیز شناور و نه عدد صحیح آن‌ها را نبریده است.

اگر نخست باید شکل جدولی را بفهمید، از رویکرد تخت‌سازی در تبدیل JSON، CSV و داده جدولی استفاده کنید. اگر مخاطب همکارانی هستند که فقط می‌خواهند داده را بخوانند، چهار راه تبدیل JSON به Excel کم‌دردسرتر از برپا کردن پایگاه‌داده است.

چرا عددها پس از وارد کردن JSON به SQL دقت را از دست می‌دهند؟

عددهای صحیح بزرگ‌تر از 2^53 از پیش در JSON.parse جاوااسکریپت دقت را از دست داده‌اند و نوشتن آن‌ها در BIGINT نمی‌تواند آن را بازگرداند. چنین idهایی باید در JSON رشته باشند و هنگام ورود به BIGINT یا TEXT تبدیل شوند.

وقتی JSON هیچ id ندارد از چه چیزی به‌عنوان کلید اصلی استفاده کنم؟

بگذارید پایگاه‌داده یکی تولید کند، یا پیش از ورود یک UUID پایدار بسازید. هرگز از ایندکس آرایه به‌عنوان id استفاده نکنید: اجرای دوباره داده را دوباره مرتب می‌کند و سطرهای اشتباه را به رکوردهای اشتباه اشاره می‌دهد. ایندکس یکتا روی یک کلید کسب‌وکار (ایمیل، شماره سفارش) upsert را هم ممکن می‌کند.

وقتی INSERT انبوه به تعارض کلید یکتا می‌خورد چه کنم؟

INSERT ساده را با upsert متناظر گویش خود جایگزین کنید و تأیید کنید ستون متعارض قید یکتا دارد. حذف اول و درج بعد هم کار می‌کند، اما باید درون یک تراکنش رخ دهد و پنجره‌ای کوتاه بدون سطر را می‌پذیرید.

آیا آرایه‌های تودرتو همیشه به جدول فرزند نیاز دارند؟

نه. برای نمایش فقط‌خواندنی بدون پرس‌وجو بر اساس زیرقلم، ستون JSON ساده‌تر است. به‌محض اینکه به فیلتر یا تجمیع بر اساس tag یا SKU نیاز پیدا کنید، جدول فرزند با ایندکس انتخاب پایدار است. عامل تعیین‌کننده نحوه پرس‌وجوی شماست، نه شکل داده.