Why does MySQL return results that don't appear to match the query?

January 26, 2022

Given the following database:

CREATE TABLE `test` (
  `pk` smallint NOT NULL,
  `vc` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test` (`pk`, `vc`) VALUES
(1, '3A61034C-A476-4615-9759-BC39F82DC511'),
(2, '4X227');

ALTER TABLE `test`
  ADD PRIMARY KEY (`pk`);

If I run the following queries I get unexpected results:

SELECT * FROM test WHERE vc=3;
  result: 1,3A61034C-A476-4615-9759-BC39F82DC511

SELECT * FROM test WHERE vc=4;
  result: 2,4X227

SELECT * FROM test WHERE vc=5;
  (no results)

I see that it gets results where vc starts with the number being queried for, but I don’t understand why it would behave that way. I would have expected no results for all three queries. I have reproduced this in MySQL 5.6,5.7 and 8.0.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d7c63d51b104d5da303314c332e2ec20