CODEWITHSUNDEEP
phpmysqlbigintegerbigint
Philll_t
Philll_t

Reputation: 4437

BIGINT in MySQL Changes a my number? Why does it do this?

I'll try and keep this simple. I'm using a BIGINT data type on a MySQL database table. I have a function generating a unique random number that may span anywhere from 12 digits to 13 digest long.

When I insert into the database with a digit that is 12 digits in length, it enters it just fine,

but when I use a value that is 13 digits or longer, it seems like it rounds up or something. here is the

php

$defaultText = 'some string'; //just some string
$web_id = 12;
$element_id = 23112182735363; //case 1 (doesn't work)
//$element_id = 2311218333205; //case 2, does work ()
  mysql_query("INSERT INTO tableName (web_id, element_id, content)
  VALUES ($web_id, $element_id, '".mysql_real_escape_string($defaultText)."')");

results: in case one, it inserts a slightly different number, usually rounds up for some reason.

in case two, it inserts the number just fine! Maybe someone can help shed some light on this mystery! Thanks again!

the big int datatype:

bigint(200)

Upvotes: 3

Views: 1439

Answers (2)

firebear
firebear

Reputation: 774

I assumed you were talking about a 32-bit server. But in my server, PHP seemed not lose the precision. echo(PHP_INT_MAX . "<br/>"); $big_int = -6174803190685607000; echo($big_int . '<br/>'); output 9223372036854775807<br/>-6174803190685607000<br/>

Sadly I still got the precision losing. I guessed it might because i used 'i' in prepare statement of mysqli, but I could not prove it.

Upvotes: 1

Ja͢ck
Ja͢ck

Reputation: 173642

Numbers lose their precision from PHP_INT_MAX onwards. See also: http://www.php.net/manual/en/reserved.constants.php#constant.php-int-max

After that they are turned into floats which have limited precision and causes weird rounding issues. The only way to support BIGINT in PHP is by using strings.

Upvotes: 8

Related Questions