Search code examples
mysqlmysql-error-1267

MySQL IP conversion IF statement not working: Illegal mix of collations

I'm trying to do this: http://sqlfiddle.com/#!2/a13fc/2 .

I'm getting the rror Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'if': SELECT dr.fqdn, IF(dr.type="CNAME",dr.value, INET_NTOA(dr.value) ) as dnsval FROM dns_record dr

It doesn't work because my table is latin1.

Can I make this work without converting my table to utf8?

Solution

  • Try:

    SELECT 
dr.fqdn, 

IF(dr.type="CNAME",dr.value, CONVERT(INET_NTOA(dr.value) USING latin1) ) as dnsval

FROM dns_record dr

;

    As you may not mix charset encoding in an IF statement like IF(..., utf8, latin1).

    SQL Fiddle: http://sqlfiddle.com/#!2/a13fc/9