萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

MySQL 中文查詢失效根因與四大解決方案

現象重現

$str = "李";
$sql = "SELECT * FROM users WHERE name LIKE '%$str%'";

預期找出含「李」字的記錄,實際返回大量無關資料(如「理」、「里」、「禮」等)。

根本原因

MySQL 預設字元集 latin1(或 utf8mb3)搭配預設排序規則 _general_ci(Case Insensitive),導致:

  1. 中文字元在排序規則下被視為「等價類別」
  2. LIKE 比較時觸發隱含轉換,破壞多位元組字元邊界
  3. 「不區分大小寫」邏輯錯誤套用至中文,造成不同漢字誤判為相同

四大解決方案(按推薦順序)

方案一:欄位層級指定 BINARY 排序(最徹底)

-- 建表時
CREATE TABLE users (
    name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
);

-- 或修改既有表
ALTER TABLE users MODIFY name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
  • utf8mb4_bin:二進制比較,逐字節匹配,完全區分每個漢字
  • 副作用:英文也不再忽略大小寫('a' != 'A'),若需英文不分大小寫可用 utf8mb4_0900_ai_ci (MySQL 8.0+)

方案二:查詢層級強制 BINARY 比較(最靈活)

SELECT * FROM users WHERE name LIKE BINARY '%李%';
  • 不改動表結構,單一查詢生效
  • 適用於僅部分查詢需精確匹配的場景

方案三:使用 LOCATE() / INSTR() 函數

-- LOCATE(substr, str) 返回位置,0 表示不存在
SELECT * FROM users WHERE LOCATE('李', name) > 0;

-- INSTR(str, substr) 同義
SELECT * FROM users WHERE INSTR(name, '李') > 0;
  • 函數內部採用位元組比較,天然支援中文
  • 無法使用索引(全表掃描),大資料量需配合全文索引

方案四:編譯/啟動參數指定字元集(系統級)

# my.cnf / my.ini
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_bin  # 或 utf8mb4_0900_ai_ci

[client]
default-character-set = utf8mb4
  • 影響全例新建表的預設值
  • 既有表需 ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
  • 需重啟 MySQL 服務生效

最佳實務總結

  1. 新專案:資料庫/表/欄位統一 utf8mb4 + utf8mb4_0900_ai_ci(MySQL 8.0)或 utf8mb4_unicode_ci(5.7),僅精確比對欄位加 COLLATE utf8mb4_bin
  2. 既有專案:優先用 LIKE BINARY 或 LOCATE() 修正查詢,再規劃表結構遷移
  3. 連線層:PHP PDO 連線 DSN 加 charset=utf8mb4,執行 SET NAMES utf8mb4
  4. 全文搜尋需求:改用 FULLTEXT INDEX + MATCH() AGAINST()(MySQL 5.7.6+ 支援中文 ngram parser)

驗證腳本

<?php
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$char = "李";
// 方案二驗證
$stmt = $pdo->prepare("SELECT name FROM users WHERE name LIKE BINARY ?");
$stmt->execute(["%$char%"]);
var_dump($stmt->fetchAll(PDO::FETCH_COLUMN));
copyright © 萬盛學電腦網 all rights reserved