| List: | Internals | « Previous MessageNext Message » | |
| From: | paul | Date: | October 13 2005 7:46pm |
| Subject: | svn commit - mysqldoc@docsrva: r58 - in trunk: refman refman-5.0 refman-5.1 | ||
| View as plain text | |||
Author: paul Date: 2005-10-13 19:46:25 +0200 (Thu, 13 Oct 2005) New Revision: 58 Log: DECIMAL range changed in 5.0.3. Modified: trunk/refman-5.0/column-types.xml trunk/refman-5.1/column-types.xml trunk/refman/column-types.xml Modified: trunk/refman/column-types.xml =================================================================== --- trunk/refman/column-types.xml 2005-10-13 15:29:16 UTC (rev 57) +++ trunk/refman/column-types.xml 2005-10-13 17:46:25 UTC (rev 58) @@ -2064,16 +2064,19 @@ </para> <para> - The maximum range of <literal>DECIMAL</literal> and - <literal>NUMERIC</literal> values is the same as for - <literal>DOUBLE</literal>, but the actual range for a given - <literal>DECIMAL</literal> or <literal>NUMERIC</literal> column - can be constrained by the precision or scale for a given column. - When such a column is assigned a value with more digits following - the decimal point than are allowed by the specified scale, the - value is converted to that scale. (The precise behavior is - operating system-specific, but generally the effect is truncation - to the allowable number of digits.) + The maximum number of digits for <literal>DECIMAL</literal> or + <literal>NUMERIC</literal> is 65 (64 from MySQL 5.0.3 to 5.0.5). + Before MySQL 5.0.3, the maximum range of + <literal>DECIMAL</literal> and <literal>NUMERIC</literal> values + is the same as for <literal>DOUBLE</literal>, but the actual range + for a given <literal>DECIMAL</literal> or + <literal>NUMERIC</literal> column can be constrained by the + precision or scale for a given column. When such a column is + assigned a value with more digits following the decimal point than + are allowed by the specified scale, the value is converted to that + scale. (The precise behavior is operating system-specific, but + generally the effect is truncation to the allowable number of + digits.) </para> <para> Modified: trunk/refman-5.0/column-types.xml =================================================================== --- trunk/refman-5.0/column-types.xml 2005-10-13 15:29:16 UTC (rev 57) +++ trunk/refman-5.0/column-types.xml 2005-10-13 17:46:25 UTC (rev 58) @@ -1253,8 +1253,8 @@ In MySQL 5.0.3 and later, if you attempt to set the length of a <literal>CHAR</literal> greater than 255, the <literal>CREATE TABLE</literal> or <literal>ALTER - TABLE</literal> statement in which this is done fails with - an error: + TABLE</literal> statement in which this is done fails with + an error: </para> <programlisting> @@ -2096,16 +2096,19 @@ </para> <para> - The maximum range of <literal>DECIMAL</literal> and - <literal>NUMERIC</literal> values is the same as for - <literal>DOUBLE</literal>, but the actual range for a given - <literal>DECIMAL</literal> or <literal>NUMERIC</literal> column - can be constrained by the precision or scale for a given column. - When such a column is assigned a value with more digits following - the decimal point than are allowed by the specified scale, the - value is converted to that scale. (The precise behavior is - operating system-specific, but generally the effect is truncation - to the allowable number of digits.) + The maximum number of digits for <literal>DECIMAL</literal> or + <literal>NUMERIC</literal> is 65 (64 from MySQL 5.0.3 to 5.0.5). + Before MySQL 5.0.3, the maximum range of + <literal>DECIMAL</literal> and <literal>NUMERIC</literal> values + is the same as for <literal>DOUBLE</literal>, but the actual range + for a given <literal>DECIMAL</literal> or + <literal>NUMERIC</literal> column can be constrained by the + precision or scale for a given column. When such a column is + assigned a value with more digits following the decimal point than + are allowed by the specified scale, the value is converted to that + scale. (The precise behavior is operating system-specific, but + generally the effect is truncation to the allowable number of + digits.) </para> <para> @@ -2142,21 +2145,22 @@ <para> When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL's behavior depends on the - SQL mode in effect at the time. If the mode is not set, MySQL clips the - value to the appropriate endpoint of the range and stores the - resulting value instead. However, if the mode is set to + SQL mode in effect at the time. If the mode is not set, MySQL + clips the value to the appropriate endpoint of the range and + stores the resulting value instead. However, if the mode is set to <literal>traditional</literal> (<quote>strict mode</quote>), a value that is out of range is rejected with an error, and the - insert fails, in accordance with the SQL standard. See <xref - linkend="server-sql-mode"/>. + insert fails, in accordance with the SQL standard. See + <xref + linkend="server-sql-mode"/>. </para> <remark role="todo"> [js] Add new example(s) once Bug#11546 is (really) fixed. - Grrrrrrrr...! + Grrrrrrrr...! </remark> - <!-- +<!-- <para> For example, the range of an <literal>INT</literal> column is <literal>-2147483648</literal> to <literal>2147483647</literal>. @@ -2169,6 +2173,7 @@ instead. If you set ... </para> --> + <para> If the <literal>INT</literal> column is <literal>UNSIGNED</literal>, the size of the column's range is the @@ -2188,12 +2193,14 @@ <para> Conversions that occur due to clipping when MySQL is not operating - in strict mode are reported as warnings for <literal>ALTER TABLE</literal>, <literal>LOAD DATA - INFILE</literal>, <literal>UPDATE</literal>, and multiple-row + in strict mode are reported as warnings for <literal>ALTER + TABLE</literal>, <literal>LOAD DATA INFILE</literal>, + <literal>UPDATE</literal>, and multiple-row <literal>INSERT</literal> statements. When MySQL is operating in strict mode, these statements fail, and some or all of the values will not be inserted or changed, depending on whether the table is - a transactional table and other factors. See <xref + a transactional table and other factors. See + <xref linkend="server-sql-mode"/> for details. </para> @@ -2222,22 +2229,22 @@ <literal>DATETIME</literal>, <literal>DATE</literal>, <literal>TIMESTAMP</literal>, <literal>TIME</literal>, and <literal>YEAR</literal>. Each temporal type has a range of legal - values, as well as a <quote>zero</quote> value that may be used when - you specify an illegal value that MySQL cannot represent. The + values, as well as a <quote>zero</quote> value that may be used + when you specify an illegal value that MySQL cannot represent. The <literal>TIMESTAMP</literal> type has special automatic updating behavior, described later on. </para> <para> - Starting from MySQL 5.0.2, MySQL gives warnings or errors if you try - to insert an illegal date. You can get MySQL to accept certain + Starting from MySQL 5.0.2, MySQL gives warnings or errors if you + try to insert an illegal date. You can get MySQL to accept certain dates, such as <literal>'1999-11-31'</literal>, by using the <literal>ALLOW_INVALID_DATES</literal> SQL mode. (Before 5.0.2, this mode was the default behavior for MySQL). This is useful when - you want to store a <quote>possibly wrong</quote> value which the user - has specified (for example, in a web form) in the database for - future processing. Under this mode, MySQL verifies only that the - month is in the range from 0 to 12 and that the day is in the + you want to store a <quote>possibly wrong</quote> value which the + user has specified (for example, in a web form) in the database + for future processing. Under this mode, MySQL verifies only that + the month is in the range from 0 to 12 and that the day is in the range from 0 to 31. These ranges are defined to include zero because MySQL allows you to store dates where the day or month and day are zero in a <literal>DATE</literal> or @@ -2716,18 +2723,18 @@ month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date containing parts not within these ranges is subject to conversion to - <literal>'0000-00-00'</literal>. Please note - that this still allows you to store invalid dates such as - <literal>'2002-04-31'</literal>. To ensure that a date is + <literal>'0000-00-00'</literal>. Please note that this still + allows you to store invalid dates such as + <literal>'2002-04-31'</literal>. To ensure that a date is valid when not using strict mode, you should perform a check - in your application. + in your application. </para> - <para> + <para> In strict mode, invalid dates are not accepted, and not converted. </para> - + <para> See <xref linkend="server-sql-mode"/> for more information. </para> @@ -3522,7 +3529,8 @@ maximum length, the value is truncated to fit. If the truncated characters are not spaces, a warning is generated. You can cause an error to occur rather than an warning by using strict SQL - mode (in which case the value is not inserted). See <xref linkend="server-sql-mode"/>. + mode (in which case the value is not inserted). See + <xref linkend="server-sql-mode"/>. </para> <para> @@ -3575,7 +3583,7 @@ <entry><literal>'ab'</literal></entry> <entry><literal>'ab '</literal></entry> <entry>4 bytes</entry> - <entry><literal>'ab '</literal></entry> + <entry><literal>'ab '</literal></entry> <entry>3 bytes</entry> </row> <row> @@ -3599,9 +3607,9 @@ <para> Note that the values shown as stored in the last row of the table apply <emphasis>only when not using strict - mode</emphasis>; if MySQL is running in strict mode, values + mode</emphasis>; if MySQL is running in strict mode, values which exceed the column length are <emphasis>not - stored</emphasis>, and an error results. + stored</emphasis>, and an error results. </para> <para> @@ -3609,7 +3617,7 @@ <literal>CHAR(4)</literal> and <literal>VARCHAR(4)</literal> columns are not always the same, because trailing spaces are removed from <literal>CHAR</literal> columns upon retrieval. - This difference is illustrated by the following example: + This difference is illustrated by the following example: </para> <programlisting> @@ -3627,7 +3635,7 @@ +----------------+----------------+ 1 row in set (0.00 sec) </programlisting> - + <para> In MySQL ¤t-series;, values in <literal>CHAR</literal> and <literal>VARCHAR</literal> columns are sorted and compared @@ -3792,11 +3800,12 @@ </para> <para> - In MySQL ¤t-series;, <literal>BLOB</literal> columns are treated as binary strings - (byte strings). <literal>TEXT</literal> columns are treated as - non-binary strings (character strings). <literal>BLOB</literal> - columns have no character set, and sorting and comparison is - based on the numeric values of the bytes in column values. + In MySQL ¤t-series;, <literal>BLOB</literal> columns are + treated as binary strings (byte strings). + <literal>TEXT</literal> columns are treated as non-binary + strings (character strings). <literal>BLOB</literal> columns + have no character set, and sorting and comparison is based on + the numeric values of the bytes in column values. <literal>TEXT</literal> columns have a character set, and values are sorted and compared based on the collation of the character set. @@ -3809,13 +3818,15 @@ </para> <para> - When not running in strict mode, if you assign a value to a <literal>BLOB</literal> or - <literal>TEXT</literal> column that exceeds the column type's - maximum length, the value is truncated to fit. If the truncated - characters are not spaces, a warning is generated. You can cause - an error to occur and the value to be rejected rather than to be - truncated with awarning by using strict SQL mode. See <xref - linkend="server-sql-mode"/>. + When not running in strict mode, if you assign a value to a + <literal>BLOB</literal> or <literal>TEXT</literal> column that + exceeds the column type's maximum length, the value is truncated + to fit. If the truncated characters are not spaces, a warning is + generated. You can cause an error to occur and the value to be + rejected rather than to be truncated with awarning by using + strict SQL mode. See + <xref + linkend="server-sql-mode"/>. </para> <para> @@ -4114,10 +4125,10 @@ When retrieved, values stored into an <literal>ENUM</literal> column are displayed using the lettercase that was used in the column definition. In MySQL ¤t-series;, - <literal>ENUM</literal> - columns can be assigned a character set and collation. For - binary or case-sensitive collations, lettercase is taken into - account when assigning values to the column. + <literal>ENUM</literal> columns can be assigned a character set + and collation. For binary or case-sensitive collations, + lettercase is taken into account when assigning values to the + column. </para> <para> @@ -4228,10 +4239,10 @@ When retrieved, values stored into a <literal>SET</literal> column are displayed using the lettercase that was used in the column definition. In MySQL ¤t-series;, - <literal>SET</literal> columns - can be assigned a character set and collation. For binary or - case-sensitive collations, lettercase is taken into account when - assigning values to to the column. + <literal>SET</literal> columns can be assigned a character set + and collation. For binary or case-sensitive collations, + lettercase is taken into account when assigning values to to the + column. </para> <para> @@ -4557,14 +4568,14 @@ </para> <para> - As of MySQL 5.0.3, values for <literal>DECIMAL</literal> - columns are represented using a binary format that packs nine - decimal (base 10) digits into four bytes. Storage for the integer - and fractional parts of each value are determined separately. Each + As of MySQL 5.0.3, values for <literal>DECIMAL</literal> columns + are represented using a binary format that packs nine decimal + (base 10) digits into four bytes. Storage for the integer and + fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the <quote>leftover</quote> digits require some fraction of four bytes. The storage required for excess digits is given by the - following table: + following table: </para> <informaltable> Modified: trunk/refman-5.1/column-types.xml =================================================================== --- trunk/refman-5.1/column-types.xml 2005-10-13 15:29:16 UTC (rev 57) +++ trunk/refman-5.1/column-types.xml 2005-10-13 17:46:25 UTC (rev 58) @@ -760,7 +760,7 @@ <replaceable>D</replaceable> is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the ‘<literal>-</literal>’ - sign are not counted in <replaceable>M</replaceable>. If + sign are not counted in <replaceable>M</replaceable>. If <replaceable>D</replaceable> is 0, values have no decimal point or fractional part. The maximum number of digits (<replaceable>M</replaceable>) for @@ -1196,10 +1196,10 @@ In MySQL ¤t-series;, if you attempt to set the length of a <literal>CHAR</literal> greater than 255, the <literal>CREATE TABLE</literal> or <literal>ALTER - TABLE</literal> statement in which this is done fails with - an error: + TABLE</literal> statement in which this is done fails with + an error: </para> - + <programlisting> mysql> <userinput>CREATE TABLE c1 (col1 INT, col2 CHAR(500));</userinput> ERROR 1074 (42000): Column length too big for column 'col' (max = 255); use BLOB or TEXT instead @@ -1323,14 +1323,14 @@ <literal>VARCHAR</literal> in MySQL ¤t-series; is determined by the maximum row size and the character set you use. The maximum <emphasis>effective</emphasis> length is - 65,532 bytes.) + 65,532 bytes.) </para> <para> <emphasis role="bold">Note</emphasis>: MySQL ¤t-series; follows the standard SQL specification, and does <emphasis>not</emphasis> remove trailing spaces from - VARCHAR values. + VARCHAR values. </para> <para> @@ -1990,7 +1990,7 @@ able to store any value with five digits and two decimals. In this case, therefore, the range of values that can be stored in the <literal>salary</literal> column is from - <literal>-999.99</literal> to <literal>999.99</literal>. + <literal>-999.99</literal> to <literal>999.99</literal>. </para> <para> @@ -2008,9 +2008,8 @@ </para> <para> - The maximum range of <literal>DECIMAL</literal> and - <literal>NUMERIC</literal> values is the same as for - <literal>DOUBLE</literal>, but the actual range for a given + The maximum number of digits for <literal>DECIMAL</literal> or + <literal>NUMERIC</literal> is 65, but the actual range for a given <literal>DECIMAL</literal> or <literal>NUMERIC</literal> column can be constrained by the precision or scale for a given column. When such a column is assigned a value with more digits following @@ -2024,7 +2023,7 @@ In MySQL ¤t-series;, the <literal>BIT</literal> data type can be used to store bit-field values. A type of <literal>BIT(<replaceable>M</replaceable>)</literal> allows for - storage of <replaceable>M</replaceable>-bit values. + storage of <replaceable>M</replaceable>-bit values. <replaceable>M</replaceable> can range from 1 to 64. </para> @@ -2054,19 +2053,21 @@ <para> When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL's behavior depends on the - SQL mode in effect at the time. If the mode is not set, MySQL clips the - value to the appropriate endpoint of the range and stores the - resulting value instead. However, if the mode is set to + SQL mode in effect at the time. If the mode is not set, MySQL + clips the value to the appropriate endpoint of the range and + stores the resulting value instead. However, if the mode is set to <literal>traditional</literal> (<quote>strict mode</quote>), a value that is out of range is rejected with an error, and the - insert fails, in accordance with the SQL standard. See <xref - linkend="server-sql-mode"/>. + insert fails, in accordance with the SQL standard. See + <xref + linkend="server-sql-mode"/>. </para> - + <remark role="todo"> [js] Add new example(s) once Bug#11546 is (really) fixed. - Grrrrrrrr...! + Grrrrrrrr...! </remark> + <!-- <para> For example, the range of an <literal>INT</literal> column is @@ -2080,6 +2081,7 @@ instead. If you set ... </para> --> + <para> If the <literal>INT</literal> column is <literal>UNSIGNED</literal>, the size of the column's range is the @@ -2100,12 +2102,13 @@ <para> Conversions that occur due to clipping when MySQL is not operating in strict mode are reported as warnings for <literal>ALTER - TABLE</literal>, <literal>LOAD DATA INFILE</literal>, + TABLE</literal>, <literal>LOAD DATA INFILE</literal>, <literal>UPDATE</literal>, and multiple-row <literal>INSERT</literal> statements. When MySQL is operating in strict mode, these statements fail, and some or all of the values will not be inserted or changed, depending on whether the table is - a transactional table and other factors. See <xref + a transactional table and other factors. See + <xref linkend="server-sql-mode"/> for details. </para> @@ -2134,24 +2137,24 @@ <literal>DATETIME</literal>, <literal>DATE</literal>, <literal>TIMESTAMP</literal>, <literal>TIME</literal>, and <literal>YEAR</literal>. Each temporal type has a range of legal - values, as well as a <quote>zero</quote> value that may be used when - you specify an illegal value that MySQL cannot represent. The + values, as well as a <quote>zero</quote> value that may be used + when you specify an illegal value that MySQL cannot represent. The <literal>TIMESTAMP</literal> type has special automatic updating behavior, described later on. </para> <para> - In MySQL ¤t-series;, MySQL gives warnings or errors if you try - to insert an illegal date. You can get MySQL to accept certain + In MySQL ¤t-series;, MySQL gives warnings or errors if you + try to insert an illegal date. You can get MySQL to accept certain dates, such as <literal>'1999-11-31'</literal>, by using the <literal>ALLOW_INVALID_DATES</literal> SQL mode. This is useful - when you want to store a <quote>possibly wrong</quote> value which the - user has specified (for example, in a web form) in the database - for future processing. Under this mode, MySQL verifies only that - the month is in the range from 0 to 12 and that the day is in the - range from 0 to 31. These ranges are defined to include zero - because MySQL allows you to store dates where the day or month and - day are zero in a <literal>DATE</literal> or + when you want to store a <quote>possibly wrong</quote> value which + the user has specified (for example, in a web form) in the + database for future processing. Under this mode, MySQL verifies + only that the month is in the range from 0 to 12 and that the day + is in the range from 0 to 31. These ranges are defined to include + zero because MySQL allows you to store dates where the day or + month and day are zero in a <literal>DATE</literal> or <literal>DATETIME</literal> column. This is extremely useful for applications that need to store a birthdate for which you don't know the exact date. In this case, you simply store the date as @@ -2161,7 +2164,7 @@ <literal>DATE_SUB()</literal> or <literal>DATE_ADD</literal> that require complete dates. (If you do <emphasis>not</emphasis> want to allow zero in dates, you can use the - <literal>NO_ZERO_IN_DATE</literal> SQL mode). + <literal>NO_ZERO_IN_DATE</literal> SQL mode). </para> <para> @@ -2629,16 +2632,16 @@ ranges is subject to conversion to <literal>'0000-00-00'</literal>. Please note that this still allows you to store invalid dates such as - <literal>'2002-04-31'</literal>. To ensure that a date is + <literal>'2002-04-31'</literal>. To ensure that a date is valid when not using strict mode, you should perform a check - in your application. + in your application. </para> - + <para> In strict mode, invalid dates are not accepted, and not converted. </para> - + <para> See <xref linkend="server-sql-mode"/> for more information. </para> @@ -2734,7 +2737,7 @@ values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special value <literal>'0000-00-00 - 00:00:00'</literal>. + 00:00:00'</literal>. </para> <para> @@ -3430,8 +3433,9 @@ maximum length, the value is truncated to fit. If the truncated characters are not spaces, a warning is generated. You can cause an error to occur rather than an warning by using strict SQL - mode (in which case the value is not inserted). See <xref - linkend="server-sql-mode"/>. + mode (in which case the value is not inserted). See + <xref + linkend="server-sql-mode"/>. </para> <para> @@ -3472,7 +3476,7 @@ <entry><literal>'ab'</literal></entry> <entry><literal>'ab '</literal></entry> <entry>4 bytes</entry> - <entry><literal>'ab '</literal></entry> + <entry><literal>'ab '</literal></entry> <entry>3 bytes</entry> </row> <row> @@ -3492,13 +3496,13 @@ </tbody> </tgroup> </informaltable> - + <para> Note that the values shown as stored in the last row of the table apply <emphasis>only when not using strict - mode</emphasis>; if MySQL is running in strict mode, values + mode</emphasis>; if MySQL is running in strict mode, values which exceed the column length are <emphasis>not - stored</emphasis>, and an error results. + stored</emphasis>, and an error results. </para> <para> @@ -3506,7 +3510,7 @@ <literal>CHAR(4)</literal> and <literal>VARCHAR(4)</literal> columns are not always the same, because trailing spaces are removed from <literal>CHAR</literal> columns upon retrieval. - This difference is illustrated by the following example: + This difference is illustrated by the following example: </para> <programlisting> @@ -3524,7 +3528,7 @@ +----------------+----------------+ 1 row in set (0.00 sec) </programlisting> - + <para> In MySQL ¤t-series;, values in <literal>CHAR</literal> and <literal>VARCHAR</literal> columns are sorted and compared @@ -3706,13 +3710,15 @@ </para> <para> - When not running in strict mode, if you assign a value to a <literal>BLOB</literal> or - <literal>TEXT</literal> column that exceeds the column type's - maximum length, the value is truncated to fit. If the truncated - characters are not spaces, a warning is generated. You can cause - an error to occur and the value to be rejected rather than to be - truncated with awarning by using strict SQL mode. See <xref - linkend="server-sql-mode"/>. + When not running in strict mode, if you assign a value to a + <literal>BLOB</literal> or <literal>TEXT</literal> column that + exceeds the column type's maximum length, the value is truncated + to fit. If the truncated characters are not spaces, a warning is + generated. You can cause an error to occur and the value to be + rejected rather than to be truncated with awarning by using + strict SQL mode. See + <xref + linkend="server-sql-mode"/>. </para> <para> @@ -3733,8 +3739,8 @@ <literal>BLOB</literal> and <literal>TEXT</literal> columns when values are stored or retrieved. (In MySQL ¤t-series;, this is the same as with - <literal>VARBINARY</literal> and - <literal>VARCHAR</literal> columns.) + <literal>VARBINARY</literal> and <literal>VARCHAR</literal> + columns.) </para> <para> @@ -4461,7 +4467,7 @@ multiple of nine digits requires four bytes, and the <quote>leftover</quote> digits require some fraction of four bytes. The storage required for excess digits is given by the - following table: + following table: </para> <informaltable>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r58 - in trunk: refman refman-5.0 refman-5.1 | paul | 13 Oct |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.664 sec. using MySQL 5.1.14-beta-log