Case sensitivity problem (I think) on OS X
I'm running MySQL 5.0.18 on OS X 10.4.5, on an intel MacBook. My table definition is: CREATE TABLE `UNIT` ( `UNIT_ID` int(11) NOT NULL auto_increment, `UNIT_CODE` varchar(50) default NULL, `UNIT_DESC` varchar(50) default NULL, `USER_ID` int(11) NOT NULL, `USER_DT` datetime NOT NULL, `RESEARCH_SITE_ID` int(11) NOT NULL, PRIMARY KEY (`UNIT_ID`), UNIQUE KEY `UNIT_CODE` (`UNIT_CODE`), KEY `RESEARCH_SITE_ID` (`RESEARCH_SITE_ID`), KEY `USER_ID` (`USER_ID`), CONSTRAINT `unit_ibfk_2` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`), CONSTRAINT `unit_ibfk_1` FOREIGN KEY (`RESEARCH_SITE_ID`) REFERENCES `research_site` (`RESEARCH_SITE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 When I try inserting I am getting an error relating to the `UNIT_CODE` UNIQUE KEY. See below: mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(9, 'g/dL', NULL, 1, '2005-03-22 08:01:07 AM', 0); Query OK, 1 row affected, 1 warning (0.00 sec) This works fine. mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(10, 'g/dl', NULL, 1, '2005-03-22 08:01:07 AM', 0); ERROR 1062 (23000): Duplicate entry 'g/dl' for key 2 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? -- Sterling Anderson email/aim: sterling_anderson [at] mac.com http://sterlinganderson.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitivity problem (I think) on OS X
At 15:22 -0600 3/16/06, Sterling Anderson wrote: I'm running MySQL 5.0.18 on OS X 10.4.5, on an intel MacBook. My table definition is: CREATE TABLE `UNIT` ( `UNIT_ID` int(11) NOT NULL auto_increment, `UNIT_CODE` varchar(50) default NULL, `UNIT_DESC` varchar(50) default NULL, `USER_ID` int(11) NOT NULL, `USER_DT` datetime NOT NULL, `RESEARCH_SITE_ID` int(11) NOT NULL, PRIMARY KEY (`UNIT_ID`), UNIQUE KEY `UNIT_CODE` (`UNIT_CODE`), KEY `RESEARCH_SITE_ID` (`RESEARCH_SITE_ID`), KEY `USER_ID` (`USER_ID`), CONSTRAINT `unit_ibfk_2` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`), CONSTRAINT `unit_ibfk_1` FOREIGN KEY (`RESEARCH_SITE_ID`) REFERENCES `research_site` (`RESEARCH_SITE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 When I try inserting I am getting an error relating to the `UNIT_CODE` UNIQUE KEY. See below: mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(9, 'g/dL', NULL, 1, '2005-03-22 08:01:07 AM', 0); Query OK, 1 row affected, 1 warning (0.00 sec) This works fine. mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(10, 'g/dl', NULL, 1, '2005-03-22 08:01:07 AM', 0); ERROR 1062 (23000): Duplicate entry 'g/dl' for key 2 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 | | utf8_unicode_ci| utf8| 192 | | Yes | 8 | | utf8_icelandic_ci | utf8| 193 | | Yes | 8 | | utf8_latvian_ci| utf8| 194 | | Yes | 8 | | utf8_romanian_ci | utf8| 195 | | Yes | 8 | | utf8_slovenian_ci | utf8| 196 | | Yes | 8 | | utf8_polish_ci | utf8| 197 | | Yes | 8 | | utf8_estonian_ci | utf8| 198 | | Yes | 8 | | utf8_spanish_ci| utf8| 199 | | Yes | 8 | | utf8_swedish_ci| utf8| 200 | | Yes | 8 | | utf8_turkish_ci| utf8| 201 | | Yes | 8 | | utf8_czech_ci | utf8| 202 | | Yes | 8 | | utf8_danish_ci | utf8| 203 | | Yes | 8 | | utf8_lithuanian_ci | utf8| 204 | | Yes | 8 | | utf8_slovak_ci | utf8| 205 | | Yes | 8 | | utf8_spanish2_ci | utf8| 206 | | Yes | 8 | | utf8_roman_ci | utf8| 207 | | Yes | 8 | | utf8_persian_ci| utf8| 208 | | Yes | 8 | | utf8_esperanto_ci | utf8| 209 | | Yes | 8 | | utf8_hungarian_ci | utf8| 210 | | Yes | 8 | ++-+-+-+--+-+ 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). -- 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]
Re: Case sensitivity problem (I think) on OS X
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. I almost contacted you directly. Not sure if you recognize my name, I'm with WNPRC. -- Sterling Anderson email/aim: sterling_anderson [at] mac.com http://sterlinganderson.net/ 608.577.7208 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitivity problem (I think) on OS X
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]
Case Sensitivity Problem
Hi, Can somebody PLEASE help me If I send a query to get a list of names, like this: SELECT Name FROM users ORDER BY Name, I get a list of users like this... Bob Jon Mark Sally andy lucy mary steve when what I REALLY want is this... andy Bob Jon lucy Mark mary Sally Steve Can someone give me an idea on how I need to change my query in order to achieve this ? Thanks Keith Netrickery Website Designers - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Case Sensitivity Problem
Select Name, lower(name) as sortOrder from users order by sortOrder; Not exactly what you wanted, but closer. Cal http://www.calevans.com -Original Message- From: Kif Henderson [mailto:[EMAIL PROTECTED]] Sent: Friday, March 09, 2001 7:38 PM To: [EMAIL PROTECTED] Subject: Case Sensitivity Problem Hi, Can somebody PLEASE help me If I send a query to get a list of names, like this: SELECT Name FROM users ORDER BY Name, I get a list of users like this... Bob Jon Mark Sally andy lucy mary steve when what I REALLY want is this... andy Bob Jon lucy Mark mary Sally Steve Can someone give me an idea on how I need to change my query in order to achieve this ? Thanks Keith Netrickery Website Designers - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
