What BIN column property in MySQL Workbench is used for?-CodePudding

I have done enough research and read the MySQL documentation, but I seem not to find a good explanation of the BINARY (BIN) column property in MySQL Table. Could someone explain when this should be checked and/or what is used for?

CodePudding user response：

The BIN column means the column uses a binary collation.

I tested this by creating a table with a VARCHAR datatype and I checked the BIN column in MySQL Workbench.

Then I viewed the DDL for the table in the command-line client:

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `title` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

You can see that the collation is utf8mb4_bin, which is the binary collation for that character set.

String comparisons to that column will use byte-by-byte comparison instead of using character equivalences according to any unicode-compatible collation.

So it's case-sensitive, and characters will compare as different even if they differ only in diacritics. For example 'e' = 'é' is false in binary comparisons.