>From the MySQL manual A.5.1 Case-Sensitivity in Searches
By default, MySQL searches are case-insensitive (although there are some character sets that are never case-insensitive, such as czech ). That means that if you search with col_name LIKE 'a%' , you will get all column values that start with Aor a. If you want to make this search case-sensitive, use something like INSTR(col_name, "A")=1 to check a prefix. Or use STRCMP(col_name, "A") = 0 if the column value must be exactly "A" . Simple comparison operations ( >=, >, = , < , <= , sorting and grouping) are based on each character's ``sort value''. Characters with the same sort value (like E, e and é) are treated as the same character! In older MySQL versions LIKE comparisons were done on the uppercase value of each character (E == e but E <> é). In newer MySQL versions LIKE works just like the other comparison operators. If you want a column always to be treated in case-sensitive fashion, declare it as BINARY . See section 6.5.3 CREATE TABLE Syntax . If you are using Chinese data in the so-called big5 encoding, you want to make all character columns BINARY . This works because the sorting order of big5 encoding characters is based on the order of ASCII codes. On Tue, 23 Sep 2003 10:37:24 -0700, Hsiu-Hui Tseng spoke thusly about case sensitive?: > Hi, > > I have questions on string comparison: > > Table: user_att > +-------------+--------------+------+-----+---------+-------+ >> Field | Type | Null | Key | Default | Extra | > +-------------+--------------+------+-----+---------+-------+ >> user_id | int(11) | | PRI | 0 | | >> att_id | int(11) | | PRI | 0 | | >> value | varchar(200) | | | | | >> date | datetime | YES | | NULL | | > +-------------+--------------+------+-----+---------+-------+ > - one unique index on user_id and att_id (pk) > - one index on att_id and user_id. > > SELECT * FROM user_att WHERE att_id = 123 and value = 'SANDY'; > SELECT * FROM user_att WHERE att_id = 123 and value = 'sandy'; > SELECT * FROM user_att WHERE att_id = 123 and value like 'sandy'; > > The first 2 queries are faster. They result are all the same. > > However, if I want to retrieve only 'sandy' instead of 'Sandy'. Is there any > query to achieve that? > > Is there anyway to configure mysql to be case sensitive? > > Thanks! > > Hsiu-Hui --- Listserv only address. Jeff Shapiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
