Database Reference
In-Depth Information
You can use the datatype BIT interchangeably with BOOL, they are both the type just
described. If you want to store Boolean values it is best just to use TINYINT, and then
within your code determine what number you need to store for on or off, 0 or 1. Bear in
mind that if you are transferring your MySQL tables to another system that implements
BOOL in a different way, the scripts that you need to write to export the data will need to
create the actions necessary to reflect that difference. Further development of MySQL may
well address this difference in a better way.
DECIMAL
The DECIMAL datatype is used for storing numbers that are not whole numbers, where the
numbers after the decimal point are important. For instance, retailers often advertise prices
that are a couple of cents or pennies less than the dollar or pound, for example, the dish-
washer is $699.95, the computer is £599.99. Although these items cost the best part of $700
and £600 respectively, the fractional part of the number is important. If you stored the dish-
washer price as an integer, rounding down, and sold 1000 of these on your website, your
accounts would be out by $950. If you rounded the price up to $700, you still would have a
discrepancy of $50. The more that you multiply these number, the more the rounding error
costs (or makes!) you. Using an integer is therefore not recommended for storing numbers
where the decimal precision is important, especially currency. An integer would be fine for
storing the number of dishwashers that you have in stock, but not their prices.
MySQL provides the DECIMAL datatype to enable you to store precision fractions of
numbers. You declare the DECIMAL datatype as follows:
columnname DECIMAL(precision, decimals)
•
precision
is the number of digits that are needed to store the complete number. If you
are storing negative numbers the minus sign is included in these digits. If you do not
store a negative number, a positive number can use this space and so the positive num-
ber can be a factor of 10 greater.
•
decimals
is the number of digits to store after the decimal point.
The
precision
part of this is especially confusing, so Table 3.2 shows some examples of
the declaration and of what happens when you put different numbers into a column created
with that declaration.
Table 3.2
Decimal datatype examples.
Declaration
Number inserted
Number stored
DECIMAL(5, 2)
3.141 59
3.14
DECIMAL(5, 2)
42
42.00
DECIMAL(5, 2)
-23 453.543
-999.99
DECIMAL(5, 2)
4932.32
4932.32
DECIMAL(5, 2)
493 212 343.3423
9999.99
DECIMAL(10, 5)
49 321 234 532.3423
999 999.999 99
Search WWH ::
Custom Search