CODEWITHSUNDEEP
mysqldatabase
jmchauv
jmchauv

Reputation: 147

"=" not finding match for exact database value in MySQL, but LIKE works

I am not able to select from my adSpec table where the width value matches exactly, for a specific value. This does not happen for all cells within the column.

My adSpec Table

╔═══════════╦══════════════╗
║  adSpecId ║     width    ║
╠═══════════╬══════════════╣
║    100    ║   154.00800  ║
║    101    ║   319.96799  ║
║    102    ║   342.00000  ║
║    103    ║   342.00000  ║
╚═══════════╩══════════════╝

Does NOT return result

SELECT * FROM `adSpec` where `width` = '154.00800'

DOES return result

SELECT * FROM `adSpec` where `width` LIKE '154.00800'

Using "=" even works with other values in the same column

SELECT * FROM `adSpec` where `width` = '319.96799'

Note: There are no trailing spaces after the value within the db

Width Column Details

Type: float

Length: 10

Decimals: 5

Upvotes: 0

Views: 69

Answers (3)

Dylan Su
Dylan Su

Reputation: 6065

FLOAT is not reliable for precise comparison.

Quote:

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section B.5.4.8, “Problems with Floating-Point Values” https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html

Here is a demo on the unreliability:

mysql> create table adSpec(adSpecId int, width float(10,5));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into adSpec values(100,154.00800);
select *,Query OK, 1 row affected (0.01 sec)

mysql> select *, round(width, 20), width = '154.00800' from adSpec;
+----------+-----------+--------------------------+---------------------+
| adSpecId | width     | round(width, 20)         | width = '154.00800' |
+----------+-----------+--------------------------+---------------------+
|      100 | 154.00800 | 154.00799560546875000000 |                   0 |
+----------+-----------+--------------------------+---------------------+
1 row in set (0.00 sec)

If you want to do exact comparison, try decimal.

DECIMAL:

mysql> create table adSpec(adSpecId int, width decimal(10,5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into adSpec values(100,154.00800);
Query OK, 1 row affected (0.00 sec)

mysql> select *, round(width, 20), width = '154.00800' from adSpec;
+----------+-----------+--------------------------+---------------------+
| adSpecId | width     | round(width, 20)         | width = '154.00800' |
+----------+-----------+--------------------------+---------------------+
|      100 | 154.00800 | 154.00800000000000000000 |                   1 |
+----------+-----------+--------------------------+---------------------+
1 row in set (0.00 sec)

Upvotes: 3

Bastian
Bastian

Reputation: 261

A decimal(10,3) will have only 3 decimal places and can be compared in the same manner as integers.

http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html

Upvotes: 0

Rudy
Rudy

Reputation: 749

  1. Check for any white space on your database. If you are inserting in to your Database from a program use the functionality available to trim before inserting that way you should be able to get the exact search results based on your criteria.
  2. Make sure the to use like with '' and if the Data type column is number you can use = with out any ''

Upvotes: 0

Related Questions