Find the answer to your Linux question:
Results 1 to 6 of 6
If I have a field with "The cat jumps over the rat" in it, and I want to type "cat" in a website form (which uses that database), and get ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Linux User
    Join Date
    Mar 2013
    Posts
    354

    How to search *within* a MySQL field?


    If I have a field with "The cat jumps over the rat" in it, and I want to type "cat" in a website form (which uses that database), and get that field, what do I do?
    I don't want to use LIKE.


    Thanks.

  2. #2
    Linux Engineer
    Join Date
    Apr 2012
    Location
    Virginia, USA
    Posts
    893
    You can use the like statement with wildcards.

    Select field from table where field like '%cat%';

    This will be pretty slow, but is the simplest way to do it.
    If you need this to be faster (ie, you're searching thousands or millions of rows of data), you should consider implementing a full-text search function. MySQL offers a primative index for this purpose, or you can use an external engine such as lucene or sphinx.

  3. #3
    Linux User
    Join Date
    Dec 2009
    Posts
    264
    The only different option I see is to create a second table containing a list of words and where they are used ...

    Would be faster to search, but adding a new text will take it's time since you need to add several entry's into an indexed second table

  4. #4
    Linux Engineer
    Join Date
    Dec 2013
    Posts
    1,274
    Quote Originally Posted by zombykillah View Post
    The only different option I see is to create a second table containing a list of words and where they are used ...

    Would be faster to search, but adding a new text will take it's time since you need to add several entry's into an indexed second table
    No need to create a list, as mizzle points out MySQL already maintains an index and it can be searched.

    i.e.
    Code:
    select * from <tablename> WHERE MATCH(<field>) AGAINST (<word>);
    MySQL :: MySQL 5.6 Reference Manual :: 12.9 Full-Text Search Functions

  5. #5
    Linux User
    Join Date
    Dec 2009
    Posts
    264
    just found that function ... didn't know mysql had an fulltext index.

  6. #6
    Linux User
    Join Date
    Mar 2013
    Posts
    354
    ummm just out of curiosity, is there any way to get MySQL/MariaDB to do fuzzy searching on some text? ie. if I type in "act" above, instead of "cat"?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •