如何在Meta Query中使用REGEX实现精准引用码匹配？

Got it, the issue with your current LIKE query is that it can match partial numbers (like searching for 159 might accidentally pull up CFN-1159, which isn't what you want). Using regex with REGEXP in your meta query will let you target the exact numeric part of your reference code without false positives.

Here's how to adjust your code:

First, sanitize the input to ensure you're only working with numeric values (this prevents regex injection and unexpected matches):

// Sanitize the submitted reference code to only digits
$ref = intval($_POST['ref']);

Then build your regex pattern. Since your meta values follow the format [Prefix]-[Number], we'll create a regex that matches either:

• The exact number as the full meta value (in case some entries don't have a prefix), OR
• Any string that ends with -[YourNumber] (to catch prefixed references like CFN-1159)

// Create the regex pattern for MySQL (uses POSIX regex syntax)
$regex_pattern = sprintf('^.*-%d$|^%d$', $ref, $ref);

// Updated meta query array
$ref_query = array(
    'meta_key' => 'reference_code',
    'meta_value' => $regex_pattern,
    'meta_compare' => 'REGEXP'
);

How this works: #

• ^.*-%d$: Matches any string that starts with any characters (.*), followed by a hyphen, then your exact reference number, and ends immediately after the number. This ensures something like CFN-11159 won't be matched when searching for 1159.
• |: Acts as an OR operator in regex.
• ^%d$: Matches the exact reference number as the full meta value, in case some entries don't use the prefix.
• REGEXP: Tells WordPress to use MySQL's regex matching instead of LIKE.

Important Notes: #

• Using intval() is crucial here—it strips any non-numeric characters from the input, so you don't have to worry about regex special characters breaking your pattern or causing security issues.
• If your reference codes sometimes include letters, you'd need to use preg_quote() to escape regex special characters instead of intval().
• Make sure this $ref_query is added to the meta_query array in your WP_Query arguments (e.g., 'meta_query' => array($ref_query)).

This will return only posts where the reference_code meta value either is exactly your searched number or ends with -[searched number], giving you the unique matches you want.

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