If you really need more than 20 digits of accuracy and can move to 5.0.3 + you can use the Decimal data type without losing precision.
This is out of Chapter 23. Precision Math The maximum value of 64 for M means that calculations on DECIMAL values are accurate up to 64 digits. This limit of 64 digits of precision also applies to exact-value numeric literals, so the maximum range of such literals is different from before. (Prior to MySQL 5.0.3, decimal values could have up to 254 digits. However, calculations were done using floating-point and thus were approximate, not exact.) This change in the range of literal values is another possible source of incompatibility for older applications. Values for DECIMAL columns no longer are represented as strings that require one byte per digit or sign character. Instead, a binary format is used that packs nine decimal digits into four bytes. This change to DECIMAL storage format changes the storage requirements as well. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the "leftover" digits require some fraction of four bytes. For example, a DECIMAL(18,9) column has nine digits on each side of the decimal point, so the integer part and the fractional part each require four bytes. A DECIMAL(20,10) column has 10 digits on each side of the decimal point. Each part requires four bytes for nine of the digits, and one byte for the remaining digit. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, June 27, 2005 10:34 AM To: Eko Budiharto Cc: mysql@lists.mysql.com Subject: Re: question about field length for integer Eko Budiharto <[EMAIL PROTECTED]> wrote on 06/26/2005 11:02:30 AM: > Hi, > is there anyway that I can have more than 20 digits for integer > (bigInt)? If not, what I can use for database index? BIGINT UNSIGNED can range from 0 to 18446744073709551615 (http://dev.mysql.com/doc/mysql/en/numeric-types.html) Are you actually saying that you have a database with more than 1.8e+19 records in it? I don't think you do. I think you are combining several pieces of information into something that looks like a number and it's exceeding the storage limits of even BIGINT. What you have is actually a "good idea" but you are physically limited by the capacity of the column types available. In this case if you cannot create all of your key values so that they look like numbers smaller than 18446744073709551615, it can't fit into a BIGINT UNSIGNED column. You do have some options: a) change the way you create your server keys so that they fit in the value allowed b) use a character-based column to store your server key values c) use some other value to identify your servers (IP address, for example) d) create a table of server keys: CREATE TABLE server ( ID int auto_increment , name varchar(25) not null , ip int unsigned , ... (any other fields you could define to describe this server) , PRIMARY KEY (ID) , UNIQUE(name) ) Then, refer to your servers using server.id instead of your composited key. e) ...? (I am sure there are more ideas from others on the list) To answer your literal question: No, MySQL cannot store integer values that contain more than 20 digits. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
