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]
