MYsql BigInt(16)

Archives Bug Discussions
1

Hi

I am trying to insert a number into a BigInt(16) column on a mysql database but it appears to only insert the number partially :

The number I am trying to insert            20081103233503

The number that gets stored in the table      2147483647

2

thanks. it's fixed.

3

Awesome  ;D

4

I reopen this old topic because for me a similar problem concerns the mysql "int" data type when it has the option "Unsigned", infact int unsigned should accept till 4294967295, but it is registered at maximum 2147483647 (probably due to the php integer limit). Is possible to do something for this? thanks

5

Yeah, PHP doesn't have unsigned concept. So in your case it is better to use bigint to avoid loss of precision.

6

Quote

Yeah, PHP doesn't have unsigned concept. So in your case it is better to use bigint to avoid loss of precision.

As I see, bigint is considered in Yii as a double (class CMysqlColumnSchema function extractType) could be a solution to consider also "int" as a double?

I've modified temporary the above function by changing the initial test to this:

if(strpos($dbType,‘bigint’)!==false || strpos($dbType,‘float’)!==false || strpos($dbType,‘double’)!==false || strtolower(substr($dbType, 0, 3))==‘int’)

I tested it and now the int unsigned are correctly saved till the maximum (4294967295)

For me is not a problem to use Bigint but I think other users are using or could use unsigned int in mysql database…

7

The problem with using double to represent integer is that it doubles the memory requirement. In most cases, we use integers to represent relatively small numbers. In the case like yours, using bigint is better (it is not very common that we would need to store numbers that fall into the range of 2147483647 and 4294967295. Should this happen, using bigint is safer anyway).

8

Quote

The problem with using double to represent integer is that it doubles the memory requirement. In most cases, we use integers to represent relatively small numbers. In the case like yours, using bigint is better (it is not very common that we would need to store numbers that fall into the range of 2147483647 and 4294967295. Should this happen, using bigint is safer anyway).

OK I agree, I'm going to use bigint, but just one warning about the field validation for bigint, if I use "numerical" validation (CNumberValidator) with integerOnly=true, and I set a max value for example 9999999999, the max check doesn't function because there's a "int($value)" that converts the value inserted by the user to the maximum integer (2147483647), so if the user inserts 10000000000 it is accepted.

To bypass thiss I create my validator class without the casting.

9

Good catch. I just checked in the fix.