如何实现支持多输入的MySQL LIKE模糊匹配替换IN精确匹配？

Got it, let's tackle this problem step by step. You want to modify your shortcode to support partial matches for product slugs (post_name) instead of exact matches, while handling multiple input values. Plus, we need to fix the SQL injection risk in your original code—super important!

Below are two solid, secure solutions tailored to your needs:

方案1：使用多个LIKE语句（兼容性强，直观易维护） #

This approach converts each input keyword into a LIKE '%keyword%' condition, then combines them with OR. It works across all MySQL versions and is straightforward to debug.

Here's the revised code:

function woo_products_by_name_shortcode( $atts, $content = null ) {
    // Replace extract() with safer attribute handling (avoids global variable conflicts)
    $atts = shortcode_atts( array(
        'name' => ''
    ), $atts );

    $name_input = trim( $atts['name'] );
    if( empty( $name_input ) ) {
        return '<p>Please enter product name keywords to match.</p>';
    }

    // Split input into an array, remove empty values
    $keywords = array_filter( array_map( 'trim', explode( ',', $name_input ) ) );
    if( empty( $keywords ) ) {
        return '<p>Please enter valid product name keywords.</p>';
    }

    global $wpdb;

    // Build safe LIKE conditions for each keyword
    $like_conditions = array();
    foreach( $keywords as $keyword ) {
        // Escape LIKE wildcards (%) and sanitize input to prevent SQL injection
        $escaped_keyword = $wpdb->esc_like( $keyword );
        $like_conditions[] = $wpdb->prepare( "post_name LIKE %s", "%{$escaped_keyword}%" );
    }

    // Combine conditions with OR
    $where_clause = implode( ' OR ', $like_conditions );

    // Fetch matching product IDs
    $ids = $wpdb->get_col( "
        SELECT ID FROM {$wpdb->prefix}posts
        WHERE post_type = 'product' AND ( {$where_clause} )
    " );

    ob_start();

    // Handle no matches case
    if( empty( $ids ) ) {
        echo '<p>No matching products found.</p>';
        return ob_get_clean();
    }

    // Query and display products (keep your original output or customize as needed)
    $loop = new WP_Query( array(
        'post_type'      => 'product',
        'posts_per_page' => -1,
        'post__in'       => $ids,
        'orderby'        => 'post__in' // Optional: Preserve order of matched keywords
    ));

    echo '<pre>'; print_r($loop->posts); echo '</pre>';

    return ob_get_clean();
}
add_shortcode("woo_products_by_name", "woo_products_by_name_shortcode");

方案1优势： #

• Wide compatibility: Works with all MySQL versions
• Clear logic: Easy to read and modify for future tweaks
• Max security: Uses WordPress's built-in $wpdb methods to eliminate SQL injection risks

方案2：使用REGEXP（简洁，适合复杂匹配） #

If you're running a modern MySQL version (5.1+), regular expressions let you write more concise code for multiple keyword matches. Just remember to escape regex special characters (like ., *, +) to avoid unexpected behavior.

Replace the condition-building part of the code above with this:

// Build safe regex patterns for each keyword
$regex_patterns = array();
foreach( $keywords as $keyword ) {
    // Escape regex special characters
    $escaped_keyword = preg_quote( $keyword, '/' );
    $regex_patterns[] = $escaped_keyword;
}

// Combine patterns into a single regex (matches any keyword)
$regex = implode( '|', $regex_patterns );

// Fetch matching product IDs
$ids = $wpdb->get_col( $wpdb->prepare( "
    SELECT ID FROM {$wpdb->prefix}posts
    WHERE post_type = 'product' AND post_name REGEXP %s
", $regex ) );

方案2优势： #

• Cleaner code: Especially useful when dealing with dozens of keywords
• Flexible: Easy to extend for advanced matching (e.g., match keywords at the start of slugs with ^cat)
• Secure: Still uses WordPress's sanitization methods to prevent injection

Key Notes for Both Solutions: #

1. SQL Injection Fix: The original code directly concatenated user input into SQL—this is a critical vulnerability. Both solutions use $wpdb->prepare() and escaping to keep your site safe.
2. Input Validation: Added checks for empty/invalid input to improve user experience.
3. Performance: If you have thousands of products, LIKE '%keyword%' won't use indexes. For better speed, consider:
   • Matching slugs starting with keywords (e.g., LIKE 'cat%') which can use indexes
   • Adding a full-text index to the post_name field (requires extra setup)
4. Order Preservation: The orderby => 'post__in' parameter keeps products in the order their keywords were entered (optional but user-friendly).

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