Custom MySQL Collation Not Working

120 views Asked by At

My goal is to sort a few numbers the same as a handful of characters.

ie:

4 sorts the same as A or a
3 sorts the same as E or e

Why isn't this working?

I've added the following to /usr/share/mysql/charsets/Index.xml

...
<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>
  <collation name="utf8_bin"        id="83">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
  <collation name="utf8_leet_ci"    id="244">
    <rules>
      <reset>\u0E33</reset>
      <t>\u0E2D</t>
      <reset>\u0E8B</reset>
      <t>\u0E2C</t>
      <reset>\u0EC1</reset>
      <t>\u0E2F</t>
      <reset>\u0EFB</reset>
      <t>\u0E2A</t>
      <reset>\u0F82</reset>
      <t>\u0E29</t>
      <reset>\u0FEA</reset>
      <t>\u0E2E</t>
      <reset>\u1002</reset>
      <t>\u0E30</t>
    </rules>
  </collation>
</charset>
...

I also tried as:

...
<collation name="utf8_leet_ci"  id="244">
    <rules>
      <reset>A</reset>
      <t>4</t>
      <reset>E</reset>
      <t>3</t>
      <reset>G</reset>
      <t>6</t>
      <reset>I</reset>
      <t>1</t>
      <reset>O</reset>
      <t>0</t>
      <reset>S</reset>
      <t>5</t>
      <reset>T</reset>
      <t>7</t>
    </rules>
...

Here is the results of my testing:

CREATE TABLE `challenge_copy` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `category` varchar(250) COLLATE utf8_leet_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=141 DEFAULT CHARSET=utf8 COLLATE=utf8_leet_ci

SELECT category, HEX(category), HEX(WEIGHT_STRING(category)) 
FROM challenge_copy;

a   61  0E33
A   41  0E33
4   34  0E2D
e   65  0E8B
E   45  0E8B
3   33  0E2C
g   67  0EC1
G   47  0EC1
6   36  0E2F
i   69  0EFB
I   49  0EFB
1   31  0E2A
o   6F  0F82
O   4F  0F82
0   30  0E29
s   73  0FEA
S   53  0FEA
5   35  0E2E
t   74  1002
T   54  1002
7   37  0E30
0

There are 0 answers