Professional Web Applications Themes

fulltext search using different collation - MySQL

Hello all, I'm running MySQL 5.0.41. I have a table with its cht set to utf8, collated to utf8_bin. I'm using utf8_bin because I need case-sensitive unique values in a specific column. I'd like to perform a case- insensitive fulltext search on this particular column. I've created a fulltext key, however I cannot seem to change the collation for the search. I see no docs on the MySQL website either (however I did find this, which may be useful to some: http://dev.mysql.com/doc/refman/5.0/en/cht-collate.html). Ultimately, I need inserts to be case-sensitive for uniqueness, but fulltext searches to be case-insensitive. I've included my ...

  1. #1

    Default fulltext search using different collation

    Hello all,
    I'm running MySQL 5.0.41. I have a table with its cht set to utf8,
    collated to utf8_bin. I'm using utf8_bin because I need case-sensitive
    unique values in a specific column. I'd like to perform a case-
    insensitive fulltext search on this particular column. I've created a
    fulltext key, however I cannot seem to change the collation for the
    search. I see no docs on the MySQL website either (however I did find
    this, which may be useful to some: http://dev.mysql.com/doc/refman/5.0/en/cht-collate.html).
    Ultimately, I need inserts to be case-sensitive for uniqueness, but
    fulltext searches to be case-insensitive. I've included my test table
    creation statement. Any help would be appreciated.

    Thanks,
    TD

    CREATE TABLE TestTable
    (
    ID int NOT NULL auto_increment,
    Chars varchar(100) NOT NULL,
    PRIMARY KEY (ID),
    UNIQUE (Chars),
    FULLTEXT (Chars)
    ) ENGINE=MyISAM CHT=utf8 COLLATE=utf8_bin;

    tdolfo Guest

  2. #2

    Default Re: fulltext search using different collation

    tdolfo wrote: 

    This seems to simple to solve the problem but create an index and
    searching the database using a case conversion function do the trick?
    I've had to do that as you never really can trust users to enter data
    properly and as a DBA, you have no control over the code.

    Adam
    Adam Guest

  3. #3

    Default Re: fulltext search using different collation

    On Jul 9, 1:01 am, Adam Englander <com> wrote: 


    >
    > This seems to simple to solve the problem but create an index and
    > searching the database using a case conversion function do the trick?
    > I've had to do that as you never really can trust users to enter data
    > properly and as a DBA, you have no control over the code.
    >
    > Adam[/ref]

    Adam, thanks for your reply. I thought of doing that, but MySQL
    doesn't seem to allow any function modifiers to be used within
    MATCH(). Doing this fails: ... WHERE MATCH (UCASE(column_name)) ...

    Any other ideas?

    Thanks,
    TD

    tdolfo Guest

  4. #4

    Default Re: fulltext search using different collation

    tdolfo wrote: 
    >> This seems to simple to solve the problem but create an index and
    >> searching the database using a case conversion function do the trick?
    >> I've had to do that as you never really can trust users to enter data
    >> properly and as a DBA, you have no control over the code.
    >>
    >> Adam[/ref]
    >
    > Adam, thanks for your reply. I thought of doing that, but MySQL
    > doesn't seem to allow any function modifiers to be used within
    > MATCH(). Doing this fails: ... WHERE MATCH (UCASE(column_name)) ...
    >
    > Any other ideas?
    >
    > Thanks,
    > TD
    >[/ref]
    The problem is your collation directive specifies utf8_bin and utf8_bin
    does not allow for case insensitive full text searching. You can change
    your collation or find another option for searching for your data.

    It doesn't look like you're trying to get relevance so why use a full
    text search? If you are using a direct match use "WHERE
    UCASE(column_name) = UCASE('value')" or if your searching within the
    string use "WHERE UCASE(column_name) like UCASE('%value%')".

    Unfortunately, it appears that MySQL doesn't allow for function based
    indexes like UCASE(column_name), I do miss Oracle sometimes, so you
    won't be able to use an index.

    If your table is large enough to really need an index or you really need
    full text searching, you could create an insert trigger with MySQL 5.x
    to store an upper case version of your column in a different column.

    Adam
    Adam Guest

Similar Threads

  1. FULLTEXT Search Problem
    By korcs in forum MySQL
    Replies: 4
    Last Post: June 28th, 03:57 PM
  2. FULLTEXT Search
    By starman7@hotmail.com in forum MySQL
    Replies: 3
    Last Post: December 30th, 01:00 AM
  3. partial word search with FULLTEXT
    By creadcdd in forum MySQL
    Replies: 0
    Last Post: August 31st, 07:02 PM
  4. Tune performance of fulltext search
    By jens.bertheau@gmx.de in forum MySQL
    Replies: 3
    Last Post: February 13th, 08:59 AM
  5. MySQL UNION on 2 Fulltext search queries
    By André Nęss in forum PHP Development
    Replies: 0
    Last Post: July 21st, 11:55 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139