Change Collation To Allow Case Sensitive Seach In Varchar Fields

[konicsek]

Hello,

 

changing collation in F6 ALTER TABLE Dialog does not work for exsiting rows.

 

 

CREATE TABLE `_test`(  

  `_test_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `username` VARCHAR(100),

  PRIMARY KEY (`_test_id`)

) ENGINE=INNODB CHARSET=latin1 COLLATE=latin1_swedish_ci;

 

INSERT INTO `_test` (`username`) VALUES (‘Willi’);

INSERT INTO `_test` (`username`) VALUES (‘willi’);

 

Same result for different statements.

mysql> SELECT * FROM _test WHERE username = ‘Willi’;

+

---

+

---

+

| _test_id | username |

+

---

+

---

+

|        1 | Willi    |

|        2 | willi    |

+

---

+

---

+

2 rows in set (0.00 sec)

mysql> SELECT * FROM _test WHERE username = ‘willi’;

+

---

+

---

+

| _test_id | username |

+

---

+

---

+

|        1 | Willi    |

|        2 | willi    |

+

---

+

---

+

 

Ok, this is documented. MySql is not case sensitive for varchar fields by default.

 

Try F6 “ALTER TABLE” Tab in SQLyog

change collation to latin_bin

ALTER TABLE `_test`  COLLATE=latin1_bin;

 

Same result, still not case sensitive

SELECT * FROM _test WHERE username = ‘Willi’;

SELECT * FROM _test WHERE username = ‘willi’;

 

But this works:

mysql> ALTER TABLE _test CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

Query OK, 2 rows affected (0.05 sec)

Records: 2  Duplicates: 0  Warnings: 0

 

mysql> SELECT * FROM _test WHERE username = ‘Willi’;

+

---

+

---

+

| _test_id | username |

+

---

+

---

+

|        1 | Willi    |

+

---

+

---

+

1 row in set (0.00 sec)

 

mysql> SELECT * FROM _test WHERE username = ‘willi’;

+

---

+

---

+

| _test_id | username |

+

---

+

---

+

|        2 | willi    |

+

---

+

---

+

1 row in set (0.00 sec)

[peterlaursen]

This is not an issue with SQLyog.  This is how the server behaves.

An ALTER TABLE statement changing the charset and/or collation of a table as such ony deines a rul for new clumns adn do not change anything as regards existing columns. 

Since your are pasting from the ‘mysql’ command line client you probably also realize this (?).

 

 

You have 2 options.

1) Execute an ALTER TABLE statement where charset/collation is changed at the column level. In SQLyo ALTE TABLE dialog there is a checkbox on upper right corner reading “Hide language options”.  If you unheck this checkbox, two more columns will become visible in the GUI. You will be able to define charset and collaton at the column level. Please try it and please read in HISTORY tab wat the generated statement looks like.

2) Alternatively you can always use a COLLATE-clause in a select query (refer http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html).

[Author]

Posts