如何在MySQL中搜索完整句子？现有代码无法精准匹配求解决

Hey there! The issue with your current query is that LIKE is built for fuzzy matching—even without wildcards, it won’t reliably give you the precise, exact match you need. Let’s fix that right away.

1. Basic Exact Match with = #

Replace the LIKE operator with the equality sign = to target rows where the question column is exactly identical to your input string:

$question = "What is your Name";
$query = "SELECT * FROM `def_questions` WHERE `question` = '$question' ";

This will only return entries where the text matches perfectly, including capitalization.

2. Handling Case Insensitivity (If Needed) #

If you need to match "What is Your Name" even when capitalization differs (like your example), you have two reliable options:

• Option 1: Use Collation (MySQL-specific)
  Force a case-insensitive collation for the comparison:

  $query = "SELECT * FROM `def_questions` WHERE `question` COLLATE utf8mb4_general_ci = '$question' COLLATE utf8mb4_general_ci";
  

• Option 2: Normalize Case with LOWER()
  Convert both the column value and input to lowercase before comparing:

  $query = "SELECT * FROM `def_questions` WHERE LOWER(`question`) = LOWER('$question') ";
  

3. Critical: Avoid SQL Injection! #

Your current code directly plugs variables into the SQL string, which is a major security risk. Always use prepared statements instead. Here’s how to do it properly with PDO:

// Assuming you have a PDO connection set up
$question = "What is your Name";
$stmt = $pdo->prepare("SELECT * FROM `def_questions` WHERE `question` = ?");
$stmt->execute([$question]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Or with MySQLi:

// Assuming you have a MySQLi connection set up
$question = "What is your Name";
$stmt = $mysqli->prepare("SELECT * FROM `def_questions` WHERE `question` = ?");
$stmt->bind_param("s", $question); // "s" denotes a string parameter
$stmt->execute();
$results = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

That should give you the precise matches you’re looking for while keeping your code secure!

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