How to assign N bytes to store integer instead of 4 bytes or 8 bytes in mysql?

开发者_JS百科

Lets assume I want to store Indian mobile phone numbers in a MySQL table, since the number of records is very large, I would want to optimize my storage. An Indian mobile phone number is 10 digits and usually starting with 7,8 or 9 . eg 9XXXXXXXXX .

Relevant MySQL fields are :

Type    Storage     Minimum Value            Maximum Value
           (Bytes)        (Signed/Unsigned)     (Signed/Unsigned)

INT      4         -2147483648                2147483647
                           0                  4294967295 <-- somewhat small

BIGINT   8       -9223372036854775808       9223372036854775807
                       0                18446744073709551615 <-- too large

As seen, unsigned INT is little smaller ( about half in value ) , and unsigned BIGINT is very large to store a 10 digit number. 5 bytes are sufficient to store this data .

My questions are :-

a) Is there a good way to assign custom number of bytes and store this data in MYSQL in any type of available data type ?

b) Is this a premature optimization ? Is there any advantage/disadvantage at all trying to do this ?

c) What are the benefits of having natural size datatypes like 4 byte and 8 byte numbers,and ignoring intermediate size like 5 byte and 6 byte in databases ?

Note : Assume that 10 digit number is not a Phone number, but a 10 digit Integer. VARCHAR is not what I am looking for.

---

I agree with @diEcho that you should use VARCHAR() for phone numbers. In my lifetime I have had 6 digit, ,7 digit,9 digit and (as I am often in a different county and need to give the country code as well as my number) 13 digit numbers.

The rule of thumb here is unless you are going to do arithmetic or you really need comparisons like "20 > 9" to be true then leave this data as characters.

If you encounter a similar problem where you need a fixed number of digits that doesn't easily fit into INT or BIGINT, and, you need numeric comparisons then just use DECIMAL (without the decimal specifier) as in DECIMAL(9).

---

A phone nymber is (contrary to the name) not a number. Phone numbers, zip codes, serial numbers etc are textual data, so you should store that as text (CHAR(10)/VARCHAR(10+reserve) - ascii collation). This will result much larger storage requirements of course, but will be that large? How many numbers will you be storing anyway?

An IM called ICQ used to store phone numbers in numeric fields. How do I know that? Because my number starts with 0

---

Phone numbers are not really numbers, in that we don't do arithmetic with them. Quantities such as INTs and BIGINTs are 4 and 8 bytes because computers do arithmetic operations naturally with such sizes.

The natural thing to do is to treat the phone number as a CHAR(10). You may view this as inefficient - using 10 bytes where 5 would do, but considering your overall data volumes (surely other data such as names and addresses are very much bigger?) I doubt that "wasting" 5 bytes per record is going to cost much. Disk space is cheap, programmer time is expensive, do the simplest thing that can work.

---

dont use INT OR BIGINT..use VARCHAR(10) and sanitize the data before inserting into database..

there are many inbuilt function is MYSQL and php ( i assume you are using php )

and after all if you are bound to choose then use

BIGINT UNSIGNED