$str = "李";
$sql = "SELECT * FROM users WHERE name LIKE '%$str%'";
預期找出含「李」字的記錄,實際返回大量無關資料(如「理」、「里」、「禮」等)。
MySQL 預設字元集 latin1(或 utf8mb3)搭配預設排序規則 _general_ci(Case Insensitive),導致:
LIKE 比較時觸發隱含轉換,破壞多位元組字元邊界-- 建表時
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+)SELECT * FROM users WHERE name LIKE BINARY '%李%';
-- 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_binutf8mb4 + utf8mb4_0900_ai_ci(MySQL 8.0)或 utf8mb4_unicode_ci(5.7),僅精確比對欄位加 COLLATE utf8mb4_binLIKE BINARY 或 LOCATE() 修正查詢,再規劃表結構遷移charset=utf8mb4,執行 SET NAMES utf8mb4FULLTEXT 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));