Re: question about field length for integer
[EMAIL PROTECTED] wrote: 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 Shawn, I believe character keys are slower than integer keys, so I'd go with d. Could you explain what you mean by good idea?I think combining several pieces of information into a single value is a bad idea. Each piece of information means something on its own. It answers some question. Inevitably, you will want to know the answer to a question about one or some of them indepently of the rest. I think if it answers a different question, it should go in a separate column. We get this all the time: My company makes widgets. Each widget is uniquely identified in the widget table by the primary key, wid, which looks like this, AAA123-xyz2756. The letters and numbers before the - are the part category and part number. The letters and numbers after the - are the plant code and production run. Now the boss wants a report of all the parts with numbers between 103 and 137 from production run 2334 at any plant. How do I do that? The answer is ugly and cannot use an index. It would have been so much simpler if the four pieces of information were each in their own columns. That answer could use an appropriate index. As a first pass, a better design would be CREATE TABLE widgets ( id INT UNSIGNED NOT NULL PRIMARY KEY, cat CHAR(3), part_num TINYINT UNSIGNED, plant CHAR(3), run INT UNSIGNED, UNIQUE (cat, part_num, plant_id, run) ); This is option d with the name broken into its separate components. It is trivial to have the output of a query combine the pieces so as to present the familiar name: SELECT CONCAT(cat, part_num, '-', plant, run) AS 'wid', ... Of course, this design can still be normalized. cat and plant should be replaced by cat_id and plant_id, for example, which point to the appropriate rows of the category and plant tables. But you get the idea. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
RE: question about field length for integer
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]
Re: question about field length for integer
[EMAIL PROTECTED] wrote on 27/06/2005 16:33:44: Are you actually saying that you have a database with more than 1.8e+19 records in it? I don't think you do. If you were to add records at the rate of a million a second, which is, I think, beyond the capabilities of any foreseeable future hardware and software, it would still take half a million years to add that number of records. It is therefore fairly easy to deduce that the OP has not got, and will not have within any of our lifetimes, a database that big. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about field length for integer
Eko Budiharto [EMAIL PROTECTED] wrote on 06/27/2005 12:28:27 PM: 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. What I have in here, I have a table that use integer(BIGINT) for index/ID and auto increment. CREATE TABLE server ( ID bigint(20) 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) ) I use BIGINT because it has the capability auto_increment for index of records instead of using character. What I have in here, I am accessing my DB with ID, name. Any integer column can auto_increment. Just be careful you don't add enough records to exceed the storage limit of your auto_increment column. For example if you use a TINYINT column to store your ID value, you could add at most 127 new records before your auto_increment would start failing. I recommend INT as a storage type as it is not very often that you wind up with 2147483647 records in a table and it only uses half as many bytes (4) to store the value as BIGINT(8). If you had 200 rows and you used a BIGINT as your ID value, the space needed to store the data from just that one column would be 8*200 = 1600 bytes but if you had used a regular INT as your ID column it would have only used 4*200 = 800 bytes (about 8MB less disk space) That is a lot less data you need to move into memory whenever you need the PK index for that table. That's also a lot less data to read through when you search an index (Data bus speeds are fast but not infinitely so. It still takes some time to read a page of memory.). By the way, it is appropriate to respond to the list. Just in case I wasn't available to respond to you, someone else could answer your questions. It also gives the list a chance to learn from any answers you get. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
question about field length for integer
Hi, is there anyway that I can have more than 20 digits for integer (bigInt)? If not, what I can use for database index? - Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football
