MySQL用户表全文检索后按指定姓名组合规则排序需求

Got it, let's tackle this problem step by step. You need to use MySQL's full-text indexing to search for records related to "Ramiz Murtaza" and sort them in your specified custom order. Here's how to make it work:

Step 1: Create the Full-Text Index #

First, you'll need a combined full-text index on the first_name and last_name columns. This lets MySQL efficiently run full-text searches across both fields:

ALTER TABLE users 
ADD FULLTEXT INDEX ft_users_names (first_name, last_name);

Step 2: Write the Search & Custom Sort Query #

Next, use MATCH() AGAINST() to filter relevant records, then a CASE statement in the ORDER BY clause to enforce your exact sorting priority:

SELECT first_name, last_name
FROM users
WHERE MATCH(first_name, last_name) AGAINST('Ramiz Murtaza' IN BOOLEAN MODE)
ORDER BY 
  CASE
    WHEN first_name = 'Ramiz' AND last_name = 'Murtaza' THEN 1
    WHEN first_name = 'Murtaza' AND last_name = 'Ramiz' THEN 2
    WHEN first_name = 'Ramiz' AND last_name = 'Xyz' THEN 3
    WHEN first_name = 'Murtaza' AND last_name = 'Xyz' THEN 4
    WHEN first_name = 'Xyz' AND last_name = 'Ramiz' THEN 5
    WHEN first_name = 'Xyz' AND last_name = 'Murtaza' THEN 6
    ELSE 7 -- Catch any other matching records (if they exist)
  END ASC;

Breakdown of How This Works: #

• Full-Text Search: The MATCH() AGAINST('Ramiz Murtaza' IN BOOLEAN MODE) clause finds all records where either first_name or last_name contains "Ramiz" or "Murtaza". Using IN BOOLEAN MODE gives flexibility in matching terms—here, it acts like an OR between the two keywords, which aligns with your need to include all name combinations involving either term.
• Custom Sorting: The CASE statement assigns a numerical priority to each of your required name pairs. Lower numbers mean higher priority, so records are sorted exactly in the order you requested:
  1. Ramiz,Murtaza (highest priority)
  2. Murtaza,Ramiz
  3. Ramiz,Xyz
  4. Murtaza,Xyz
  5. Xyz,Ramiz
  6. Xyz,Murtaza
  7. Any other matching records (will appear last)

Optional: Restrict to Records with Both Keywords #

If you only want records that include both "Ramiz" and "Murtaza" (excluding the Xyz combinations), adjust the AGAINST clause with boolean operators:

AGAINST('+Ramiz +Murtaza' IN BOOLEAN MODE)

This will only return the top two priority groups (Ramiz,Murtaza and Murtaza,Ramiz).

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