Can't identify character set and collation used in string comparisons
Posted by: David Raimosson
Date: July 13, 2013 03:03PM

Hi,

I have a table with a string column and try to determine what character set and collation is being used for the comparisons when searching for values.

Charset for current schema, table and column: latin1
Collation for the specified column: latin1_swedish_ci
Charset for connection and rest: utf-8

Suppose you have done the following:
CREATE TABLE `collation_test` (
`Name` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `collation_test` VALUES ('u'),('ü'),('e'),('é'),('ö'),('ø');

Now...

SELECT `Name`
FROM collation_test
WHERE `Name` = 'e';
=> 2 rows (e and é)

SELECT `Name`
FROM collation_test
WHERE `Name` = 'u';
=> 1 row (u)

SELECT `Name`
FROM collation_test
WHERE `Name` = 'ø';
=> 1 row (ø)

SELECT STRCMP('é', 'e');
=> 0 (they are considered equal when using the default charset and collation)

SELECT STRCMP(_latin1 'é' COLLATE latin1_swedish_ci, _latin1 'e' COLLATE latin1_swedish_ci);
=> -1 (they're NOT equal when specifying latin1)

It first seemed to me that utf-8 was used with some unknown collation, but after checking this site:
http://mysql.rjweb.org/doc.php/charcoll
then I can't find anything matching these tests.

What am I doing wrong here? Any help appreciated.

David

Options: ReplyQuote


Subject
Views
Written By
Posted
Can't identify character set and collation used in string comparisons
3828
July 13, 2013 03:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.