The field "A" in the existing data table T, the value of A is usually in the form of: "12,123,312,1212". There are 100,000 pieces of data in T.
Need to query the records containing a single "12" in A~
For example:
"123,125,1212,1234,312" does not contain a single "12", do not
"1,18,123,1212,321,312" does not contain a single "12", do not
"1,12,123,1212,321,312" contains a single "12" to
...
I have two query methods, which feel a bit cumbersome~ One is CONCAT, the other is LIKE OR
Asking if anyone has a more efficient method, thank you~
仅有的幸福2017-05-31 10:36:41
You can consider adding a full-text index to field A and use full-text search:
ALTER TABLE `T` ADD FULLTEXT (`A`);
SELECT `id` FROM `T`
WHERE MATCH(`A`) AGAINST('12' IN BOOLEAN MODE)
ORDER BY `id` DESC LIMIT 10 OFFSET 0;
It should be noted that:
The default value of innodb_ft_min_token_size of MySQL5.6.4 is 3.
The default value of ft_min_word_len of MyISAM is 4.
If the minimum length of the index you want is 2, then configure it in my.cnf:
innodb_ft_min_token_size =2
ft_min_word_len=2