GNU Info

(mysql.info)Case sensitivity

---

Next: Using DATE Prev: Query Issues Up: Query Issues

---

Enter node , (file) or (file)node

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 `A' or `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 where 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'. Note: `CREATE TABLE'.

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.