使用utf8mb4仍无法插入\xD1，MySQL报1366错误求助

问题描述 #

执行插入语句时，MySQL返回错误：

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xD1' for column 'last_update' at row 1

插入数据的代码如下：

$connection = DataBase\MySQLConnect::getConnection(self::$dbName);
$query  = "INSERT "
        . " into `answer` "
        . " ( "
        . " `reference`, "
        . " `language`, "
        . " `date`, "
        . " `info`, "
        . " `body`, "
        . " `url_html`, "
        . " `url_word`, "
        . " `url_pdf`, "
        . " `last_update` "
        . " ) "
        . " VALUE "
        . " ("
        . " :reference, "
        . " :language, "
        . " :date, "
        . " :info, "
        . " :body, "
        . " :url_html, "
        . " :url_word, "
        . " :url_pdf, "
        . " :last_update "
        . " )";
$stmt = DataBase\MySQLConnect::prepare($query, $connection);
DataBase\MySQLConnect::bindParam($stmt, ':reference', $this->fields['reference'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':language', $this->fields['language'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':date', $this->fields['date'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':info', $this->fields['info'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':body', $this->fields['body'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':url_html', $this->fields['url_html'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':url_word', $this->fields['url_word'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':url_pdf', $this->fields['url_pdf'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':last_update', $this->fields['last_update'], 'str');

$boolContinue = DataBase\MySQLConnect::execute($stmt);

环境说明：

• 所有静态函数基于PDO实现
• 数据库连接通过PHP PDO创建并绑定参数
• 连接和表均使用utf8mb4字符集，表排序规则试过utf8mb4_0900_ai_ci和utf8mb4_unicode_ci，错误依旧

排查与解决步骤 #

• 检查last_update字段类型
  错误里的\xD1是单字节Latin1编码字符，先确认表结构中last_update的字段类型：如果应为datetime/timestamp类型，却被设为字符串，或传入的内容不是合法日期格式，就会触发错误。若字段是日期类型，直接传入PHP时间戳或标准格式化日期字符串，避免带特殊字符的内容。

• 强制PDO连接字符集
  即使库表是utf8mb4，PDO连接时可能未正确指定字符集。创建PDO连接的DSN必须加上charset=utf8mb4：

  $dsn = "mysql:host=localhost;dbname=your_db;charset=utf8mb4";
  $pdo = new PDO($dsn, $user, $pass);
  

  也可在连接后执行语句加固字符集：

  $connection->exec("SET NAMES utf8mb4");
  

• 验证last_update内容编码
  打印$this->fields['last_update']的实际内容，用mb_detect_encoding()检测编码，若为Latin1等非UTF-8编码，转码后再插入：

  $lastUpdate = $this->fields['last_update'];
  if (!mb_detect_encoding($lastUpdate, 'UTF-8', true)) {
      $lastUpdate = mb_convert_encoding($lastUpdate, 'UTF-8', 'Latin1');
  }
  

• 检查PDO参数绑定逻辑
  代码中绑定last_update用自定义的'str'类型，若自定义bindParam函数处理有问题，直接改用原生PDO绑定：

  $stmt->bindParam(':last_update', $this->fields['last_update'], PDO::PARAM_STR);
  

• 排查MySQL全局字符集配置
  执行以下SQL查看全局字符集设置：

  SHOW VARIABLES LIKE 'character_set%';
  SHOW VARIABLES LIKE 'collation%';
  

  确保character_set_connection、character_set_client、character_set_results均为utf8mb4。若不符，在MySQL配置文件（my.cnf/my.ini）中添加：

  [mysqld]
  character-set-server=utf8mb4
  collation-server=utf8mb4_0900_ai_ci
  [client]
  default-character-set=utf8mb4
  

  重启MySQL服务后测试。

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