基于模式匹配的SQL表多字段实时前缀匹配搜索功能问询

Alright, let's break down how to build this suggestion system for your SQL table. You want users to type into a text box and get exact or prefix-matched terms from the Details, Keywords, and Name fields—all of which are MAX-length. Here's a practical, step-by-step approach:

First, we need to extract individual words from those text fields, clean up any punctuation, then match them against the user's input. Since your table has 1000 rows, performance won't be a huge issue, but we'll still keep things efficient.

1.1 Example for SQL Server #

This query cleans punctuation, splits text into words, and filters for exact/prefix matches:

DECLARE @SearchTerm NVARCHAR(100) = 'mach'; -- Replace with user input

SELECT DISTINCT LOWER(Word) AS Suggestion
FROM (
    -- Extract words from Name field
    SELECT TRIM(value) AS Word
    FROM YourTableName
    CROSS APPLY STRING_SPLIT(
        -- Strip common punctuation before splitting
        REPLACE(REPLACE(REPLACE(LOWER(Name), '.', ''), ',', ''), '!', ''),
        ' '
    )
    WHERE TRIM(value) <> ''

    UNION ALL

    -- Extract words from Keywords field
    SELECT TRIM(value) AS Word
    FROM YourTableName
    CROSS APPLY STRING_SPLIT(
        REPLACE(REPLACE(REPLACE(LOWER(Keywords), '.', ''), ',', ''), '!', ''),
        ' '
    )
    WHERE TRIM(value) <> ''

    UNION ALL

    -- Extract words from Details field
    SELECT TRIM(value) AS Word
    FROM YourTableName
    CROSS APPLY STRING_SPLIT(
        REPLACE(REPLACE(REPLACE(LOWER(Details), '.', ''), ',', ''), '!', ''),
        ' '
    )
    WHERE TRIM(value) <> ''
) AS AllWords
WHERE Word = @SearchTerm -- Exact match
   OR Word LIKE @SearchTerm + '%'; -- Prefix match

Notes: #

• LOWER() ensures case-insensitive matching (remove it if you need case sensitivity)
• DISTINCT avoids duplicate suggestions from repeated words across records
• We clean punctuation first so terms like "machine." don't show up as separate from "machine"

1.2 Example for MySQL #

MySQL doesn't have STRING_SPLIT, so we'll use a recursive approach to split text:

SET @SearchTerm = 'mach';

SELECT DISTINCT LOWER(word) AS Suggestion
FROM (
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(cleaned_text, ' ', n), ' ', -1) AS word
    FROM (
        -- Combine cleaned text from all three fields
        SELECT REPLACE(REPLACE(REPLACE(LOWER(Name), '.', ''), ',', ''), '!', '') AS cleaned_text FROM YourTableName
        UNION ALL
        SELECT REPLACE(REPLACE(REPLACE(LOWER(Keywords), '.', ''), ',', ''), '!', '') AS cleaned_text FROM YourTableName
        UNION ALL
        SELECT REPLACE(REPLACE(REPLACE(LOWER(Details), '.', ''), ',', ''), '!', '') AS cleaned_text FROM YourTableName
    ) AS texts
    -- Generate a number sequence to split words (adjust if you have longer text)
    JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) AS numbers
    ON CHAR_LENGTH(cleaned_text) - CHAR_LENGTH(REPLACE(cleaned_text, ' ', '')) >= n - 1
    WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(cleaned_text, ' ', n), ' ', -1) <> ''
) AS all_words
WHERE word = @SearchTerm OR word LIKE CONCAT(@SearchTerm, '%');

To make this work for users typing in a text box, we'll add real-time search with a debounce (to avoid spamming requests) and a simple API endpoint.

2.1 HTML #

<input type="text" id="searchInput" placeholder="Type to get suggestions...">
<ul id="suggestionsList"></ul>

2.2 JavaScript (Vanilla) #

const searchInput = document.getElementById('searchInput');
const suggestionsList = document.getElementById('suggestionsList');

// Debounce to wait 300ms after user stops typing before sending a request
const debounce = (func, delay) => {
  let timeoutId;
  return (...args) => {
    clearTimeout(timeoutId);
    timeoutId = setTimeout(() => func.apply(this, args), delay);
  };
};

// Fetch suggestions from your backend API
const getSuggestions = async (searchTerm) => {
  if (!searchTerm.trim()) {
    suggestionsList.innerHTML = '';
    return;
  }

  try {
    const response = await fetch(`/api/suggestions?term=${encodeURIComponent(searchTerm)}`);
    const suggestions = await response.json();
    
    // Render the suggestion list
    suggestionsList.innerHTML = suggestions.map(suggestion => 
      `<li>${suggestion}</li>`
    ).join('');
  } catch (err) {
    console.error('Failed to load suggestions:', err);
  }
};

// Bind the input event with debounce
searchInput.addEventListener('input', debounce((e) => {
  getSuggestions(e.target.value);
}, 300));

2.3 Example Backend Endpoint (C# ASP.NET Core) #

[ApiController]
[Route("api/[controller]")]
public class SuggestionsController : ControllerBase
{
    private readonly YourDbContext _dbContext;

    public SuggestionsController(YourDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    [HttpGet]
    public async Task<IActionResult> Get([FromQuery] string term)
    {
        if (string.IsNullOrEmpty(term)) return Ok(new List<string>());

        var query = @"
            SELECT DISTINCT LOWER(Word) AS Suggestion
            FROM (
                SELECT TRIM(value) AS Word
                FROM YourTableName
                CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(LOWER(Name), '.', ''), ',', ''), '!', ''), ' ')
                WHERE TRIM(value) <> ''
                UNION ALL
                SELECT TRIM(value) AS Word
                FROM YourTableName
                CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(LOWER(Keywords), '.', ''), ',', ''), '!', ''), ' ')
                WHERE TRIM(value) <> ''
                UNION ALL
                SELECT TRIM(value) AS Word
                FROM YourTableName
                CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(LOWER(Details), '.', ''), ',', ''), '!', ''), ' ')
                WHERE TRIM(value) <> ''
            ) AS AllWords
            WHERE Word = @Term OR Word LIKE @Term + '%'";

        var suggestions = await _dbContext.Suggestions
            .FromSqlRaw(query, new SqlParameter("@Term", term))
            .Select(s => s.Suggestion)
            .ToListAsync();

        return Ok(suggestions);
    }
}

Even with 1000 rows, these tweaks will make things smoother:

• Preprocess words: Create a dedicated SuggestionWords table and sync it nightly (via SQL Agent/Cron) with all cleaned words from your three fields. This avoids splitting text on every request.
• Add limits: Use TOP 10 (SQL Server) or LIMIT 10 (MySQL) to return only the most relevant suggestions instead of every match.
• Cache frequent searches: Store results for common terms in Redis or in-memory cache to reduce database hits.

Using your sample data:

This is automatic machine. That is macho man. This is use less machine. A Mach number is a measure of airspeed, and ...

• Typing mach returns: machine, macho
• Typing machine returns: machine

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