5.2. Numeric Data Types


For storing numeric data, MySQL provides integer data types, floating-point types that store approximate-value numbers, a fixed-point type that stores exact-value numbers, and a BIT type for bit-field values. When you choose a numeric data type, consider the following factors:

  • The range of values the data type represents

  • The amount of storage space that column values require

  • The display width indicating the maximum number of characters to use when presenting column values in query output

  • The column precision and scale for floating-point and fixed-point values

Precision and scale are terms that apply to floating-point and fixed-point values, which can have both an integer part and a fractional part. Precision is the number of significant digits. Scale is the number of digits to the right of the decimal point.

5.2.1. Integer Data Types

Integer data types include TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. Smaller integer types require less storage space, but are more limited in the range of values they represent. For example, TINYINT column values take only one byte each to store, but the type has a small range ( 128 to 127). INT column values require four bytes each, but the type has a much larger range ( 2,147,483,648 to 2,147,483,647). The integer data types are summarized in the following table, which indicates the amount of storage per value that each type requires as well as its range. For integer values declared with the UNSIGNED attribute, negative values are not allowed, and the high end of the range shifts upward to approximately double the maximum positive value of the signed range.

Type

Storage Required

Signed Range

Unsigned Range

TINYINT

1 byte

128 to 127

0 to 255

SMALLINT

2 bytes

32,768 to 32,767

0 to 65,535

MEDIUMINT

3 bytes

8,388,608 to 8,388,607

0 to 16,777,215

INT

4 bytes

2,147,683,648 to 2,147,483,647

0 to 4,294,967,295

BIGINT

8 bytes

9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

0 to 18,446,744,073, 709,551,615


Integer data types may be declared with a display width, which affects the number of digits used to display column values in query output. For example, assume that you declare an INT column with a display width of 4 like this:

 century INT(4) 

The result is that values in the century column usually are displayed four digits wide. However, it's important to understand that the display width is unrelated to the range of the data type. The display width specified for a column affects only the maximum number of digits MySQL uses to display column values. Values shorter than the display width are padded with spaces as necessary. Note also that the display width is not a hard limit; it won't cause output truncation of a value that's too long to fit within the width. Instead, the full value is shown. For example, assume that you've inserted the number 57622 into the century column. When you SELECT the column in a query, MySQL displays the entire value (57622), not just the first four digits of the value.

The display width for integer types also is unrelated to storage requirements. For example, an INT(4) column does not require half as much storage per value as INT(8). All values for the INT data type require four bytes.

If you specify no display width for an integer type, MySQL chooses a default based on the number of characters needed to display the full range of values for the type (including the minus sign, for signed types). For example, SMALLINT has a default display width of 6 because the widest possible value is -32768.

5.2.2. Floating-Point Data Types

The floating-point data types include FLOAT and DOUBLE. Each of these types may be used to represent approximate-value numbers that have an integer part, a fractional part, or both. FLOAT and DOUBLE data types represent values in the native binary floating-point format used by the server host's CPU. This is a very efficient type for storage and computation, but values are subject to rounding error.

FLOAT represents single-precision floating-point values that require four bytes each for storage. DOUBLE represents double-precision floating-point values that require eight bytes each for storage.

You can specify explicit precision and scale values in the column definition to indicate the number of significant digits and the number of decimal places to the right of the decimal point. The following definitions specify a single-precision column with a precision of 10 digits and scale of 3 decimals, and a double-precision column with a precision of 20 digits and scale of 7 decimals:

 weight FLOAT(10,3) avg_score DOUBLE(20,7) 

If you specify no precision or scale, MySQL represents values stored in FLOAT and DOUBLE columns to the maximum accuracy allowed by the hardware of the MySQL server host. The following definitions include no explicit precision or scale:

 float_col FLOAT double_col DOUBLE 

Floating-point values are stored using mantissa/exponent representation, which means that the precision is defined by the width of the mantissa and the scale varies depending on the exponent value. The result of these factors is that stored values are approximate.

5.2.3. Fixed-Point Data Types

The fixed-point data type is DECIMAL. It is used to represent exact-value numbers that have an integer part, a fractional part, or both.

DECIMAL uses a fixed-decimal storage format: All values in a DECIMAL column have the same number of decimal places and are stored exactly as given when possible. DECIMAL values are not processed quite as efficiently as FLOAT or DOUBLE values (which use the processor's native binary format), but DECIMAL values are not subject to rounding error, so they are more accurate. In other words, there is an accuracy versus speed tradeoff in choosing which type to use. For example, the DECIMAL data type is a popular choice for financial applications involving currency calculations, because accuracy is most important.

DECIMAL columns may be declared with a precision and scale to indicate the number of significant digits and the number of decimal places to the right of the decimal point. For example, if you want to represent values such as dollar-and-cents currency figures, you can do so using a two-digit scale:

 cost DECIMAL(10,2) 

The precision and scale can be omitted, or just the scale. The defaults for omitted precision and scale are 10 and 0, respectively, so the following declarations are equivalent:

 total DECIMAL total DECIMAL(10) total DECIMAL(10,0) 

The amount of storage required for DECIMAL column values depends on the precision and scale. Approximately four bytes are required per nine digits on each side of the decimal point.

The NUMERIC data type in MySQL is a synonym for DECIMAL. (If you declare a column as NUMERIC, MySQL uses DECIMAL in the definition.) Standard SQL allows for a difference between the two types, but in MySQL they are the same. In standard SQL, the precision for NUMERIC must be exactly the number of digits given in the column definition. The precision for DECIMAL must be at least that many digits but is allowed to be more. In MySQL, the precision is exactly as given, for both types.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net