Type Affinity ב-SQLite: איך טיפוסי עמודות מתנהגים באמת

Affinity הוא העדפה, לא כלל

SQLite הוא בעל טיפוסיות דינמית. ערך נושא איתו את סוג האחסון שלו (NULL, INTEGER, REAL, TEXT, BLOB), והטיפוס שהוגדר לעמודה לא מגביל באופן קשיח מה אפשר להכניס אליה. מה שהטיפוס המוגדר כן עושה הוא לתת לעמודה affinity: סוג אחסון מועדף ש-SQLite מנסה להמיר אליו ערכים נכנסים.

שימו לב מה קורה כש-affinity לא מספיק כדי לעצור אי התאמה:

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'apple');
INSERT INTO items VALUES ('two', 'banana');

SELECT id, typeof(id), name FROM items;

השורה השנייה שומרת את המחרוזת 'two' בעמודת INTEGER. SQLite ניסה להמיר את 'two' למספר, לא הצליח (זה לא מספרי), ושמר אותו בכל זאת כ-TEXT. typeof() חושף את סוג האחסון האמיתי של כל ערך, והוא לא תמיד מה שהגדרת העמודה מרמזת.

זה מפתיע אנשים שמגיעים מ-Postgres או מ-MySQL. זה בכוונה.

חמשת סוגי ה-Affinity

כל עמודה בטבלה שאינה STRICT מקבלת בדיוק אחד מאלה:

• TEXT: מעדיף מחרוזות.
• NUMERIC: מעדיף מספרים, אבל מקבל טקסט אם אי אפשר להמיר.
• INTEGER: כמו NUMERIC, אבל שומר ערכים בלי חלק שברי כמספרים שלמים.
• REAL: מעדיף מספרים עשרוניים (נקודה צפה).
• BLOB: בלי העדפה, שומר כל מה שנותנים לו.

ל-affinity מסוג BLOB קוראים גם "ללא affinity": זה מה שמקבלים כשלא מגדירים טיפוס בכלל.

CREATE TABLE samples (
    a,                 -- BLOB affinity (no type declared)
    b TEXT,            -- TEXT
    c INTEGER,         -- INTEGER
    d REAL,            -- REAL
    e NUMERIC          -- NUMERIC
);

INSERT INTO samples VALUES ('42', '42', '42', '42', '42');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM samples;

אותו קלט, המחרוזת '42', וחמישה טיפוסים שמורים שונים. כל עמודה המירה (או לא) לפי ה-affinity שלה.

איך SQLite בוחר Affinity מתוך ההגדרה שלכם

הנה החלק שמכשיל אנשים: ל-SQLite אין רשימה קבועה של טיפוסים "חוקיים". אפשר לכתוב כמעט כל דבר אחרי שם עמודה, ו-SQLite מסיק את ה-affinity על ידי סריקת הטקסט ומציאת תת מחרוזות, בסדר הזה:

1. מכיל INT → INTEGER
2. מכיל CHAR, CLOB או TEXT → TEXT
3. מכיל BLOB, או שאין טיפוס בכלל → BLOB
4. מכיל REAL, FLOA או DOUB → REAL
5. כל דבר אחר → NUMERIC

זה כל האלגוריתם. הוא מסביר הרבה מוזרויות:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT  (contains CHAR)
    b BIGINT,             -- INTEGER (contains INT)
    c DOUBLE PRECISION,   -- REAL (contains DOUB)
    d DECIMAL(10,2),      -- NUMERIC (matches none of the above)
    e BOOLEAN,            -- NUMERIC (matches none of the above)
    f DATETIME,           -- NUMERIC (matches none of the above)
    g FLOATING_POINTS,    -- INTEGER (contains INT - surprise!)
    h GIBBERISH           -- NUMERIC (matches none of the above)
);

SELECT name, type FROM pragma_table_info('quirks');

FLOATING_POINTS הופך ל-INTEGER כי תת המחרוזת INT מופיעה ב-POINTS. הכלל הראשון שמתאים מנצח, מלמעלה למטה. זו הסיבה שהעתקה עיוורת של טיפוסים ממסד נתונים אחר יכולה לתת משהו שונה ממה שציפיתם.

Affinity בפעולה: המרות בזמן הכנסה

Affinity חשוב במיוחד כש-SQLite מחליט אם להמיר את הערך שלכם או לשמור אותו כמו שהוא. הכללים:

• affinity מסוג TEXT: מספרים ו-BLOBים מומרים לטקסט.
• affinity מסוג NUMERIC, INTEGER, REAL: טקסט שנראה כמו מספר מומר. טקסט שלא נראה כך נשאר טקסט.
• affinity מסוג BLOB: שום דבר לא מומר.

CREATE TABLE conv (
    n NUMERIC,
    t TEXT,
    b BLOB
);

INSERT INTO conv VALUES ('123', 456, 789);
INSERT INTO conv VALUES ('12.5', 7.5, 'hello');
INSERT INTO conv VALUES ('hello', 'world', 'world');

SELECT n, typeof(n), t, typeof(t), b, typeof(b) FROM conv;

שורה אחר שורה:

• '123' בעמודת NUMERIC הופך למספר השלם 123. ההמרה מטקסט למספר הצליחה ולא איבדה מידע.
• '12.5' הופך למספר העשרוני 12.5.
• 'hello' ב-NUMERIC נשאר טקסט: אין מספר להמיר אליו.
• עמודת ה-TEXT ממירה מספרים לצורת המחרוזת שלהם.
• עמודת ה-BLOB שומרת הכול בדיוק כמו שניתן, כולל הטיפוס.

הקמט של INTEGER מול REAL

affinity מסוג INTEGER מתנהג כמעט זהה ל-NUMERIC, עם שינוי אחד: ערך כמו 3.0 שאין לו חלק שברי אמיתי נשמר כמספר השלם 3 כדי לחסוך מקום.

CREATE TABLE nums (
    i INTEGER,
    n NUMERIC
);

INSERT INTO nums VALUES (3.0, 3.0);
INSERT INTO nums VALUES (3.5, 3.5);

SELECT i, typeof(i), n, typeof(n) FROM nums;

3.0 נוחת כ-INTEGER בשתי העמודות: האופטימיזציה קורית גם ב-NUMERIC. 3.5 שומר על החלק השברי ונשאר REAL. המסקנה: אל תסמכו על typeof() כדי לדעת אם עמודה הוגדרה INTEGER או REAL. הוא אומר מה באמת שמור, וזה יכול להשתנות משורה לשורה.

כש-Affinity נושך אתכם

הגמישות נוחה עד שהיא לא. שני סוגי כשלים מופיעים בקוד אמיתי:

1. נתונים גרועים מתגנבים פנימה. אם יש באפליקציה שלכם באג ששולח 'N/A' לעמודת INTEGER, SQLite ישמור אותו. שאילתות מאוחרות יותר שעושות חשבון על העמודה יחזירו תוצאות מוזרות או NULL. בלי שגיאה, בלי אזהרה, רק השחתה שקטה.

2. השוואות נהיות מוזרות. מיון ובדיקות שוויון מתייחסים אחרת לערכים מסוגי אחסון שונים:

CREATE TABLE mixed (val INTEGER);
INSERT INTO mixed VALUES (10), (2), ('100'), ('20'), (3);

SELECT val, typeof(val) FROM mixed ORDER BY val;

מספרים שלמים ממוינים מספרית, ואז ערכי טקסט ממוינים לקסיקוגרפית, והם ממוינים אחרי כל המספרים. אז מקבלים 2, 3, 10 (המספרים השלמים בסדר מספרי), ואז '20', '100' (המחרוזות בסדר אלפביתי). לא מה שרוב האנשים רוצים.

אם אתם שולטים בהכנסות ומאמתים בקפידה, טבלאות רגילות הן בסדר. אם לא, או שאתם פשוט רוצים שמסד הנתונים יאכוף טיפוסים בשבילכם, יש אפשרות טובה יותר.

הבא בתור: טבלאות STRICT

SQLite 3.37 הוסיף טבלאות STRICT, שמכבות את ה-affinity ודוחות ערכים שלא תואמים לטיפוס המוגדר. הן נותנות את הטיפוסיות הדינמית כברירת מחדל כשאתם רוצים אותה, ואכיפה בסגנון Postgres כשלא. זה הדף הבא.