SQLite电影数据库设计：可变数量演员存储与多演员查询方案咨询

Hey there! Let's tackle this problem properly—your initial string concatenation approach has some annoying gotchas, so let's go with a standard relational database design that's way more robust for this scenario.

First, why you should ditch the string approach #

• The LIKE %actor1 query will accidentally match names like actor10 or actor123, leading to wrong results
• It's almost impossible to efficiently query for films that have multiple specific actors
• Maintenance is a headache: editing the actor list means splitting and re-combining strings, which is error-prone

The correct design: A 3-table relational model #

This is the standard way to handle many-to-many relationships (one film has multiple actors, one actor appears in multiple films) in relational databases like SQLite:

1. Create the table structure #

-- Films table: Stores basic movie info
CREATE TABLE films (
    film_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    release_year INTEGER,
    genre TEXT,
    -- Add other fields like director, runtime if needed
    UNIQUE(title, release_year) -- Prevent duplicate entries for the same movie
);

-- Actors table: Stores basic actor info
CREATE TABLE actors (
    actor_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    UNIQUE(name) -- Prevent duplicate entries for the same actor
);

-- Junction table: Links films to their actors
CREATE TABLE film_actors (
    film_id INTEGER NOT NULL,
    actor_id INTEGER NOT NULL,
    PRIMARY KEY(film_id, actor_id), -- Composite key to avoid duplicate links
    FOREIGN KEY(film_id) REFERENCES films(film_id) ON DELETE CASCADE,
    FOREIGN KEY(actor_id) REFERENCES actors(actor_id) ON DELETE CASCADE
);

The ON DELETE CASCADE clause means if you delete a film or actor, their associated links will be automatically removed—no manual cleanup needed.

2. Example data insertion #

-- Insert some films
INSERT INTO films (title, release_year, genre) VALUES
('The Shawshank Redemption', 1994, 'Drama'),
('Pulp Fiction', 1994, 'Crime'),
('Inception', 2010, 'Sci-Fi');

-- Insert some actors
INSERT INTO actors (name) VALUES
('Tim Robbins'),
('Morgan Freeman'),
('John Travolta'),
('Uma Thurman'),
('Leonardo DiCaprio');

-- Link films to actors
INSERT INTO film_actors (film_id, actor_id) VALUES
(1, 1), (1, 2), -- Shawshank has Tim and Morgan
(2, 3), (2, 4), -- Pulp Fiction has John and Uma
(3, 5); -- Inception has Leo

3. Query examples #

Single actor: Find all films Morgan Freeman was in

SELECT f.title, f.release_year
FROM films f
JOIN film_actors fa ON f.film_id = fa.film_id
JOIN actors a ON fa.actor_id = a.actor_id
WHERE a.name = 'Morgan Freeman';

Multiple actors: Find films that have both John Travolta and Uma Thurman

SELECT f.title, f.release_year
FROM films f
JOIN film_actors fa1 ON f.film_id = fa1.film_id
JOIN actors a1 ON fa1.actor_id = a1.actor_id AND a1.name = 'John Travolta'
JOIN film_actors fa2 ON f.film_id = fa2.film_id
JOIN actors a2 ON fa2.actor_id = a2.actor_id AND a2.name = 'Uma Thurman';

Flexible multi-actor: Find films that have any of the specified actors

SELECT DISTINCT f.title, f.release_year
FROM films f
JOIN film_actors fa ON f.film_id = fa.film_id
JOIN actors a ON fa.actor_id = a.actor_id
WHERE a.name IN ('Morgan Freeman', 'Leonardo DiCaprio');

Why this works better #

• No data redundancy: Actor names are stored once, not repeated in every film entry
• Accurate queries: No more accidental matches from fuzzy LIKE searches
• Easy to extend: You can add more fields to actors (like birth date) or films (like rating) without breaking existing data
• Faster queries: You can add indexes to the junction table's fields to speed up complex searches

This design fully meets your needs—whether you're filtering by one actor or multiple, it's reliable and scalable.

备注：内容来源于stack exchange，提问作者Abel Gutiérrez