A “case” history for MySQL select distinct

on an old linux running mysql4.0.x, I noticed a simple SQL query

select distinct item from tablename

returned a case insensitive results (e.g. “foo”, “FOO, “Foo” was listed on a different rows).

But how to obtain a unique record “foo” from a select distinct?

Reading this post I was able to obtain a case insensitive result from my “select distinct” SQL query.

# mysqldump dbname > dbname.dump
# mysqladmin drop dbname
# mysqladmin create dbname
# vi dbname.dump

using vi the editor I added the “binary” attribute to the record definition, for instance

create table kludges ( shortdesc varchar(100) BINARY, fulldesc text)

and then

# mysql dbname < dbname.dump

Tags:

Leave a Reply