SQLite批量插入优化：避免硬编码外键引用的方案问询

Great question—dealing with repetitive foreign key lookups can get tedious fast, especially when you’re avoiding hardcoded IDs. Let’s walk through your options, from SQLite-native solutions to script/tool-based approaches:

1. SQLite-Native Solutions (No Extra Tables Needed) #

Your CONSTANTS table is a workaround for mapping category names to IDs, but you can skip it entirely by leveraging SQLite’s query capabilities directly.

Option 1a: Use a JOIN to Map Names to IDs on Insert #

This is my favorite approach—it lets you write insert statements using human-readable category names, and SQLite handles the ID lookup automatically:

INSERT INTO words(id_category, name)
SELECT c.id, w.word_name
FROM (
  -- List your words with their category names here
  VALUES 
    ('noun', 'hello'),
    ('abreviation', 'SO'),
    ('character', 'Hermione')
) AS w(category_name, word_name)
JOIN categories c ON c.name = w.category_name;

This eliminates the need for the CONSTANTS table entirely. Just update the VALUES clause with your word-category pairs, and the JOIN pulls in the correct foreign key ID.

Option 1b: Use a CTE to Pre-Fetch All Needed IDs #

If you prefer to reference IDs by alias (like your original CONSTANTS approach but cleaner), a Common Table Expression (CTE) lets you fetch all required IDs once and reuse them:

WITH category_ids AS (
  SELECT
    (SELECT id FROM categories WHERE name = 'noun') AS noun_id,
    (SELECT id FROM categories WHERE name = 'abreviation') AS abbr_id,
    (SELECT id FROM categories WHERE name = 'character') AS char_id
)
INSERT INTO words(id_category, name)
VALUES
  ((SELECT noun_id FROM category_ids), 'hello'),
  ((SELECT abbr_id FROM category_ids), 'SO'),
  ((SELECT char_id FROM category_ids), 'Harry');

This keeps your insert lines clean and avoids repeated CONSTANTS table queries.

2. Sed/Shell Scripting for Placeholder Replacement #

If you want to keep your insert statements readable with placeholders (like {{category_noun}}), you can use sed to replace them with actual IDs before running the SQL.

First, create a template file (insert_words_template.sql):

INSERT INTO words(id_category, name) VALUES
  ({{category_noun}}, 'hello'),
  ({{category_abreviation}}, 'SO'),
  ({{category_character}}, 'Ron');

Then use a shell script to fetch IDs and replace placeholders:

# Fetch IDs from the database
NOUN_ID=$(sqlite3 your_database.db "SELECT id FROM categories WHERE name='noun'")
ABBR_ID=$(sqlite3 your_database.db "SELECT id FROM categories WHERE name='abreviation'")
CHAR_ID=$(sqlite3 your_database.db "SELECT id FROM categories WHERE name='character'")

# Replace placeholders and run the SQL
sed -e "s/{{category_noun}}/$NOUN_ID/g" \
    -e "s/{{category_abreviation}}/$ABBR_ID/g" \
    -e "s/{{category_character}}/$CHAR_ID/g" \
    insert_words_template.sql | sqlite3 your_database.db

This works well for one-off scripts but is less maintainable than SQL-native approaches if your data changes frequently.

3. Programmatic Handling (Best for Scalable Projects) #

If you’re using a programming language to interact with SQLite (Python, Node.js, etc.), this is the most flexible approach. You can pre-fetch a category name-to-ID map once, then reuse it for all inserts:

Example with Python: #

import sqlite3

# Connect to the database
conn = sqlite3.connect("your_database.db")
cursor = conn.cursor()

# Build a category name → ID map
category_map = {}
cursor.execute("SELECT name, id FROM categories")
for name, cat_id in cursor.fetchall():
    category_map[name] = cat_id

# Insert words using the map
words_to_insert = [
    (category_map["noun"], "hello"),
    (category_map["abreviation"], "SO"),
    (category_map["character"], "Dumbledore")
]
cursor.executemany("INSERT INTO words(id_category, name) VALUES (?, ?)", words_to_insert)

# Commit changes and close the connection
conn.commit()
conn.close()

This approach is clean, scalable, and easy to maintain—especially if you’re dealing with large datasets or frequent updates.

Recommendation #

If you want to stick purely to SQLite, go with the JOIN-based insert (Option 1a)—it’s the most concise and eliminates the need for the CONSTANTS table entirely. For projects with more complex logic, programmatic handling is the way to go. Sed is fine for quick one-offs but not ideal for long-term use.

内容的提问来源于stack exchange，提问作者Zermingore