Charl van Niekerk » Blog

Main

Latest

Archives

Powered by Blogger

MySQL BIGINT Calculation Weirdness

Check these out:

mysql> select 5 + 5;
+-------+
| 5 + 5 |
+-------+
|    10 | 
+-------+
1 row in set (0.00 sec)

mysql> select '5' + '5';
+-----------+
| '5' + '5' |
+-----------+
|        10 | 
+-----------+
1 row in set (0.00 sec)

mysql> select 4000000000000000000 + 1;
+-------------------------+
| 4000000000000000000 + 1 |
+-------------------------+
|     4000000000000000001 | 
+-------------------------+
1 row in set (0.00 sec)

mysql> select '4000000000000000000' + 1;
+---------------------------+
| '4000000000000000000' + 1 |
+---------------------------+
|                     4e+18 | 
+---------------------------+
1 row in set (0.00 sec)

mysql> select '4000000000000000000';
+---------------------+
| 4000000000000000000 |
+---------------------+
| 4000000000000000000 | 
+---------------------+
1 row in set (0.00 sec)

So basically if you surround the number with quotes, it would first take it as a string, but if it needs to convert it, it would not convert it optimally to a BIGINT and thereby you lose accuracy.

This sounds like a petty issue but it really screwed me around the other day.

Posted by Charl van Niekerk on Friday, July 18, 2008
Get TimesURL

1 Comments

Comment by OpenID thakadu on Sunday, July 27, 2008 3:30:00 PM

Hi Charl

You may be aware already but MySQL autoconversion gets way weirder. Say you have a table that has a numeric primary key called id, enetering:

select * from my_table where id='100';

will return the same as:

select * from my_table where id=100;

That may be acceptable but whats worse is this
also returns the same thing:

select * from my_table where id='100anyjunk';

!

Post a Comment

Copyright © 2004-2009 Charl van Niekerk. All articles are released under the Creative Commons Attribution 2.5 South Africa licence, unless where otherwise stated.