我会非常清楚:在MySQL中创建视图的解决方案是什么,没有非法的非法混合排序错误.
我的SQL代码是这样的(它有一些葡萄牙语单词),我的数据库默认排序规则是latin1_swedish_ci:
CREATE VIEW v_veiculos AS
SELECT
v.id,
v.marca_id,
v.modelo,
v.placa,
v.cor,
CASE v.combustivel
WHEN 'A' THEN 'Álcool'
WHEN 'O' THEN 'Óleo Diesel'
WHEN 'G' THEN 'Gasolina'
ELSE 'Não Informado'
END AS combustivel,
marcas.marca,
/*I think that the CONCAT and COALESCE below causes this error, when the next line the view works fine*/
CONCAT(marca, ' ', v.modelo, ' - Placa: ', v.placa, ' - Combustível: ', COALESCE(v.combustivel, 'Não informado')) AS info_completa
FROM veiculos v
LEFT JOIN
marcas on(marcas.id = v.marca_id);
我认为错误原因是因为我正在使用coalesce和/或concat,因为完整错误的描述告诉我:非法混合排序(latin1_swedish_ci,IMPLICIT)和(utf8_general_ci,COERCIBLE)操作’coalesce’
解决方法:
您也可以使用CAST()将字符串转换为不同的字符集.语法是:
CAST(character_string AS character_data_type CHARACTER SET charset_name)
例如:
SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);
替代方法:使用CONVERT(expr USING transcoding_name)
标签:mysql-error-1064,sql,mysql,collation
来源: https://codeday.me/bug/20190901/1783047.html