mysql多表查询1267报错

Question

const books = await mysql('books')
                  .select('books.*','csessioninfo.user_info')
                  .join('csessioninfo','books.openid','csessioninfo.open_id')
                  // .orderBy('id','desc')

 code: 'ER_CANT_AGGREGATE_2COLLATIONS', errno: 1267, sqlMessage: 'Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation \'=\'', sqlState: 'HY000', index: 0, sql: 'select `books`.*, `csessioninfo`.`user_info` from `books` inner join `csessioninfo` on `books`.`openid` = `csessioninfo`.`open_id`' } +1ms

执行上边的代码报错1267，不知道什么原因造成的？

自己在数据库执行也是这个错误
mysql> select `books`.*, `csessioninfo`.`user_info` from `books` inner join `csessioninfo` on `books`.`openid` = `csessioninfo`.`open_id`
    -> ;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

Answer 1

从报错信息上可以看出，是books表和csessioninfo的排序规则不一致导致的。你可以修改 books 表，将排序规则改为utf8mb4_unicode_ci。