At 16:01 -0600 3/16/06, Sterling Anderson wrote:
On Mar 16, 2006, at 3:49 PM, Paul DuBois wrote:
This fails however. 'g/dl' != 'g/dL' though. I don't get why a
string field is being treated this way. I understand the HFS+
filesystem is not case sensitive but that shouldn't be an issued
with varchar fields should it?
No, it's not a filesystem issue. It's a character set issue. The column
has a character set of utf8, and the default collation (which determines
the comparison and sorting characteristics is utf8_general_ci:
mysql> show collation like 'utf8%';
+--------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
...
"ci" means "case insensitive" and that's why you're seeing the results
that you do. You could use the utf8_bin collation for this column
if you want comparisons to be based on binary character values (which
will, in effect, give you case sensitive behavior).
So, it sounds like it may be more logical for me to use utf8_bin
universally or perhaps a different character set like latin1? I'm
curious because we contracted someone at DoIT (the UW) to migrate an
Oracle DB to MySQL, and they chose to use utf8. Now I'm having this
problem.
The character set is not the issue. The collation is. That's what
determines the comparison and sorting properties.
DoIT's choice of utf8 is neutral, and it makes sense that they chose
to use the default case-insensitive collation unless you specified in
advance that you require case sensitive comparisons.
Changing to latin1 won't in itself make any difference because its default
collation is case insensitive, too. You do have the choice of using an
explicitly case-sensitive collation, though: latin1_general_cs
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
