CODEWITHSUNDEEP
mysqlsql
daniel.tosaba
daniel.tosaba

Reputation: 2563

How to automatically convert a MySQL column to lowercase

Is there a property that I can add to a column so that it converts its value to lowercase? Instead of doing it for every single value through PHP?

Upvotes: 1

Views: 4981

Answers (3)

Darma
Darma

Reputation: 11

Maybe you mean this?

  UPDATE table SET column = LOWER(column)

Upvotes: 0

MarkR
MarkR

Reputation: 63548

Yes, but don't do it.

If you want exclusively lowercase characters in a column, convert them when you insert (or update) them.

If you need a column to be case insensitive in comparisons, use a case insensitive collation (which are used by default in e.g. utf8 columns)

Upvotes: 1

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27496

You could probably do it through a trigger that fires on insert or update. Myself, I'd rather just create a view that has a lower-case version of the column in question. The SQL for the view might look like

SELECT ID, LOWER(MY_COLUMN) AS MY_COLUMN_LOWERCASE
FROM MY_TABLE;

Upvotes: 2

Related Questions