在MariaDB中结合子查询使用FIND_IN_SET函数

Got it, let's break down how to build this query using subqueries and the FIND_IN_SET() function to get the client records you need.

The Query #

Here's the full SQL statement—just replace 'YourTargetPostalName' with the specific postal name you want to look up:

SELECT c.*
FROM clients c
WHERE FIND_IN_SET(
    -- Subquery to get the region ID linked to the target postal name
    (SELECT m.region_id
     FROM municipalities m
     INNER JOIN postals p 
         ON m.id = p.municipality_id
     WHERE p.name = 'YourTargetPostalName'),
    c.regions
) > 0;

How It Works #

Let's walk through each part to make sense of it:

• Subquery Layer: This part works backwards from the postal name to get the associated region ID:
  1. First, it finds the municipality_id in the postals table where the name matches your input.
  2. Then it joins with the municipalities table to pull the region_id tied to that municipality.
• FIND_IN_SET Check: The FIND_IN_SET() function checks if the region ID from the subquery exists in the comma-separated regions string in the clients table. If it finds a match, it returns a number greater than 0—so we use > 0 to filter only matching clients.

A Quick Note on Database Design #

While this query works for your current setup, storing region IDs as a comma-separated string in clients.regions isn't ideal for long-term scalability or query performance. A better approach would be to create a junction table (like client_regions) with client_id and region_id as foreign keys. This follows database normalization rules and makes queries faster and more maintainable.

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