how to store a 10 digit number into sql bigint datatype?

Question

When I try to enter value through PHP, 2147483647 is getting stored.

NOTE: 2147483647 is the max size for int datatype not bigint in mysql.

Answer 1

Maximum value that can be stored in MySQL INT datatype is 2147483647.

MySQL will silently truncate larger values down to the maximum supported value.

This is expected (i.e. documented) behavior.

Reference: https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

datataype     maximum signed value
---------     --------------------
TINYINT                        127
SMALLINT                     32767
MEDIUMINT                   388607
INT                     2147483647
BIGINT                      2^63-1

If we are seeing this behavior storing to a BIGINT column, then somewhere in the code path, the value got converted to INT datatype, which truncated the value, before the value was assigned to the BIGINT column. This could occur as the return from a function call, or in an assignment of the value in a TRIGGER, et al.

It's not possible to more precisely diagnose the issue with the information provided.

---

How to store a value to a BIGINT column?

We can send a string literal in a SQL statement. As a demonstration, twelve decimal digits

 CREATE TABLE foo (mycol BIGINT); 
 INSERT INTO foo (mycol) VALUES ('987645312745');
 SELECT mycol FROM foo;

         mycol
 -------------
  987645312745

Answer 2

I see no issue:

create table my_number (
  val bigint
);

insert into my_number (val) values (1234567890);
insert into my_number (val) values (123456789012345678);

select * from my_number;

Result:

val                   
----------------------
1234567890            
123456789012345678    

Edit: Adding new example posted as comment:

CREATE TABLE contacts_db (
  Fname varchar(15) NOT NULL,
  Lname varchar(15) NOT NULL,
  Email varchar(35) NOT NULL,
  Phone bigint(10) UNSIGNED NOT NULL
);

insert into contacts_db (Fname, Lname, Email, Phone) 
                 values ('a', 'b', 'c', 1234567890);
insert into contacts_db (Fname, Lname, Email, Phone) 
                 values ('d', 'e', 'f', 123456789012345678);

select * from contacts_db;

Result:

Fname  Lname  Email  Phone
-----  -----  -----  ------------------
a      b      c      1234567890
d      e      f      123456789012345678

Still works well.

Answer 3

Thanks guys for answering the question. The problem was due to a typecasting done on the PHP side, by mistake I typecasted the value to (int) while retrieving the input from HTML page.