JSON ל-SQL: הצהרות INSERT, תכנון טבלאות ומלכודות מיפוי טיפוסים

שורה תחתונה: החליטו איזו ישות עסקית מיוצגת על ידי שורה אחת לפני שמעצבים טבלאות, ואז כתבו VALUES מרובי-שורות מפורש בתוך טרנזקציה אחת; סגרו שמות שדות ומיפוי טיפוסים לפני הייבוא במקום לתקן אותם בתוך SQL.

JSON מתאר צורה; SQL מתאר גם אילוצים. כשמעבירים את הראשון לשני, כל אילוץ שלא כתבתם חוזר אחר כך כנתונים מלוכלכים.

1. החליטו קודם: טבלה אחת או רשומה אחת לכל שורת JSON

הסתכלו בשכבה החיצונית ביותר של הנתונים לפני עיצוב כלשהו של טבלה. החלטה שגויה כאן מחייבת לכתוב מחדש כל INSERT שאחריה.

יש רק קריטריון אחד: איזו ישות עסקית מיוצגת על ידי שורה אחת? אם שורה היא הזמנה, פריטי השורה חייבים לחיות בטבלה משלהם. אם שורה היא משתמש, תגים שייכים לטבלת בת או לעמודת JSON, לא למחרוזת מחוברת בפסיקים.

2. ממערך ל-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);

שלושה פרטים חשובים:

• כתבו כל שם עמודה. כשהסכימה מקבלת בהמשך עמודה, שום דבר לא מחליק לשדה שגוי.
• VALUES מרובי-שורות עובד ב-SQLite, PostgreSQL ו-MySQL, וחוסך מספר רב של סבבי רשת לעומת לולאה של הכנסות חד-שורתיות.
• NULL הוא מילה שמורה; אל תעטפו אותו בגרשיים. כתיבת 'NULL' מאחסנת מחרוזת בת ארבעה תווים.

אם מפתחות ה-JSON אינם תואמים לשמות העמודות (userId מול user_id), הוסיפו שכבת מיפוי. אל תנחשו עם COALESCE בתוך ה-SQL. תיקון שמות שדות בשכבת הנתונים זול הרבה יותר מתיקונם בשאילתות.

3. אובייקטים מקוננים: טבלת אב בתוספת טבלת בת

הזמנות הן המקרה הקלאסי של אחד-לרבים:

复制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 ושמרו את שתי ההכנסות בטרנזקציה אחת, אחרת האב יכול להתקיים בזמן שפריטיו חסרים.
• אם הנתונים הם לקריאה בלבד ואיש אינו מסנן לפי פריט שורה, עמודת JSON מקובלת (PostgreSQL jsonb, MySQL JSON, או SQLite TEXT עם פונקציות json1), אבל קבלו את המחיר: ללא אינדוקס ושאילתות מסורבלות.

שטחו אובייקטים מקוננים קבועים כמו user.address לעמודות (address_city, address_zip). פצלו לטבלה רק כשהקינון יכול להמשיך לגדול; פיצול של צורה קבועה רק מוסיף JOIN-ים.

4. NULL מול שדות חסרים: ל-SQL אין "מפתח נעדר"

JSON מאפשר שמפתח ייעדר לגמרי. עמודות SQL קבועות. אחרי ייבוא שני המקרים נראים זהים — NULL — למרות שהם יכולים להיות בעלי משמעות שונה:

שתי טעויות נפוצות:

1. שימוש במחרוזת ריקה במקום NULL. בהמשך, WHERE email IS NULL מחמיץ את השורות האלה, והמדד שלכם ל"משתמשים בלי אימייל" סופר בחסר בשקט.
2. בדיקה עם col = NULL. בלוגיקה התלת-ערכית של SQL, NULL = NULL מוערך ל-NULL, לא ל-TRUE. חייבים לכתוב IS NULL:

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

אם צריך לשמר אם מפתח היה קיים בכלל, הוסיפו עמודת סימון בוליאנית במקום להסיק זאת מ-NULL. גם חוזה ה-API צריך להצהיר על כך; ראו עיצוב API של JSON ושמות שדות.

5. מירכאות ו-escape: לעולם אל תחברו מחרוזות SQL

מחרוזות JSON יכולות להכיל גרשיים בודדים, גרשיים כפולים, קווים נטויים אחוריים, שורות חדשות ואפילו \u0000. עם מחרוזות שנבנו ביד, escape אחד שהוחמץ מסיט את שאר ההצהרה, ובמקרה הגרוע הופך להזרקת SQL.

ב-SQL תקני, גרש בודד עובר escape על ידי הכפלתו:

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

קוד פרודקשן צריך להשתמש בקשירת פרמטרים ולתת לדרייבר לטפל ב-escape ובטיפוסים:

复制// PostgreSQL משתמש ב-$1/$2, MySQL משתמש ב-?; הדוגמה הזו משתמשת ב-node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

בצעו escape ביד רק כשחייבים לייצא קובץ .sql, ואז לכל הפחות:

• החליפו ' ב-'';
• אם שורות חדשות נשמרות, אשרו שמסד הנתונים היעד מקבל literal של מחרוזת רב-שורתית;
• הסירו \u0000, מפני שרוב מסדי הנתונים דוחים NUL בתוך מחרוזות;
• MySQL מתייחס לקו נטוי אחורי כתו escape כברירת מחדל, ולכן \ חייב להפוך ל-\\, או עברו למצב תקני.

גרשיים כפולים מ-JSON אינם זקוקים ל-escape בתוך מחרוזת SQL העטופה בגרש בודד. הוספת שכבת escape נוספת היא הדרך שבה קווים נטויים אחוריים מיותרים מגיעים לנתונים שלכם.

6. מיפוי טיפוסים: היכן ששת ערכי JSON נוחתים ב-SQL

ל-JSON יש רק מחרוזת, מספר, בוליאני, null, אובייקט ומערך. ל-SQL יש מערכת טיפוסים מעודנת הרבה יותר. הטעויות קורות בדרך כלל בדיוק ובסמנטיקה ולא בתחביר:

בוליאניים מסתירים את הבאג העדין ביותר: false ב-JSON הופך ל-0 ב-TINYINT(1) של MySQL, סבבי הלוך ושוב נראים תקינים, ואיש לא מבחין. אבל ברגע שקטע קוד עושה if (value) על המחרוזת "false", היא מוערכת כאמת. סקריפטי המרה צריכים לקרוא במפורש ל-Boolean(value).

ה-JSONB של PostgreSQL מפענח ומסיר כפילויות של מפתחות, ושומר על המופע האחרון, מה שמתאים לשאילתות; JSON משמר את הטקסט המקורי ואת סדר המפתחות. ה-JSON של SQLite הוא רק TEXT בתוספת סט פונקציות, בלי אילוץ טיפוס אמיתי. לפני הבחירה, שאלו אם הטבלה הזו צריכה סינון על שדות בתוך ה-JSON.

7. הכנסות מרובות וטרנזקציות: אצוות, commit, והפיכה להרצה חוזרת

כשהנפח גדל, שלושה דברים קובעים אם ייבוא נשאר בשליטה:

1. אצווה. לולאה של N הצהרות INSERT חד-שורתיות עולה N סבבי רשת. שלבו 500 עד 1000 שורות ל-VALUES מרובה-שורות אחד, או השתמשו בטוען ההמוני של מסד הנתונים עצמו (PostgreSQL COPY, MySQL LOAD DATA, הכנסות מרובות-שורות בתוך טרנזקציה של SQLite).
2. השתמשו בטרנזקציה. הכול או כלום, כך שטבלה שנטענה חלקית לעולם לא שורדת שגיאה:

复制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;

3. הפכו אותה לאידמפוטנטית. סקריפטי ייבוא מורצים שוב כל הזמן. השתמשו ב-upsert במקום במחיקה ואז הכנסה:

ON CONFLICT דורש אילוץ ייחודיות או מפתח ראשי, אחרת הוא לעולם לא מופעל. כשאין מפתח טבעי, בנו אינדקס ייחודי על מפתח עסקי כמו email. כשאין מפתח בכלל, הוסיפו id יציב בצד ה-JSON — לעולם אל תישענו על id אוטו-אינקרמנטי לצורך אידמפוטנטיות, מפני שהרצה חוזרת תכניס כפילויות.

8. הבדלים בין דיאלקטים במבט אחד

כדי שקובץ SQL אחד יעבוד בכל השלושה, הימנעו לפחות מ-ON CONFLICT, מ-backticks ומ-SERIAL. בפרויקטים חוצי-מסדי-נתונים, ייצרו סכימה והצהרות upsert בנפרד; אל תצפו שסקריפט אחד יתאים לכולם.

9. שלוש בדיקות נקודתיות אחרי הייבוא

לפני מחיקת קובץ המקור, אמתו שלושה דברים:

1. מספר שורות. SELECT COUNT(*) FROM users; צריך להיות שווה לאורך מערך ה-JSON. שורה חסרה משמעה בדרך כלל שהרשומה האחרונה נחתכה.
2. העמודה עם שיעור ה-NULL הגבוה ביותר. זה מצביע על שמות מפתחות לא עקביים או קינון שלא הורחב. חזרו ובדקו אם המקור משתמש גם ב-userId וגם ב-user_id.
3. כסף ומזהים ארוכים. קחו את ערכי המקסימום והמינימום ואשרו ששום המרה של נקודה צפה או שלם לא חתכה אותם.

אם צריך קודם להבין את הצורה הטבלאית, השתמשו בגישת השטיחה בהמרת JSON, CSV ונתונים טבלאיים. אם הקהל הוא עמיתים שרק רוצים לקרוא את הנתונים, ארבע הדרכים להמיר JSON ל-Excel דורשות פחות עבודה מהקמת מסד נתונים.

למה מספרים מאבדים דיוק אחרי ייבוא JSON ל-SQL?

שלמים מעל 2^53 כבר איבדו דיוק בזמן JSON.parse של JavaScript, וכתיבתם ל-BIGINT אינה יכולה לשחזר אותו. מזהים כאלה צריכים להיות מחרוזות ב-JSON ולהמיר ל-BIGINT או ל-TEXT בזמן הייבוא.

מה להשתמש כמפתח ראשי כשאין id ב-JSON?

תנו למסד הנתונים ליצור אחד, או צרו UUID יציב לפני הייבוא. לעולם אל תשתמשו באינדקס המערך כ-id: הרצה חוזרת משנה את סדר הנתונים ומצביעה את השורות הלא נכונות על הרשומות הלא נכונות. אינדקס ייחודי על מפתח עסקי (אימייל, מספר הזמנה) גם מאפשר upserts.

מה עושים כשה-INSERT ההמוני נתקל בהתנגשות במפתח ייחודי?

החליפו את ה-INSERT הפשוט ב-upsert המתאים לדיאלקט שלכם, ואשרו שלעמודה המתנגשת יש אילוץ ייחודיות. מחיקה קודם והכנסה אחר כך גם עובדת, אבל היא חייבת לקרות בתוך טרנזקציה אחת ואתם מקבלים חלון קצר ללא שורות.

מערכים מקוננים תמיד צריכים טבלת בת?

לא. לתצוגה לקריאה בלבד בלי שאילתות לפי פריט משנה, עמודת JSON פשוטה יותר. ברגע שצריך לסנן או לקבץ לפי tag או SKU, טבלת בת עם אינדקסים היא הבחירה בת-הקיימא. הגורם המכריע הוא איך אתם שואלים, לא איך הנתונים מעוצבים.