Achieving properties of binary and collation at the same time

Question

I have a varchar field in my database which i use for two significantly different things. In one scenario i use it for evaluating with case sensitivity to ensure no duplicates are inserted. To achieve this I've set the comparison to binary. However, I want to be able to search case-insensitively on the same column values. Is there any way I can do this without simply creating a redundant column with collation instead of binary?

Answer 1

you can use the COLLATE statement to change the collation on a column in a query. see this manual page for extensive examples.

Answer 2

CREATE TABLE t_search (value VARCHAR(50) NOT NULL COLLATE UTF8_BIN PRIMARY KEY);

INSERT
INTO    t_search
VALUES  ('test');

INSERT
INTO    t_search
VALUES  ('TEST');

SELECT  *
FROM    t_search
WHERE   value = 'test' COLLATE UTF8_GENERAL_CI;

The second query will return both rows.

Note, however, that anything with COLLATE applied to it has the lowest coercibility.

This means that it's value that will be converted to UTF8_GENERAL_CI for the comparision purposes, not the other way round, which means that the index on value will not be used for searching and the condition in the query will be not sargable.

If you need good performance on case-insensitive searching, you should create an additional column with case-insensitive collation, index it and use in the searches.