Laravel连接外部数据库报错：COLLATION与字符集不匹配

这个错误SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8'的核心原因很明确：你查询涉及的表（或目标数据库）使用的字符集是utf8，但搭配了属于utf8mb4字符集的排序规则utf8mb4_unicode_ci，两者不兼容导致数据库报错。下面给你几个可行的解决思路：

1. 统一数据库/表的字符集与排序规则 #

这是最彻底的解决方案，建议优先操作：

• 先确认目标数据库的字符集配置：

  SELECT @@character_set_database, @@collation_database;
  

• 再检查涉及查询的表的字符集和排序规则（以orders_products为例）：

  SHOW CREATE TABLE orders_products;
  

• 根据结果选择调整方式：
  • 如果表的字符集是utf8，把排序规则改成对应utf8的utf8_unicode_ci：

    ALTER TABLE orders_products CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    ALTER TABLE orders CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    ALTER TABLE members CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    ALTER TABLE products CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    

  • 如果你的数据库支持，更推荐把字符集升级为utf8mb4（支持更多特殊字符如emoji），搭配对应的utf8mb4_unicode_ci：

    ALTER TABLE orders_products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    -- 对所有涉及的表执行上述语句
    

2. 调整Laravel的数据库连接配置 #

确保Laravel连接配置的字符集和排序规则与目标数据库一致：
打开config/database.php，找到你的zarsam_mysql连接配置块，修改charset和collation字段：

'zarsam_mysql' => [
    'driver' => 'mysql',
    'host' => env('ZARSAM_DB_HOST', '127.0.0.1'),
    'port' => env('ZARSAM_DB_PORT', '3306'),
    'database' => env('ZARSAM_DB_DATABASE', 'forge'),
    'username' => env('ZARSAM_DB_USERNAME', 'forge'),
    'password' => env('ZARSAM_DB_PASSWORD', ''),
    'unix_socket' => env('ZARSAM_DB_SOCKET', ''),
    'charset' => 'utf8', // 对应数据库实际字符集，utf8或utf8mb4
    'collation' => 'utf8_unicode_ci', // 对应charset的合法排序规则
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
],

修改后记得清除配置缓存：

php artisan config:clear

3. 临时在查询中指定排序规则（应急方案） #

如果暂时无法修改数据库或配置，可以在查询中对字符串字段显式指定合法的排序规则，比如：

$order = \DB::connection('zarsam_mysql')->select("
    SELECT O.order_id,
           O.order_time,
           from_unixtime(O.order_time) AS created_at,
           O.priceForPay,
           O.totalPrice,
           O.totalDiscount,
           CONCAT(M.name COLLATE utf8_unicode_ci, ' ', M.family COLLATE utf8_unicode_ci) AS customer_full_name,
           M.username,
           P.pro_title,
           OP.product_id,
           P.product_identifier AS namad_code,
           OP.user_quantity AS count,
           O.mob AS mobile,
           CONCAT(AG.name COLLATE utf8_unicode_ci, ' ', AG.family COLLATE utf8_unicode_ci) AS branch_title
    FROM orders_products OP
    INNER JOIN orders O ON O.order_id = OP.order_id
    INNER JOIN members M ON M.username = O.user_id
    LEFT JOIN members AG ON AG.username = O.agent_id
    INNER JOIN products P ON P.product_id = OP.product_id
    WHERE O.pay_status > 0 and O.order_id = 418766
");

这个方法适合快速验证，但长期来看还是建议统一字符集和排序规则，避免后续出现更多类似问题。

内容的提问来源于stack exchange，提问作者S.M_Emamian