Altering Mysql Table column to be case sensitive

Question

I have a table in my Mysql database, which is used for authentication. And now, I need to make the authentication case sensitive. Googling around, I have realized Mysql columns are case insensitive (contrary to Oracle) for search operations and the default behavior can be changed while creating the table by specifying the "binary" ie.

CREATE TABLE USERS
(
    USER_ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    USER_NAME VARCHAR(50) BINARY NOT NULL
)

Can someone please tell me how to alter the table in Mysql to add the "Binary" to an existing column of a DB?

Thanks!

Answer 1

Rather than altering your table, you can still perform case sensitive queries on your table when authenticating, use the BINARY option as follows:

SELECT BINARY * FROM USERS where USER_ID = 2 AND USER_NAME = 'someone' LIMIT 1;

Does this help?

Answer 2

You should be able to do something like this:

Edit: Misread what you intended to do:

ALTER TABLE USERS MODIFY
    USER_NAME VARCHAR(50)
      CHARACTER SET latin1
      COLLATE latin1_bin;

Answer 3

ALTER TABLE USERS CHANGE USER_NAME USER_NAME VARCHAR(50) BINARY NOT NULL;

Answer 4

Please see http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html

Example:

ALTER TABLE some_table MODIFY some_column BLOB;
ALTER TABLE some_table MODIFY some_column VARCHAR(50) BINARY;

The first line converts to a binary data type (attempt to minimize character loss) and the second converts back to the VARCHAR type with BINARY collation.

It may actually be preferable to store as one of the binary types (BLOB, BINARY, or VARBINARY) rather than simply collating BINARY. I would suggest you compare a bit, your mileage may vary depending on your actual data and the queries you need to run.