网站首页技术博客
mysql General error: 1267 Illegal mix of collations错误
摘要mysql General error: 1267 Illegal mix of collations报错是由于字符集问题导致,这里使用convert对字符串的字符集进行了转换convert('in' using utf8mb4) AS `type`
在项目迁移之后一个视图的访问出现了问题,报了mysql的1267错误,具体报错信息就是
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='
查看了构建的视图
发现type的字符集和user_name不一致,貌似是字符集有问题,尝试修改type的字符集
我的视图
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `goods_in_out_view` AS SELECT
`goods_in`.`id` AS `id`,
`goods_in`.`shop_id` AS `shop_id`,
'in' AS `type`,
`goods_in`.`goods_id` AS `goods_id`,
`goods_in`.`in_price` AS `price`,
`goods_in`.`number` AS `number`,
`goods_in`.`supplier_id` AS `supplier_id`,
0 AS `customer_id`,
`goods_in`.`user_name` AS `user_name`,
`goods_in`.`create_time` AS `create_time`,
`goods_in`.`update_time` AS `update_time`,
`goods_in`.`delete_time` AS `delete_time`
FROM
`goods_in`
WHERE
(`goods_in`.`delete_time` = 0)
UNION
SELECT
`goods_out`.`id` AS `id`,
`goods_out`.`shop_id` AS `shop_id`,
'out' AS `type`,
0 AS `goods_id`,
`goods_out`.`price` AS `price`,
`goods_out`.`number` AS `number`,
0 AS `supplier_id`,
`goods_out`.`customer_id` AS `customer_id`,
`goods_out`.`user_name` AS `user_name`,
`goods_out`.`create_time` AS `create_time`,
`goods_out`.`update_time` AS `update_time`,
`goods_out`.`delete_time` AS `A`
FROM
`goods_out`
WHERE
(`goods_out`.`delete_time` = 0)
修改后的视图
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `goods_in_out_view` AS SELECT
`goods_in`.`id` AS `id`,
`goods_in`.`shop_id` AS `shop_id`,
convert('in' using utf8mb4) AS `type`,
`goods_in`.`goods_id` AS `goods_id`,
`goods_in`.`in_price` AS `price`,
`goods_in`.`number` AS `number`,
`goods_in`.`supplier_id` AS `supplier_id`,
0 AS `customer_id`,
`goods_in`.`user_name` AS `user_name`,
`goods_in`.`create_time` AS `create_time`,
`goods_in`.`update_time` AS `update_time`,
`goods_in`.`delete_time` AS `delete_time`
FROM
`goods_in`
WHERE
(`goods_in`.`delete_time` = 0)
UNION
SELECT
`goods_out`.`id` AS `id`,
`goods_out`.`shop_id` AS `shop_id`,
convert('out' using utf8mb4) AS `type`,
0 AS `goods_id`,
`goods_out`.`price` AS `price`,
`goods_out`.`number` AS `number`,
0 AS `supplier_id`,
`goods_out`.`customer_id` AS `customer_id`,
`goods_out`.`user_name` AS `user_name`,
`goods_out`.`create_time` AS `create_time`,
`goods_out`.`update_time` AS `update_time`,
`goods_out`.`delete_time` AS `A`
FROM
`goods_out`
WHERE
(`goods_out`.`delete_time` = 0)
这里使用convert对字符串的字符集进行了转换
convert('in' using utf8mb4) AS `type`
转换后再访问发现正常了,再去看一下视图结构
问题解决!!!