| Bug #22341 | Case sensitivity on varchar fields. | ||
|---|---|---|---|
| Submitted: | 14 Sep 2006 12:26 | Modified: | 14 Sep 2006 12:53 |
| Reporter: | Jan Lindström | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 5.0.24a | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[14 Sep 2006 12:36]
Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW create table tw1; SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'coll%'; statements from the same environment where you got your results.
[14 Sep 2006 12:43]
Jan Lindström
mysql> SHOW create table tw1; +-------+-----------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------+ | tw1 | CREATE TABLE `tw1` ( `s1` varchar(1000) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'char%'; +--------------------------+---------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /home/jan/share/mysql/charsets/ | +--------------------------+---------------------------------+ 8 rows in set (0.01 sec) mysql> SHOW VARIABLES LIKE 'coll%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec)
[14 Sep 2006 12:53]
Valeriy Kravchuk
'ci" in collation name, latin1_swedish_ci, means "case-insensitive". Hence the results you've got. Read http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html.
Description: mysql> create table tw1 (s1 varchar(1000)) engine=myisam; Query OK, 0 rows affected (0.01 sec) mysql> insert into tw1 values ('KA'),('Ka'),('ka'),('kA'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select hex(s1) from tw1 where s1 = 'ka'; +---------+ | hex(s1) | +---------+ | 4B41 | | 4B61 | | 6B61 | | 6B41 | +---------+ 4 rows in set (0.01 sec) How to repeat: create table tw1 (s1 varchar(1000)) engine=myisam; insert into tw1 values ('KA'),('Ka'),('ka'),('kA'); select hex(s1) from tw1 where s1 = 'ka';