Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

String compare exact in query MySQL

I created table like that 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 query data from table barcode:

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

The result is:

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

But I want the result set is only 1 record. I workaround with:

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

The result is 1 record. But I'm using NHibernate with MySQL for generating query from mapping table. So that how to resolve this case?

like image 756
Tang Khai Phuong Avatar asked Apr 27 '12 07:04

Tang Khai Phuong


People also ask

How do I match a string in MySQL?

STRCMP() function in MySQL is used to compare two strings. If both of the strings are same then it returns 0, if the first argument is smaller than the second according to the defined order it returns -1 and it returns 1 when the second one is smaller the first one.

How do I match part of a string in SQL?

SQL Pattern Matching : It is used for searching a string or a sub-string to find certain character or group of characters from a string. We can use LIKE Operator of SQL to search sub-string. The LIKE operator is used with the WHERE Clause to search a pattern in string of column.

How do I select specific in MySQL?

If you want to select only specific columns, replace the * with the names of the columns, separated by commas. The following statement selects just the name_id, firstname and lastname fields from the master_name table.


2 Answers

There is no other fix for it. Either you specify a single comparison as being binary or you set the whole database connection to binary. (doing SET NAMES binary, which may have other side effects!)

Basically, that 'lazy' comparison is a feature of MySQL which is hard coded. To disable it (on demand!), you can use a binary compare, what you apparently already do. This is not a 'workaround' but the real fix.

from the MySQL Manual:

All MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces

Of course there are plenty of other possiblities to achieve the same result from a user's perspective, i.e.:

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

The problem with these is that they effectively disable fast index lookups, so your query always results in a full table scan. With huge datasets that makes a big difference.

Again: PADSPACE is default for MySQLs [VAR]CHAR comparison. You can (and should) disable it by using BINARY. This is the indended way of doing this.

like image 98
Kaii Avatar answered Sep 19 '22 06:09

Kaii


You can try with a regular expression matching :

SELECT * FROM barcode WHERE `code` REGEXP 'abc[[:space:]]'
like image 31
aleroot Avatar answered Sep 20 '22 06:09

aleroot