MySQL 8: insert binary string failed

Question

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.

You might want to check the warnings you get (you will get the warning Truncated incorrect DOUBLE value: '\x02\x00\x00...'). — Progman
– 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
– 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) — Aario
– Aario, Commented Jan 18 at 20:52
the warning seems useless at all. insert into tmp set wid=4, simhash='2'; SELECT wid,bin(simhash+0) FROM tmp; SHOW WARNINGS; — Aario
– Aario, Commented Jan 18 at 21:07

Ahmed Amin Shahin · Accepted Answer · 2025-01-18 22:37:34Z

0

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`;

answered Jan 18 at 22:37

Ahmed Amin Shahin

1,2018 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL 8: insert binary string failed

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related