MySQL case-sensitive update statement

Question

Let's say you want to update a table Person that looks like this:

Name | Address | Age

You write the query:

UPDATE Person SET Name='Tom' WHERE Name='Thomas'.

Is the WHERE clause in this query case-sensitive? That is, would it update rows with names of thomas and THOMAS?

(I'm working with MySQL Ver 14.14 Distrib 5.1.73, if it helps.)

generally case-insensitive, unless your table is using a case-sensitive collation — Marc B, Oct 16 '14 at 20:52
It depends on the collation of the column and of the database. The documentation is a good place to start to understand these things: http://dev.mysql.com/doc/refman/5.7/en/charset-charsets.html. — Gordon Linoff, Oct 16 '14 at 20:56
Is there a way around this? For example, using STRING functions? — ktm5124, Oct 16 '14 at 20:59
@ktm5124 You can around the default behaviour while creating the table, and you can choose another behaviour in the query statement (not using STRING functions but using an specific syntax). See my answer. — Caffé, Oct 16 '14 at 21:02
possible duplicate of [How can I make SQL case sensitive string comparison on MySQL?](http://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql) — Air, Oct 16 '14 at 21:04

score 4 · Accepted Answer · answered Oct 16 '14 at 20:59

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default.

One could change this default behaviour while creating the table with a different collation: MySql CREATE TABLE Syntax.

You can also change the collation (so the case-insensitive default behaviour) in the query:

WHERE col_name COLLATE latin1_general_cs LIKE 'a%'

1 Answers1