search

Home  >  Q&A  >  body text

php - mysql query method question

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~

PHPzPHPz2752 days ago648

reply all(1)I'll reply

  • 仅有的幸福

    仅有的幸福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

    reply
    0
  • Cancelreply