Is there any way in MySQL to force underscore to become a word splitter character (like space or dash) without having to use a fulltext parser plugin?
If not, does such a plugin already exist or do I have to learn to make my own?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
I have never heard of such a plugin. Sure, you could make your own plugin.
However, I have a rather wild suggestion if you can afford the diskspace.
Let's take an example table:
CREATE TABLE mydb.mytable ( id int not null auto_increment, txt text, primary key (id), fulltext txt (txt) ) ENGINE=MyISAM;
Let's say a table like this is populated already.
Suppose you want to index any words such as
You may want to create a separate column with the underscores replaced with blanks. You will have to perform the following conversion
CREATE TABLE mydb.mytable_new LIKE mydb.mytable; ALTER TABLE mydb.mytable_new ADD txtsrch TEXT; ALTER TABLE mydb.mytable_new ADD FULLTEXT txt_txtsrtch (txt,txtsrch); ALTER TABLE mydb.mytable_new DROP INDEX txt; INSERT INTO mydb.mytable_new (id,txt,txtsrch) SELECT id,txt,REPLACE(txt,'_',' ') FROM mydb.mytable; ALTER TABLE mydb.mytable RENAME mydb.mytable_old; ALTER TABLE mydb.mytable_new RENAME mydb.mytable;
Once done, the table should look like this
CREATE TABLE mydb.mytable ( id int not null auto_increment, txt text, txsrch text, primary key (id), fulltext txtsrch (txt,txtsrch) ) ENGINE=MyISAM;
This will index the data both ways. Consequently, this blows up the table and index files in size.
You will have to do searching with the following clause
MATCH(txt,txtsrch) AGAINST ...
Please perform this on a test server, look at the increased size of the new table and index. You can decide whether this table layout provides the data you want with tokens separated.
Give it a Try !!!