String comparable in MySQL query

Question

I created a table in MySQL:

DROP TABLE IF EXISTS `barcode`;
CREATE TABLE `barcode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(40) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `barcode` VALUES ('1', 'abc');

INSERT INTO `barcode` VALUES ('2', 'abc ');

Then I request data from the barcode of the table:

SELECT * FROM barcode WHERE `code` = 'abc ';

Result:

+-----+-------+
|  id | code  |
+-----+-------+
|  1  |  abc  |
+-----+-------+
|  2  |  abc  |
+-----+-------+

But I want the result set to have only 1 record. I work around with:

SELECT * FROM barcode WHERE `code` = binary 'abc ';

The result is 1 record. But I use NHibernate with MySQL to generate a query from a mapping table. So how to solve this case?

Answer 1

There is no other fix. Either you specify one comparison as binary, or you set the value for the entire database connection binary. (execution SET NAMES binarythat may have other side effects!)

, "" - MySQL, . ( !), binary, , , . " ", .

MySQL:

MySQL PADSPACE. , CHAR VARCHAR MySQL -

, , :

• WHERE field = 'abc ' AND CHAR_LENGTH(field) = CHAR_LENGTH('abc ')
• WHERE field REGEXP 'abc[[:space:]]'

, , . , .

: PADSPACE MySQLs [VAR] CHAR. ( ) , binary. .

Answer 2

:

SELECT * FROM barcode WHERE `code` REGEXP 'abc[[:space:]]'

Answer 3

, , LIMIT

SELECT * FROM barcode WHERE `code` = 'abc ' LIMIT 1;

, Collation

 SELECT *
 FROM barcode
 WHERE code COLLATE utf8_bin = 'abc';

Answer 4

:

SELECT * FROM barcode WHERE `code` = 'abc ' 
AND CHAR_LENGTH(`code`)=CHAR_LENGTH('abc ');

Answer 5

, LIKE (%), . STRCMP(text1, text2) mysql, . BINARY LIKE .

SELECT * FROM barcode WHERE `code` LIKE BINARY 'abc ';

Answer 6

Kaii "use LIKE":

"" LIKE,

and the example below shows what is 'Monty' = 'Monty 'true, but not 'Monty' LIKE 'Monty '.

However, if you use LIKE, be careful with literal strings containing characters '%', '_'or '\': '%'and '_'are wildcards, '\'used to remove sequences.