MySQL邻近搜索结果的PHP字符串高亮：求解起始定位方法

Got it, let's work through this problem. The key challenge here is pinpointing the start position of your proximity regex match so you can wrap the entire matched segment in your highlight formatting (like <strong> or custom tags). Here's a step-by-step approach using MySQL's built-in string/regex functions:

1. Use REGEXP_INSTR() to Get Exact Match Positions #

MySQL 8.0+ has REGEXP_INSTR(), which returns the starting index of a regex match (and can also give you the end position with a parameter tweak). This is exactly what you need since you already have the end position covered.

Key Parameters for REGEXP_INSTR(): #

• REGEXP_INSTR(column, pattern, start, match_number, return_option, match_type)
  • return_option = 0: Returns the start position of the match
  • return_option = 1: Returns the position after the end of the match
  • match_type = 'i': Makes the match case-insensitive (critical for your examples where query terms are lowercase but text has uppercase)

2. Full SQL Example for Your First Case #

Let's apply this to your first example:

• Query regex: work.{0,10}john.{0,10}smith
• Target text: "I have been working for John Joe Smith for the last 5 years"

SELECT
  columnA AS original_text,
  -- Get start position of the match
  REGEXP_INSTR(columnA, 'work.{0,10}john.{0,10}smith', 1, 1, 0, 'i') AS highlight_start,
  -- Calculate end position (subtract 1 since return_option=1 gives position after the match)
  REGEXP_INSTR(columnA, 'work.{0,10}john.{0,10}smith', 1, 1, 1, 'i') - 1 AS highlight_end,
  -- Build the highlighted text
  CONCAT(
    -- Text before the match
    LEFT(columnA, REGEXP_INSTR(columnA, 'work.{0,10}john.{0,10}smith', 1, 1, 0, 'i') - 1),
    -- Highlight opening tag
    '<strong>',
    -- The matched segment itself
    SUBSTRING(
      columnA,
      REGEXP_INSTR(columnA, 'work.{0,10}john.{0,10}smith', 1, 1, 0, 'i'),
      REGEXP_INSTR(columnA, 'work.{0,10}john.{0,10}smith', 1, 1, 1, 'i') - REGEXP_INSTR(columnA, 'work.{0,10}john.{0,10}smith', 1, 1, 0, 'i')
    ),
    -- Highlight closing tag
    '</strong>',
    -- Text after the match
    RIGHT(columnA, LENGTH(columnA) - (REGEXP_INSTR(columnA, 'work.{0,10}john.{0,10}smith', 1, 1, 1, 'i') - 1))
  ) AS highlighted_text
FROM myTable
WHERE columnA REGEXP 'work.{0,10}john.{0,10}smith';

This will output:
I have been <strong>working for John Joe Smith</strong> for the last 5 years

3. Example for Your Second Case #

Just swap out the regex pattern for your second example (jo.{0,10}ba.{0,10}tur):

SELECT
  columnA AS original_text,
  REGEXP_INSTR(columnA, 'jo.{0,10}ba.{0,10}tur', 1, 1, 0, 'i') AS highlight_start,
  REGEXP_INSTR(columnA, 'jo.{0,10}ba.{0,10}tur', 1, 1, 1, 'i') - 1 AS highlight_end,
  CONCAT(
    LEFT(columnA, REGEXP_INSTR(columnA, 'jo.{0,10}ba.{0,10}tur', 1, 1, 0, 'i') - 1),
    '<strong>',
    SUBSTRING(
      columnA,
      REGEXP_INSTR(columnA, 'jo.{0,10}ba.{0,10}tur', 1, 1, 0, 'i'),
      REGEXP_INSTR(columnA, 'jo.{0,10}ba.{0,10}tur', 1, 1, 1, 'i') - REGEXP_INSTR(columnA, 'jo.{0,10}ba.{0,10}tur', 1, 1, 0, 'i')
    ),
    '</strong>',
    RIGHT(columnA, LENGTH(columnA) - (REGEXP_INSTR(columnA, 'jo.{0,10}ba.{0,10}tur', 1, 1, 1, 'i') - 1))
  ) AS highlighted_text
FROM myTable
WHERE columnA REGEXP 'jo.{0,10}ba.{0,10}tur';

This will output:
I have been working for <strong>Joseph Balsora Turgeon</strong> for the last 5 years

4. Notes for Older MySQL Versions (Pre-8.0) #

If you're stuck on MySQL 5.7 or earlier (which doesn't have REGEXP_INSTR()), you'll need a workaround:

• Use LOCATE() with a simplified pattern, but this gets messy for proximity searches.
• Create a custom stored function to parse the regex and find the start position, but this is more complex. Your best bet is to upgrade to MySQL 8.0 if possible for native regex position support.

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