Why do trailing whitespaces in the field of sql-query produces "wrong" result?

Question

Suppose, we have a test table in MySql:

CREATE TABLE `test_table` (
    `_id` INT(10) UNSIGNED NOT NULL,
    `testfield` TEXT NULL COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`_id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM;

it contains a single row:

INSERT INTO `test_table` (`testfield`) VALUES ('testValue');

Then when I do this query:

SELECT * FROM `test_table` WHERE `testField` = 'testValue';

or this:

SELECT * FROM `test_table` WHERE `testField` = 'testValue ';

or even this:

SELECT * FROM `test_table` WHERE `testField` = 'testValue  ';

I always receive the same result, and it is this single row, but I want this row only in first case.

Why mysql truncate blank spaces at the end of 'testVaue '? And the main question, what should I do to eliminate such behavior?

score 1 · Answer 1 · answered Jul 27 '12 at 16:55

1

I have no idea why exactly it trims the WHERE (what's the point of it?). It has probably to do with binary stuff etc., since you can fix it by making your TEXT field binary (BLOB).

answered Jul 27 '12 at 16:55

Robin V.

91
5

score 1 · Answer 2 · answered Jul 27 '12 at 16:56

1

It's documented behavior: http://bugs.mysql.com/bug.php?id=5412. TEXT fields are treated the same as CHAR/VARCHAR for comparison purposes.

answered Jul 27 '12 at 16:56

Marc B

356,200
43
426
500

score 1 · Accepted Answer · edited May 23 '17 at 12:13

1

When MySQL compares strings it trims all trailing spaces ('x ' = 'x' is true).
See this question for more info.

edited May 23 '17 at 12:13

Community

1
1

answered Jul 27 '12 at 17:01

Vatev

7,493
1
32
39

Why do trailing whitespaces in the field of sql-query produces "wrong" result?

3 Answers3