CREATE TABLE `tmp` (
  `wid` bigint unsigned NOT NULL,
  `simhash` binary(255) NOT NULL,
  PRIMARY KEY (`wid`),
  UNIQUE KEY `u_simhash` (`simhash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
TRUNCATE `tmp`;
insert into tmp set wid=4, simhash=b'10';
SELECT wid,bin(simhash+0) FROM tmp;

As above, I want to insert a binary string '10' (equals to decimal 2) to a binary field.

But it fails. the result of bin(simhash+0) is 0, not 10 as I expected.

So what is the problem? I just want to insert binary string (like '1000110101101....') to the binary field.

• Env: MySQL 8.0.40
• Client: MySQL Workbench 8.0.40

PS: Use SELECT bin(simhash+0) to get the binary string of the field. E.g. display '10' for the decimal number 2.

CREATE TABLE `tmp` (
  `wid` bigint unsigned NOT NULL,
  `simhash` binary(255) NOT NULL,
  PRIMARY KEY (`wid`),
  UNIQUE KEY `u_simhash` (`simhash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
TRUNCATE `tmp`;
insert into tmp set wid=4, simhash=b'10';
SELECT wid,bin(simhash+0) FROM tmp;

As above, I want to insert a binary string '10' (equals to decimal 2) to a binary field.

But it fails. the result of bin(simhash+0) is 0, not 10 as I expected.

So what is the problem? I just want to insert binary string (like '1000110101101....') to the binary field.

• Env: MySQL 8.0.40
• Client: MySQL Workbench 8.0.40

PS: Use SELECT bin(simhash+0) to get the binary string of the field. E.g. display '10' for the decimal number 2.

• mysql
• mysql-8.0

• You might want to check the warnings you get (you will get the warning Truncated incorrect DOUBLE value: '\x02\x00\x00...'). – Progman Commented Jan 18 at 20:46
• Please edit your question to explain why you use the BIN() function in your SELECT statement. The column is already "binary". – Progman Commented Jan 18 at 20:49
• no warning at all. use SELECT BIN() is to get the binary string (only contains 0 or 1) – AarioAi Commented Jan 18 at 20:52
• There are warnings, see dbfiddle.uk/705gO2u6 – Progman Commented Jan 18 at 20:56
• the warning seems useless at all. insert into tmp set wid=4, simhash='2'; SELECT wid,bin(simhash+0) FROM tmp; SHOW WARNINGS; – AarioAi Commented Jan 18 at 21:07

1 Answer 1

CREATE TABLE `tmp` (
  `wid` bigint unsigned NOT NULL,
  `simhash` varbinary(255) NOT NULL,  -- Use VARBINARY instead of BINARY
  PRIMARY KEY (`wid`),
  UNIQUE KEY `u_simhash` (`simhash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

TRUNCATE `tmp`;

-- Insert the binary string '10' (which is 2 in decimal)
INSERT INTO `tmp` (`wid`, `simhash`) VALUES (4, UNHEX('02'));

-- Select the data to verify
SELECT `wid`, HEX(`simhash`) AS `simhash_hex`, BIN(`simhash` + 0) AS `simhash_bin` FROM `tmp`;