30

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!

PaiS
  • 1,282
  • 5
  • 16
  • 23
  • 2
    You don't need to make it Binary to make it case sensitive. If it is a varchar, just use collation = latin1_general_cs – Chiwda Mar 02 '18 at 10:20

5 Answers5

41
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;
dregad
  • 1,150
  • 8
  • 21
deinst
  • 18,402
  • 3
  • 47
  • 45
  • 15
    To quote the mysql documentation http://dev.mysql.com/doc/refman/5.1/en/alter-table.html, "If you want to change a column's type but not the name, CHANGE syntax still requires an old and new column name, even if they are the same." – deinst Aug 03 '10 at 12:33
  • In case anyone missed the docs, you can use `MODIFY` instead of `CHANGE` if you're not changing the column name. In that case you only need to provide the column name once. – wsams Feb 15 '18 at 20:21
  • @deinst, isn't VARCHAR(50) BINARY the same as VARBINARY(50) ? – Levsha Feb 08 '21 at 12:00
  • @Levsha not it's not; `VARCHAR(50) BINARY` results in `varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin` (actual charset may vary). – dregad Mar 27 '23 at 09:49
10

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.

defines
  • 10,229
  • 4
  • 40
  • 56
  • 1
    BINARY attribute is deprecated in MySQL 8.0. See my edit of https://stackoverflow.com/a/3396315/1045774 – dregad Mar 27 '23 at 10:22
5

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;
Robert Christopher
  • 4,940
  • 1
  • 20
  • 21
Falle1234
  • 5,013
  • 1
  • 22
  • 29
  • The following worked for me: ALTER TABLE UserAccounts CHANGE Password Password varchar(50) NOT NULL COLLATE utf8_bin; – Chris Prince Feb 13 '14 at 19:16
0

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

Sid K
  • 3
  • 1
-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?

SimonDowdles
  • 2,026
  • 4
  • 24
  • 36
  • Based on the above, a user name of SomeOne will return false. – SimonDowdles Aug 03 '10 at 12:12
  • 7
    This adds a significant overhead to every single query. If the data must always be returned binary why convert it on every single access? That is what we call "kludge". – defines Aug 03 '10 at 12:17
  • Perhaps I am not understanding, will the following statement mean a case SENSITIVE check in the future? ALTER TABLE USERS CHANGE USER_NAME USER_NAME VARCHAR(50) BINARY NOT NULL; As far as I understood each and every login authentication needed to be case sensitive. – SimonDowdles Aug 03 '10 at 12:21
  • Thanks everyone for the response, yes I managed to do it using the ALTER syntax provided by you guys. I was getting confused looking at the charset in the syntax, of the MySQL manual. – PaiS Aug 03 '10 at 18:24