Mysql match against numeric keyword

Question

I use mysql full-text index. I found it can not matches a key numeric word as '1' in '1,2,3' or '1 2 3'. I use this query "SELECT * FROM users u where match(u.leader_uids) against('1' IN BOOLEAN MODE);"

How to solve this issue? Thanks a lot!

Answer 1

I am Providing the example hope it will works for you i think

MATCH (field) AGAINST ('+856049' IN BOOLEAN MODE)

Answer 2

It will work only with words of 4 or more digits. So you must concat some prefix in the leader_uid before saving it. Example:

CREATE TABLE mytable(
    id INT NOT NULL KEY AUTO_INCREMENT,
    myfield TEXT,
    FULLTEXT KEY ix_mytable (myfield)
);

INSERT INTO mytable (myfield) VALUES 
    ('id_1 id_2 id_3'),
    ('id_8'),
    ('id_4 id_1');

SELECT * FROM mytable 
    WHERE MATCH(myfield) AGAINST ('+id_1' IN BOOLEAN MODE);

-- will select rows 1 and 3

You can change the minimum amount of chars required for the words, in mysql config: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_min_token_size