CODEWITHSUNDEEP
mysql
PaiS
PaiS

Reputation: 1282

Altering Mysql Table column to be case sensitive

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!

Upvotes: 30

Views: 36936

Answers (5)

Sid K
Sid K

Reputation: 1

You can go to tables ,find your table ...right click on it .. then choose design option ... Once the design window opens .. under 'Table designer' ( the bottom pane) ..

Find collation .. it will be set to 'database default' .. change it to Sql_latin_general_cp1_Cs_As( you'll find this option in the drop-down )

Here cs in the above option stands for case sensitive ..

Now click save ..

Your column will be case sensitive now

Upvotes: 0

deinst
deinst

Reputation: 18802

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

For string data types, the BINARY attribute is a nonstandard MySQL extension that is shorthand for specifying the binary collation of the character set. As of MySQL 8.0.27, this usage is deprecated [1][2]. A character set with _bin collation should be used instead, e.g.

ALTER TABLE users MODIFY user_name VARCHAR(50) COLLATE utf8mb4_bin;

Upvotes: 42

Falle1234
Falle1234

Reputation: 5063

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 binary;

Upvotes: 5

defines
defines

Reputation: 10534

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.

Upvotes: 10

SimonDowdles
SimonDowdles

Reputation: 2046

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?

Upvotes: -1

Related Questions