Re: Can't identify character set and collation used in string comparisons

[David Raimosson]

After finishing the topic with Rick outside the forum (because of the risk for the discussion to be lengthy) the answer is now posted here.

The target column SupplierName was in a supplier table. The following SQL statement reveals what I needed to know:
SELECT HEX(SupplierName), SupplierName FROM supplier WHERE SupplierName = 'Nestle';"
> 4E6573746C65 Nestle
> 4E4553544CC9 NESTLÉ

So, the final byte (C9) tells us that this is a latin1 column. See http://mysql.rjweb.org/doc.php/charcoll#8_bit_encodings. The string comparison then has to be made using latin1.

A SHOW CREATE TABLE for this table gives:
CREATE TABLE `supplier` (
...
`suppliername` varchar(255) NOT NULL,
...
PRIMARY KEY (`supplierid`),
KEY `suppliername` (`suppliername`)
) ENGINE=InnoDB AUTO_INCREMENT=... DEFAULT CHARSET=latin1

This makes it even more clear that it certainly is a latin1 column.

Finally, the confusing STRCMP() comparing "e" with "é" using latin1 charset and swedish_ci collation might be a bug. That's the best explanation.

Hope this helps anyone.