SQLite INSERT: הוספת שורות, הכנסה המונית ו-OR IGNORE

INSERT מוסיף שורות לטבלה

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

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    author TEXT,
    year   INTEGER
);

INSERT INTO books (title, author, year)
VALUES ('The Left Hand of Darkness', 'Ursula K. Le Guin', 1969);

SELECT * FROM books;

שלושה חלקים לשים לב אליהם:

• INSERT INTO books: טבלת היעד.
• (title, author, year): העמודות שאתם מספקים להן ערכים.
• VALUES (...): הערכים, באותו סדר כמו ברשימת העמודות.

id לא ברשימת העמודות, ולכן SQLite מקצה אותו אוטומטית (זה INTEGER PRIMARY KEY, שמקבל את ה-rowid). כל עמודה שמדלגים עליה מקבלת את ערך ברירת המחדל שלה, או NULL אם אין כזה.

תמיד פרטו את העמודות

אפשר להשמיט את רשימת העמודות ולספק ערכים לכל עמודה לפי סדר ההצהרה:

-- עובד, אבל שביר:
INSERT INTO books VALUES (NULL, 'Dune', 'Frank Herbert', 1965);

אל תעשו את זה. ברגע שמישהו מוסיף עמודה ל-books, כל פקודה כזו נשברת או מתחילה להכניס ערכים לעמודה הלא נכונה. כתבו את העמודות במפורש:

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    author TEXT,
    year   INTEGER
);

INSERT INTO books (title, author, year)
VALUES ('Dune', 'Frank Herbert', 1965);

SELECT id, title, year FROM books;

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

הכנסה של כמה שורות

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

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    author TEXT,
    year   INTEGER
);

INSERT INTO books (title, author, year) VALUES
    ('Dune', 'Frank Herbert', 1965),
    ('Neuromancer', 'William Gibson', 1984),
    ('Hyperion', 'Dan Simmons', 1989);

SELECT count(*) AS total FROM books;

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

הכנסה המונית: עטפו אותה בטרנזקציה

כברירת מחדל, כל INSERT הוא טרנזקציה בפני עצמו. SQLite מבצעת fsync בסוף כל אחד, וזה מה שהופך לולאות תמימות לאיטיות, לא ההכנסות עצמן.

קבצו אותן:

CREATE TABLE numbers (n INTEGER);

BEGIN;
INSERT INTO numbers (n) VALUES (1);
INSERT INTO numbers (n) VALUES (2);
INSERT INTO numbers (n) VALUES (3);
INSERT INTO numbers (n) VALUES (4);
INSERT INTO numbers (n) VALUES (5);
COMMIT;

SELECT count(*) AS total, sum(n) AS total_sum FROM numbers;

fsync אחד במקום חמישה. באלפי שורות ההבדל יכול להגיע לשניים או שלושה סדרי גודל. אם משהו בפנים נכשל, ROLLBACK מבטל את כל האצווה.

התבנית הזו היא המתכון להכנסה המונית. השתמשו בה בין אם אתם קוראים ל-SQLite מ-Python, מ-Node או מ-Rust: עטפו את הלולאה ב-BEGIN / COMMIT.

INSERT ... SELECT: העתקה מטבלה אחרת

אפשר למלא טבלה משאילתה במקום מערכים מפורשים:

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    year   INTEGER
);
INSERT INTO books (title, year) VALUES
    ('Dune', 1965), ('Neuromancer', 1984), ('Hyperion', 1989);

CREATE TABLE classics (title TEXT, year INTEGER);

INSERT INTO classics (title, year)
SELECT title, year FROM books WHERE year < 1980;

SELECT * FROM classics;

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

DEFAULT VALUES ועמודות שמדלגים עליהן

אם לעמודה יש פסוקית DEFAULT, אפשר להשאיר אותה מחוץ לרשימת העמודות ו-SQLite תמלא את ברירת המחדל:

CREATE TABLE events (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
);

INSERT INTO events (name) VALUES ('signup');
INSERT INTO events (name) VALUES ('login');

SELECT * FROM events;

created_at מקבלת את חותמת הזמן הנוכחית כי לא סיפקנו אחת. אם רוצים שורה שכולה ערכי ברירת מחדל, שימושי לשורות ממלאות מקום, השתמשו בצורה DEFAULT VALUES:

CREATE TABLE counters (
    id    INTEGER PRIMARY KEY,
    value INTEGER DEFAULT 0
);

INSERT INTO counters DEFAULT VALUES;
INSERT INTO counters DEFAULT VALUES;

SELECT * FROM counters;

שתי שורות חדשות, בשתיהן value = 0 ומזהים שהוקצו אוטומטית.

INSERT OR IGNORE: דילוג על כפילויות

כששורה הייתה מפרה אילוץ UNIQUE או PRIMARY KEY, התנהגות ברירת המחדל היא לעצור את הפקודה עם שגיאה:

Error: UNIQUE constraint failed: users.email

INSERT OR IGNORE מחליף את זה ב"דלג בשקט על השורה הבעייתית":

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

INSERT INTO users (email) VALUES ('ada@example.com');

INSERT OR IGNORE INTO users (email) VALUES
    ('ada@example.com'),    -- כפילות, מדולגת
    ('boris@example.com'),  -- מוכנסת
    ('rosa@example.com');   -- מוכנסת

SELECT * FROM users;

שלוש שורות שורדות. הכפילות נשמטת בלי שגיאה. זו הדרך האידיומטית ב-SQLite לבטא "הכנס אם לא קיים" לנתוני seed פשוטים: בלי SELECT נפרד לבדיקה מראש, בלי טיפול בחריגות.

INSERT OR REPLACE: דריסת כפילויות

INSERT OR REPLACE מוחק את השורה המתנגשת ומכניס את החדשה במקומה:

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value TEXT
);

INSERT INTO settings (key, value) VALUES ('theme', 'light');

INSERT OR REPLACE INTO settings (key, value) VALUES ('theme', 'dark');

SELECT * FROM settings;

שימו לב לדבר אחד: REPLACE הוא DELETE + INSERT, לא UPDATE. אם לשורה שנמחקה הצביעו מפתחות זרים עם ON DELETE CASCADE, גם הבנים האלה נמחקים. וכל עמודה שלא מפרטים ב-INSERT החדש מתאפסת לברירת המחדל שלה: היא לא שומרת את הערך הישן.

ברוב המקרים של "עדכן אם קיים, הכנס אם לא", מה שאתם באמת רוצים הוא upsert אמיתי עם ON CONFLICT ... DO UPDATE. יש לו עמוד משלו.

סיכום מהיר

• INSERT INTO table (cols) VALUES (...): הצורה הבסיסית. תמיד פרטו עמודות.
• הכנסה של כמה שורות משתמשת ברשומות מופרדות בפסיקים אחרי VALUES.
• לטעינה המונית אמיתית, עטפו את ההכנסות ב-BEGIN / COMMIT.
• INSERT INTO ... SELECT ... מעתיק שורות משאילתה.
• DEFAULT VALUES יוצר שורה מערכי ברירת מחדל בלבד; גם עמודות שמדלגים עליהן מקבלות את ברירות המחדל שלהן.
• INSERT OR IGNORE מדלג על שורות מתנגשות; INSERT OR REPLACE דורס אותן (באמצעות מחיקה + הכנסה).

הצעד הבא: UPDATE

הכנסת שורות היא חצי מהסיפור. החצי השני הוא שינוי שורות שכבר קיימות: הגדלת מונה, תיקון שגיאת כתיב, סימון הזמנה כנשלחה. זה UPDATE, ויש לו סט הרגלים משלו שכדאי לעשות נכון (במיוחד פסוקית ה-WHERE). בעמוד הבא.