Perhaps you want “.” or “:” or “-” or something to be treated as a word character so that you can do a fulltext match against an IP or MAC address.
If you want to change the set of characters that are considered word characters, you can do so in two ways…
* Modify the MySQL source … and recompile MySQL.
* Modify a character set file: This requires no recompilation.
Thus sayeth the Manual. What it doesn’t say is just what to modify in that character set file. (Edit: Now it does.) I had to ask MySQL developer Alexander Barkov for help to get it right.
In the user comments on that manual page, John Navratil has provided most of the answer, but there’s an important step missing: adding a new collation.
The “normal” collations like latin1_swedish_ci are compiled in. But we don’t really want to mess with those anyway, because then the parser might become confused: you still want “-” to indicate subtraction everywhere except inside your fulltext search, right?
First, find your character set files. Typically they’re under [MySQL Install Path]\share\charsets, but you can double check that with SHOW VARIABLES LIKE 'character_sets_dir';
Edit Index.xml. Find the section for the character set you want to use (I’m using latin1), and add a new collation, with a new name and an unused id:
<collation name="latin1_ft_ci" id="62"/>
Edit the character set file (latin1.xml in my example). Near the top you’ll find a <ctype> array. There’s a leading 00 (which is there to offset the array to 257 characters for some legacy EOF convention, per the manual.) After that, you’ll find 256 bytes which identify the type of each character in the set. Find the hex value for the character you want (MySQL’s HEX() function is handy for this.) The value for “:” is 0x3A. Find that position in the array. Remember to start at 0x00, so 0x3A is the fourth row down, and the eleventh in from the left. Change the 10 there (which means spacing character) to 01 (which means upper case letter.) You’ll find the rest of the possible character types in the manual.
Scroll down in the same file and find the collations. Copy and paste the whole map from whichever one you normally use (like latin1_swedish_ci) and change the name to match the one we created in the Index.xml file (like latin1_ft_ci).
Restart the server and check that the new collation appears:
SHOW COLLATION LIKE 'latin1_ft_ci';
Finally, alter your table to use the new collation for the fulltext column:
ALTER TABLE tblName MODIFY textColumn TEXT COLLATE latin1_ft_ci;
And now your fulltext search ought to work.
Before and after:
mysql> SELECT * FROM t1 WHERE MATCH(d) against ('00:12:34:56:78:9A');
Empty set (0.00 sec)
mysql> ALTER TABLE t1 MODIFY d TEXT COLLATE latin1_ft_ci;
Query OK, 4 row affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1 WHERE MATCH(d) against ('00:12:34:56:78:9A');
+-------------------+
| d |
+-------------------+
| 00:12:34:56:78:9A |
+-------------------+
1 row in set (0.01 sec)
Leave a Reply