Search code examples
mysqlmysql-workbench

What BIN column property in MySQL Workbench is used for?

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? enter image description here

Solution

  • 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.