MySQL JSON_EXTRACT无结果排查及电影标题JSON搜索实现问询

Hey there! Let's dig into why your JSON_EXTRACT isn't returning results and get that multi-keyword movie title search working smoothly.

一、先排查JSON_EXTRACT失效的核心原因 #

First things first, let's rule out the common issues that break JSON field queries:

• Check your field type: Is your movie_data column set to a proper JSON type? If it's stored as TEXT or VARCHAR, MySQL still supports JSON functions—but you need to make sure the stored JSON is 100% valid (double quotes around keys/strings, no syntax errors).
• Verify your JSON path: To target the movie_title value, your path needs to start with $ (the root of the JSON object). A lot of people skip this and wonder why nothing comes back!

Here's a working single-keyword query to test:

-- Basic version with JSON_EXTRACT
SELECT id, movie_data
FROM movies
WHERE JSON_EXTRACT(movie_data, '$.movie_title') LIKE '%Forrest%';

-- Simplified shorthand (MySQL 5.7.13+ / MariaDB 10.2.3+)
-- ->> is equivalent to JSON_UNQUOTE(JSON_EXTRACT()), removes extra quotes
SELECT id, movie_data
FROM movies
WHERE movie_data->>'$.movie_title' LIKE '%Forrest%';

If your movie_data is a TEXT column, the shorthand ->> is especially useful—it strips the surrounding quotes from the extracted value so your LIKE match works as expected.

二、实现多关键词同时搜索 #

Now let's tackle splitting user input into keywords and making sure all (or any) of them match the movie title.

1. Split keywords in your backend (example with PHP) #

First, take the user's search input, split it by spaces, and filter out empty strings (in case of multiple spaces):

$searchInput = trim($_POST['search_query']);
$keywords = array_filter(explode(' ', $searchInput)); // Split and clean up

2. Build a dynamic SQL query #

Next, create a WHERE clause that checks each keyword against the movie title. Use AND if you want all keywords to appear in the title, or OR if any match is enough.

$conditions = [];
foreach ($keywords as $keyword) {
    // Escape to prevent SQL injection (critical!)
    $safeKeyword = $pdo->quote("%{$keyword}%");
    $conditions[] = "movie_data->>'$.movie_title' LIKE {$safeKeyword}";
}

// Join conditions with AND (all keywords must match)
$sql = "SELECT id, movie_data FROM movies WHERE " . implode(' AND ', $conditions);

// Execute the query with your database connection...

If you want to test directly in SQL (e.g., searching for "Forrest Gump"):

SELECT id, movie_data
FROM movies
WHERE movie_data->>'$.movie_title' LIKE '%Forrest%'
AND movie_data->>'$.movie_title' LIKE '%Gump%';

Swap AND for OR if you want to return movies that match any of the keywords instead.

三、Pro tips for better performance #

If you have a lot of movie entries, searching directly in JSON fields can get slow. Here's how to fix that:

-- Add a stored virtual column that extracts movie_title
ALTER TABLE movies
ADD COLUMN movie_title VARCHAR(255) GENERATED ALWAYS AS (movie_data->>'$.movie_title') STORED;

-- Create an index on the new column
CREATE INDEX idx_movie_title ON movies(movie_title);

Now you can search directly on movie_title instead of the JSON field—way faster, especially with large datasets!

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