CODEWITHSUNDEEP
mysqlsqlfull-text-searchinnodbfulltext-index
Kahlil N
Kahlil N

Reputation: 91

How do I use Fulltext Search in MYSQL with slashes

Looking for some help with this if possible. I have a MySQL table that looks like this:

CREATE TABLE mydata_tbl
    (`name` varchar(92));

INSERT INTO mydata_tbl
    (`name`)
VALUES
    ('https://www.google102.com'),
    ('1999 Pokemon BASE SET SHADOWLESS 84/102 PLUSPOWER UNCOMMON (SHADOWLESS)'),
    ('2018 Pokemon SUN MOON DRAGON MAJESTY 114/102 RESHIRAM GX ULTRA RARE'),
    ('2000 Pokemon GYM HEROES 1ST EDITION 110/102 ERIKAS PERFUME UNCOMMON 1ST EDITION'),
    ('2017 Pokemon SUN MOON CRIMSON INVASION 117/102 KARTANA GX HYPER RARE'),
    ('2016 Pokemon SUN MOON PROMOS SM158/102 CHARIZARD (STAFF) PRE RELEASE PROMO'),
    ('1999 Pokemon FOSSIL UNLIMITED 50/102 KABUTO COMMON UNLIMITED'), 
    ('Pokemon BATTLE ACADEMY BOX SET 119/102 CYNTHIA PIKACHU DECK (PIKACHU SYMBOL 59)'),
    ('1999 Pokemon BASE SET 1ST EDITION 4/102 CHARIZARD HOLO');

CREATE FULLTEXT INDEX idx_1 ON mydata_tbl (`name`);

My problem is I want to be able to sometimes search by the card number. So, if I search for 4/102 I should at least get that result in the first few matches. There's about 100,000 rows in my table and I'm not getting the results I need with FULLTEXT Search. I can get the results I need by using LIKE but that means you have to use the search words in the order they stored in the table...which I don't always know.

Here is my simple SELECT statement:

SELECT name 
FROM mydata_tbl 
WHERE MATCH (name) AGAINST ('"4/102"' IN BOOLEAN MODE);
Limit 5;

What I think is happening is the / is being ignored. Is there a way around this? What do you think the issue could be?

Any help is appreciated.

Thanks, Kahl

Upvotes: 0

Views: 698

Answers (3)

Rick James
Rick James

Reputation: 142306

Plan A:

What is the "min word size" in your configuration? It is probably "3". That means that "4" cannot be searched for, but "102" can be.

Here's the fastest way to find "4/102" in a big table:

WHERE MATCH(col) AGAINST('+102' IN BOOLEAN MODE)
  AND col LIKE '%4/102%'

Since that also gets "114/102" and you don't want that, more effort is needed: LIKE ' 4/102 ' is risky because it "spaces" may not be around it. This works better: RLIKE '[[:<:]]4/102[[:<:]]' (in MySQL 8.0), else RLIKE '\\b4/102\\b'

Plan B:

Prep the data so that words will looked like words. This may require you to add a second column -- one for showing the user, one tailored for FULLTEXT.

In this case, transform "4/102" into, say, "4_102" and then do

WHERE MATCH(col) AGAINST('+4_102' IN BOOLEAN MODE)

That requires work on your part, but will be very fast.

Upvotes: 2

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Use semicolon(;) after limit 5; then the query will work. But it won't return exact matched rows for "4/102". Please check and let me know is it what your are looking for:

SELECT name 
FROM mydata_tbl 
WHERE MATCH (name) AGAINST ('"4/102"' IN BOOLEAN MODE)
Limit 5;

enter image description here

Upvotes: 0

nbk
nbk

Reputation: 49375

This isn't possible with full text search Full text search search is for full words search, and not for partial word searching with like you need it.

use

SELECT name 
FROM mydata_tbl 
WHERE name Like "%4/102%"

or regular expression

Upvotes: 0

Related Questions