[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]
