Hey folks,
I need an innodb fulltext index that treats some characters different from how they are treated by default. To be exact I want the "&" (ampersand u0026) to be treated like a character e.g. like an "a" (u0061).
And set up a UCA collation in /usr/share/mysql/charsets/Index.xml like:
<charset name="utf8">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
<collation name="utf8_general_ci" id="33">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<!-- my code starts here -->
<collation name="utf8_withampersand_ci" id="1024">
<rules>
<reset>a</reset>
<i>\u0026</i> <!-- ampersand -->
</rules>
</collation>
<!-- my code ends here -->
<collation name="utf8_bin" id="83">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
</charset>
First question:How do I define the base collation of an UCA collation? Since it is nowhere given in the example I assume the primary collation of the charset is used as base, is that correct?
Defining the collation like this and restarting mysql made the collation available.
I can list it using SHOW COLLATION LIKE 'utf8\_%';
I can use it for collumns
-- Example table using the custom collation
CREATE TABLE `fulltext_search` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`search` varchar(255) CHARACTER SET utf8 COLLATE utf8_check24_ci DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `ft_search` (`search`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Alas the collation has no effect.
More questions:
What am I missing?
Is it even possible to control the collation of the fulltext index for innodb this way?
Is my LDML to change the behaviour of the ampersand correct at all?
Thanks in advance
Regards
Armin