SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot ThetaWaveRider's Avatar
    Join Date
    Aug 2004
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL 4.1.13 case-sensitivity

    I'm writing a simple query in MySQL 4.1.13 comparing a string with a VARCHAR field using LIKE:

    Code:
    SELECT *
    FROM products
    WHERE products_model LIKE 'bk052-rt'
    This query returns nothing. If I run the following:

    Code:
    SELECT *
    FROM products
    WHERE products_model LIKE 'BK052-RT'
    ...one row is returned. The only difference is the case-sensitivity of the LIKE string.

    From what I've seen and learned, VARCHAR fields are not case-sensitive. I thought the problem might be collation-related, so I switched from latin1 to utf8, but the results are the same.

    Anyone know if MySQL is case-sensitive across all field types is this version? I couldn't find any docs confirming it, but it sure seems like it.

    Thanks.
    Last edited by ThetaWaveRider; Mar 12, 2006 at 13:27.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    what specific collation are you using? utf8_bin is going to be case-sensitive since it's a binary collation. if you want case insensitive, then you need a collation that ends in _ci. also, once you change the collation, you need to rebuild your indexes. the mysql manual reccommends a repair index, but on large datasets, droping the index and recreating it is faster.

  3. #3
    SitePoint Zealot ThetaWaveRider's Avatar
    Join Date
    Aug 2004
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, that's the ticket. I failed to mention that both collations are binary. I followed your advice and rebuilt the indexes after changing collations, and all is well now. Thanks.


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
  •