MySQL中substring_index()函数返回结果异常问题排查求助

Hey there! Let's break down why your query is returning empty results and fix it up.

The Root Cause #

Looking at your hangar table data, the hangarlocation values for NSW locations look like 'Sydney, NSW' — notice the space after the comma? When you use SUBSTRING_INDEX(hangarlocation, ',', -1), it extracts everything after the last comma, which includes that leading space. So you're trying to compare ' NSW' (with a space) to 'NSW' (without a space), which don't match. That's why no rows are returned!

Fixes for Your Query #

Here are two straightforward ways to fix this:

1. Trim the Whitespace

Wrap the SUBSTRING_INDEX result in TRIM() to remove leading/trailing spaces:

SELECT s.*, h.hangarlocation
FROM service s
JOIN hangar h ON s.hangarid = h.hangarid
WHERE TRIM(SUBSTRING_INDEX(h.hangarlocation, ',', -1)) = 'NSW';

2. Adjust the Comparison to Include the Space

If you prefer not to use TRIM(), you can match the exact string with the space:

SELECT s.*, h.hangarlocation
FROM service s
JOIN hangar h ON s.hangarid = h.hangarid
WHERE SUBSTRING_INDEX(h.hangarlocation, ',', -1) = ' NSW';

Bonus: Use Explicit JOIN Syntax #

I also swapped out the old comma-separated table syntax for an explicit JOIN — this makes your query more readable and helps avoid accidental cross-joins down the line. It's a best practice in modern SQL!

When you run either of these corrected queries, you'll get all the service details for aircraft that used NSW-based hangars, which is exactly what you wanted.

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