-1

I have a table with VARCHAR(191) that actually uses a unique id which is constructed of 5 chars from these options: a-z, A-Z, 0-9 (example: 7hxYy)

I am performing a simple lookup with select * from table where token = 7hxYy yet I want the token to be case sensitive since I can actually query for 7hxyy and it will work.

How can I convert this column to binary without affecting the SQL select statements?

Broshi
  • 3,334
  • 5
  • 37
  • 52
  • Seems odd why would you force case sensitivity then want to override it with select queries. Can you clarify by example please. – P.Salmon Aug 24 '22 at 09:26
  • If you absolutely need to convert the column type to BINARY then all queries which uses this column will use case-sensitive collation without alternative. You may edit your queries. You may add generated column which converts your string value to binary one. There are more options... but you must describe your task completely. – Akina Aug 24 '22 at 09:44
  • I've edited the question, I just want to convert the column to binary without losing any data. – Broshi Aug 28 '22 at 07:25
  • [SQL Case Sensitive String Compare](https://stackoverflow.com/questions/3969059/sql-case-sensitive-string-compare) is an answer for MSSQL. The same thing works in MySQL, but MySQL has different COLLATION names (see `SHOW COLLATION`) – Luuk Aug 28 '22 at 07:53
  • The *real* question is how to use case-insensitive queries, not how to convert that value to binary. Case sensitivity is controlled by the column's collation. You don't need to convert to binary to get case-sensitive searches, just make sure you create the column with a case-sensitive collation. – Panagiotis Kanavos Aug 29 '22 at 09:19
  • If you changed to binary you'd also have to change the code that generates and saves the token, to make it generate BINARY values. You can't just type `where token = 7hxYy` no matter what the type is – Panagiotis Kanavos Aug 29 '22 at 09:21

1 Answers1

0

Try to modified the data type like:

alter table modify column token varchar(191) binary;

In MySQL, char vs char binary may affect the value case sensitive.

Or you can change your query sql like:

select * from table where BINARY `token`=‘7hxYy’;
Find Bugs
  • 125
  • 3