Most likely primary keys in databases are numbers. But what happens, when an administrator uses the wrong numeric data type? In the worst case, databases can’t write down entries anymore.
For example, if an administrator wants to write customer information into the databases and wants to use the customerID itself as the primary key, then the numeric data type „TINYINT“ would cause that only 255 entries can be written. But on the other hand the „BIGINT“ numeric data type, could be too large for smaller databases.
So when you are setting up a database, you should think about how many entries will be written the next months/years and think about which datatype is the right one for your setup. Also you should think about if you should use the data types unsigned or not. This value will change the range of the datatypes.
Typ | signed | unsigned | ||
---|---|---|---|---|
Min | Max | Min | Max | |
TINYINT |
-128 | +127 | 0 | 255 |
SMALLINT |
-32.768 | +32.767 | 0 | 65.535 |
MEDIUMINT |
-8.388.608 | +8.388.607 | 0 | 16.777.215 |
INT/INTEGER |
-2.147.483.647 | +2.147.483.646 | 0 | 4.294.967.295 |
BIGINT |
-263 | +263 – 1 | 0 | 264 – 1 |
Another example:
If an administrator wants to store 60000 customer information in the database, he should use at least a „SMALLINT“. Should he use the unsigned version or not? Lets have a look.
With the signed data type he has a range from -32.768 up to +32.767, but no customerID (primary key as mentioned above) has a negative number, so a „unsigned SMALLINT“ would be necessary.
The case, that you thought about the questions above but your data type got out of range, could happen. There is a way to change the datatype and to increase the range in a simple way.
*ALTER TABLE tablename MODIFY column MEDIUMINT UNSIGNED;*
But remember: The larger your database is, the longer will it take to do such changes!
0 Kommentare