Oracle 5.0 Reference Manual Download

Page: 1023 / 3232

DECIMAL

Data Type Changes

1003

range of such literals differs 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 1 byte per digit or sign

character. Instead, a binary format is used that packs nine decimal digits into 4 bytes. This change
to

DECIMAL

storage format changes the storage requirements as well. The storage requirements for

the integer and fractional parts of each value are determined separately. Each multiple of nine digits
requires 4 bytes, and any remaining digits require some fraction of 4 bytes. The storage required for
remaining digits is given by the following table.

Leftover Digits

Number of Bytes

1–2

3–4

5–6

7–9

For example, a

DECIMAL(18,9)

column has nine digits on either side of the decimal point, so the

integer part and the fractional part each require 4 bytes. A

DECIMAL(20,6)

column has fourteen

integer digits and six fractional digits. The integer digits require four bytes for nine of the digits and 3
bytes for the remaining five digits. The six fractional digits require 3 bytes.

As a result of the change from string to numeric format for

DECIMAL

storage,

DECIMAL

columns

no longer store a leading

character or leading

digits. Before MySQL 5.0.3, if you inserted

+0003.1

into a

DECIMAL(5,1)

column, it was stored as

+0003.1

. As of MySQL 5.0.3, it is stored as

3.1

. For negative numbers, a literal

character is no longer stored. Applications that rely on the older

behavior must be modified to account for this change.

The change of storage format also means that

DECIMAL

columns no longer support the nonstandard

extension that permitted values larger than the range implied by the column definition. Formerly, 1
byte was allocated for storing the sign character. For positive values that needed no sign byte, MySQL
permitted an extra digit to be stored instead. For example, a

DECIMAL(3,0)

column must support a

range of at least

–999

999

, but MySQL would permit storing values from

1000

9999

as well, by

using the sign byte to store an extra digit. This extension to the upper range of

DECIMAL

columns is

no longer supported. As of MySQL 5.0.3, a

DECIMAL(M,D)

column permits at most

digits to the

left of the decimal point. This can result in an incompatibility if an application has a reliance on MySQL
permitting “too-large” values.

The SQL standard requires that the precision of

NUMERIC(M,D)

be exactly

digits. For

DECIMAL(M,D)

, the standard requires a precision of at least

digits but permits more. In MySQL,

DECIMAL(M,D)

and

NUMERIC(M,D)

are the same, and both have a precision of exactly

digits.

Summary of incompatibilities:

The following list summarizes the incompatibilities that result from changes to

DECIMAL

column and

value handling. You can use it as guide when porting older applications for use with MySQL 5.0.3 and
up.

• For

DECIMAL(M,D)

, the maximum

is 65, not 254.

• Calculations involving exact-value decimal numbers are accurate to 65 digits. This is fewer than

the maximum number of digits permitted before MySQL 5.0.3 (254 digits), but the exact-value
precision is greater. Calculations formerly were done with double-precision floating-point, which has
a precision of 52 bits (about 15 decimal digits).

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands