Below is the list of changes that have just been committed into a local
mysqldoc repository of monty. When monty does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.2780 05/03/30 15:22:32 monty@stripped +1 -0
Added some information about the new @code{DECIMAL} type.
Added more information about automatic convertation of column types
Changelog 5.0.4
Docs/manual.texi
1.2614 05/03/30 15:22:30 monty@stripped +69 -21
Added some information about the new @code{DECIMAL} type.
Added more information about automatic convertation of column types
Changelog 5.0.4
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: monty
# Host: narttu.mysql.com
# Root: /home/my/mysqldoc
--- 1.2613/Docs/manual.texi 2005-03-29 23:01:42 +03:00
+++ 1.2614/Docs/manual.texi 2005-03-30 15:22:30 +03:00
@@ -28391,7 +28391,7 @@
@item @code{Packed}
MySQL tries to pack keys with a common suffix. This can only be used
-for indexes on @code{CHAR}, @code{VARCHAR}, or @code{DECIMAL} columns. For
+for indexes on @code{CHAR}, @code{VARCHAR} columns. For
long indexed strings that have similar leftmost parts,
this can significantly reduce the space used. In the third example
above, the fourth key is 10 characters long and a 60% reduction in space is
@@ -45189,6 +45189,25 @@
@c TODO: update for precision math
+For MySQL 5.0.3 and above:
+
+An packed 'exact' fixed-point number. @var{M} is the total number of
+digits and @var{D} is the number of decimals. The decimal point and
+(for negative numbers) the @samp{-} sign are not counted in @var{M},
+although space for them is reserved. If @var{D} is 0, values have no
+decimal point or fractional part. The maximum number of digits
+(@var{M}) for @code{DECIMAL} is 64. The maximum number of supported
+decimals (@var{D}) is 30. If @code{UNSIGNED} is specified, negative
+values are disallowed.
+
+If @var{D} is omitted, the default is 0. If @var{M} is omitted, the
+default is 10.
+
+All basic calculation (@code{+,-,*,/}) with @code{DECIMAL} columns are
+done with 56 bit precision.
+
+For MySQL version before 5.0.3:
+
An unpacked fixed-point number. Behaves like a @code{CHAR} column;
``unpacked'' means the number is stored as a string, using one character for
each digit of the value. @var{M} is the total number of digits and
@@ -45203,8 +45222,10 @@
If @var{D} is omitted, the default is 0. If @var{M} is omitted, the
default is 10.
-Prior to MySQL 3.23, the @var{M} argument must be large enough to
-include the space needed for the sign and the decimal point.
+Prior to MySQL 3.23:
+
+Like above, but with the exception that @var{M} argument had to be large
+enough to include the space needed for the sign and the decimal point.
@tindex DEC data type
@tindex NUMERIC data type
@@ -47300,18 +47321,23 @@
best type. Of the types that represent all the required values, it uses
the least amount of storage.
-@c TODO: update for precision math
-
-Accurate representation of monetary values is a common problem. In MySQL,
-you should use the @code{DECIMAL} type. This is stored as a string, so no
-loss of accuracy should occur. (Calculations on @code{DECIMAL} values may
-still be done using double-precision operations, however.) If accuracy
-is not too important, the @code{DOUBLE} type may also be good enough.
+Tables created with MySQL 5.0.3 and above uses a a new storage format
+for @code{DECIMAL} columns. All basic calculation (@code{+,-,*,/}) with
+@code{DECIMAL} columns are done with 56 bit precision.
+@xref{Numeric type overview}.
+
+For earlier MySQL version accurate representation of monetary values was
+a common problem. In these MySQL version, you should also use the
+@code{DECIMAL} type. In this case the value is stored as a string, so no
+loss of accuracy should occur on storage. Calculations on these
+@code{DECIMAL} values are however done using double-precision
+operations. If accuracy is not too important or if speed is important,
+the @code{DOUBLE} type may also be good enough.
For high precision, you can always convert to a fixed-point type stored
-in a @code{BIGINT}. This allows you to do all calculations with integers
-and convert results back to floating-point values only when necessary.
-
+in a @code{BIGINT}. This allows you to do all calculations with 64 bit
+integers and convert results back to floating-point values only when
+necessary.
@node Other-vendor column types, , Choosing types, Column types
@section Using Column Types from Other Database Engines
@@ -47319,6 +47345,8 @@
@cindex types, portability
@cindex portability, types
@cindex columns, other types
+@tindex data type, BOOL
+@tindex data type, BOOLEAN
To make it easier to use code written for SQL implementations from other
vendors, MySQL maps column types as shown in the following table. These
@@ -47328,7 +47356,11 @@
@multitable @columnfractions .35 .55
@item @strong{Other Vendor Type} @tab @strong{MySQL Type}
@item @code{BINARY(@var{M})} @tab @code{CHAR(@var{M}) BINARY} (before MySQL 4.1.2)
+@item @code{BOOL} @tab @code{TINYINT}
+@item @code{BOOLEAN} @tab @code{TINYINT}
@item @code{CHAR VARYING(@var{M})} @tab @code{VARCHAR(@var{M})}
+@item @code{DEC} @tab @code{DECIMAL}
+@item @code{FIXED} @tab @code{DECIMAL} (MySQL 4.1.0 on)
@item @code{FLOAT4} @tab @code{FLOAT}
@item @code{FLOAT8} @tab @code{DOUBLE}
@item @code{INT1} @tab @code{TINYINT}
@@ -47340,6 +47372,7 @@
@item @code{LONG VARCHAR} @tab @code{MEDIUMTEXT}
@item @code{LONG} @tab @code{MEDIUMTEXT} (MySQL 4.1.0 on)
@item @code{MIDDLEINT} @tab @code{MEDIUMINT}
+@item @code{NUMERIC} @tab @code{DECIMAL}
@item @code{VARBINARY(@var{M})} @tab @code{VARCHAR(@var{M}) BINARY} (before MySQL 4.1.2)
@end multitable
@@ -50872,7 +50905,8 @@
description. All other characters are just taken verbatim, thus not being
interpreted.
If @var{str} contains an illegal date, time, or datetime value,
-@code{STR_TO_DATE()} returns @code{NULL}.
+@code{STR_TO_DATE()} returns @code{NULL}. Starting from MySQL 5.0.3 an illegal
+value will also produce a warning.
@c end_description_for_help_topic
@c example_for_help_topic STR_TO_DATE
@@ -65233,8 +65267,8 @@
@itemize @bullet
@item
-All @code{CHAR}, @code{NUMERIC}, and @code{DECIMAL} columns are space-padded
-to the column width.
+@code{CHAR} columns (and @code{NUMERIC}, and @code{DECIMAL} created before
+MySQL 5.0.3) are space-padded to the column width.
@item
Very quick.
@@ -96177,6 +96211,13 @@
@node Problems with float, , No matching rows, Query Issues
@appendixsubsec Problems with Floating-Point Comparisons
+Note that the following section is mainly relevant for MySQL version older
+than 5.0.3. In MySQL 5.0.3 and above we are calculating operations with
+@code{DECIMAL} with 56 bit precession, which should solve most common
+inaccuracy problems when it comes to @code{DECIMAL} columns.
+For @code{DOUBLE} and @code{FLOAT} columns the problems still remains
+as inexactness is the basic nature of floating point numbers.
+
@c TODO: update for precision math
Floating-point numbers sometimes cause confusion because they
@@ -96184,12 +96225,14 @@
can see on the screen usually is not the exact value of the number.
The column types @code{FLOAT}, @code{DOUBLE}, and @code{DECIMAL} are such.
@code{DECIMAL} columns store values with exact precision because they are
-represented as strings, but calculations on @code{DECIMAL} values may be done
-using floating-point operations.
+represented as strings, but calculations on @code{DECIMAL} values is before
+MYSQL 5.0.30 done using floating-point operations.
-The following example demonstrate the problem. It shows that even for the
-@code{DECIMAL} column type, calculations that are done using floating-point
-operations are subject to floating-point error.
+The following example (for older MySQL version than 5.0.3) demonstrate
+the problem. It shows that even for the @code{DECIMAL} column type,
+calculations that are done using floating-point operations are subject
+to floating-point error. (In all MYSQL versions, you would have similar
+problems if you would replace the @code{DECIMAL} columns with @code{FLOAT}).
@example
mysql> CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
@@ -96220,6 +96263,8 @@
the numbers shows up around the tenth decimal or so, depending on computer
architecture.
+In MySQL 5.0.3 you will only get the last row in the above result.
+
The problem cannot be solved by using @code{ROUND()} or similar functions,
because the result is still a floating-point number:
@@ -97883,6 +97928,9 @@
Bugs fixed:
@itemize @bullet
+@item
+@code{STR_TO_DATE()} now produces warnings in normal mode and errors in
+@code{strict mode}.
@item
Fixed a problem with @code{ORDER BY} that sometimes caused incorrect sorting
of @code{utf8} data. (Bug #9309)
| Thread |
|---|
| • bk commit - mysqldoc tree (monty:1.2780) | monty | 30 Mar |
