multiple indexes with different Collation on same column
G'day, is it possible to have multiple indexes with different Collation on same column with MariaDB Let say varchar(300) this column would hold index with collation 'utf8_general_ci' as well as 'utf_german2_ci' It's for searching with and without umlauts. I guess same can be achieve with adding new column, but I would preferably use just index.
This can be achieve in MySQL8 with command:
CREATE INDEX idx_test ON table ((text COLLATE utf8_general_ci));
Answer Answered by Marko Mäkelä in this comment.
Unfortunately, in MariaDB, the collation is currently an attribute of the column, not of the index. You could work around this limitation by defining an indexed virtual column that uses a different collation. But, you should be aware that there are various bugs in the InnoDB virtual column implementation of MySQL 5.7 (and presumably 8.0) and MariaDB 10.2.