PHP mysql_query使用变量查询Unicode数据失败问题求助

Hey Amit, let's tackle this Unicode search issue you're hitting—this is a pretty common pitfall when dealing with dynamic SQL and multi-parameter forms, so let's break down the most likely fixes step by step:

1. Verify Database/Table Character Set Configuration #

First, double-check that your database and target table are using a full Unicode-compatible character set. MySQL's standard utf8 only supports 3-byte Unicode characters (missing emojis and some rare scripts), so you need utf8mb4 for complete coverage.

Run this query to inspect your table's charset:

SHOW CREATE TABLE your_target_table;

Look for CHARSET=utf8mb4 in the output. If you see latin1 or plain utf8, alter the table to switch to the correct charset:

ALTER TABLE your_target_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

(Pro tip: Back up your table before making this change to avoid accidental data loss.)

2. Force UTF-8 Encoding for MySQL Connections #

Even if your table uses utf8mb4, if your PHP-MySQL connection doesn't explicitly use UTF-8, parameter values will get mangled during transmission.

Right after establishing your database connection, add this line:

// For mysqli (recommended over old mysql_* functions)
mysqli_set_charset($conn, 'utf8mb4');

// For legacy mysql_* functions (migrate to mysqli/PDO soon!)
mysql_set_charset('utf8mb4', $conn);

This ensures both your query and parameter values are sent in the correct encoding to match your table.

3. Replace Dynamic String Concatenation with Prepared Statements #

Building SQL by directly gluing user input isn't just a massive SQL injection risk—it's also a common source of Unicode encoding errors.

Switch to parameterized prepared statements to safely bind your 13 parameters. Here's a quick example with mysqli:

// Assume $conn is your valid database connection
$sql = "SELECT * FROM your_table 
        WHERE param1 = ? 
        AND param2 = ? 
        -- Add the remaining 11 parameter placeholders here";

// Initialize the prepared statement
$stmt = mysqli_prepare($conn, $sql);

// Bind your 13 parameters (use 's' for string, 'i' for integer, etc.)
// For 13 string parameters:
mysqli_stmt_bind_param($stmt, 'sssssssssssss', $param1, $param2, $param3, $param4, $param5, $param6, $param7, $param8, $param9, $param10, $param11, $param12, $param13);

// Execute and fetch results
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

// Process results as usual
while ($row = mysqli_fetch_assoc($result)) {
    // Handle row data
}

Prepared statements automatically handle encoding matching between your app and database, eliminating manual concatenation bugs.

4. Confirm Frontend/Form Encoding #

Make sure your HTML page and form are sending data in UTF-8:

• Add this meta tag in your HTML <head>:

  <meta charset="UTF-8">
  

• If your form has an accept-charset attribute, set it to accept-charset="UTF-8" (or remove it—UTF-8 is the default in modern browsers).

This ensures Unicode values from your form reach your server in the correct encoding before you even process them.

5. Remove Unnecessary Encoding Conversions #

Check if you're accidentally converting parameters with functions like utf8_encode() or mb_convert_encoding()—these can corrupt Unicode data if the input is already UTF-8. Only use these functions if you know the input is in a different charset (e.g., Latin-1).

Start with checking the table charset and connection encoding first—those are the most common culprits. If that doesn't fix it, switching to prepared statements will almost certainly resolve the issue while making your code much safer.

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