Source code editor What Is Ajax
MySQL supports all of the standard SQL numeric data types. These types include the exact numeric data types (
NUMERIC), as well as the approximate numeric data types (
DOUBLE PRECISION). The keyword
INT is a synonym for
INTEGER, and the keyword
DEC is a synonym for
DECIMAL. For numeric type storage requirements, see Section 11.5, “Data Type Storage Requirements”.
As of MySQL 5.0.3, a
BIT data type is available for storing bit-field values. (Before 5.0.3, MySQL interprets
TINYINT(1).) In MySQL 5.0.3,
BIT is supported only for
MyISAM. MySQL 5.0.5 extends
BIT support to
As an extension to the SQL standard, MySQL also supports the integer types
BIGINT. The following table shows the required storage and range for each of the integer types.
|Type||Bytes||Minimum Value||Maximum Value|
Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example,
INT(4)). This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces.
The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as
SMALLINT(3) has the usual
SMALLINT range of
32767, and values outside the range allowed by three characters are displayed using more than three characters.
When used in conjunction with the optional extension attribute
ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as
INT(5) ZEROFILL, a value of
4 is retrieved as
00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL assumes that the data fits into the original column width.
ZEROFILL attribute is stripped when a column is involved in expressions or
All integer types can have an optional (non-standard) attribute
UNSIGNED. Unsigned values can be used when you want to allow only non-negative numbers in a column and you need a larger upper numeric range for the column. For example, if an
INT column is
UNSIGNED, the size of the column's range is the same but its endpoints shift from
2147483647 up to
Floating-point and fixed-point types also can be
UNSIGNED. As with integer types, this attribute prevents negative values from being stored in the column. However, unlike the integer types, the upper range of column values remains the same.
If you specify
ZEROFILL for a numeric column, MySQL automatically adds the
UNSIGNED attribute to the column.
For floating-point data types, MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
DOUBLE data types are used to represent approximate numeric data values. For
FLOAT the SQL standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword
FLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a four-byte single-precision
FLOAT column. A precision from 24 to 53 results in an eight-byte double-precision
MySQL allows a non-standard syntax:
DOUBLE PRECISION(. Here, “
(” means than values can be stored with up to
M digits in total, of which
D digits may be after the decimal point. For example, a column defined as
FLOAT(7,4) will look like
-999.9999 when displayed. MySQL performs rounding when storing values, so if you insert
999.00009 into a
FLOAT(7,4) column, the approximate result is
DOUBLE as a synonym for
DOUBLE PRECISION (a non-standard extension). MySQL also treats
REAL as a synonym for
DOUBLE PRECISION (a non-standard variation), unless the
REAL_AS_FLOAT SQL mode is enabled.
For maximum portability, code requiring storage of approximate numeric data values should use
DOUBLE PRECISION with no specification of precision or number of digits.
NUMERIC data types are used to store exact numeric data values. In MySQL,
NUMERIC is implemented as
DECIMAL. These types are used to store values for which it is important to preserve exact precision, for example with monetary data.
As of MySQL 5.0.3,
NUMERIC values are stored in binary format. Previously, they were stored as strings, with one character used for each digit of the value, the decimal point (if the scale is greater than 0), and the ‘
-’ sign (for negative numbers). See Chapter 21, Precision Math.
When declaring a
NUMERIC column, the precision and scale can be (and usually is) specified; for example:
In this example,
5 is the precision and
2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point. If the scale is 0,
NUMERIC values contain no decimal point or fractional part.
Standard SQL requires that the
salary column be 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
salary column is from
999.99. MySQL enforces this limit as of MySQL 5.0.3. Before 5.0.3, on the positive end of the range, the column could actually store numbers up to
9999.99. (For positive numbers, MySQL 5.0.2 and earlier used the byte reserved for the sign to extend the upper end of the range.)
In standard SQL, the syntax
DECIMAL( is equivalent to
DECIMAL(. Similarly, the syntax
DECIMAL is equivalent to
DECIMAL(, where the implementation is allowed to decide the value of
M. MySQL supports both of these variant forms of the
NUMERIC syntax. The default value of
M is 10.
The maximum number of digits for
NUMERIC is 65 (64 from MySQL 5.0.3 to 5.0.5). Before MySQL 5.0.3, the maximum range of
NUMERIC values is the same as for
DOUBLE, but the actual range for a given
NUMERIC 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.)
As of MySQL 5.0.3, the
BIT data type is used to store bit-field values. A type of
BIT( allows for storage of
M can range from 1 to 64.
To specify bit values,
b' notation can be used.
value is a binary value written using zeros and ones. For example,
b'10000000' represent 7 and 128, respectively. See Section 9.1.5, “Bit-Field Values”.
If you assign a value to a
BIT( column that is less than
M bits long, the value is padded on the left with zeros. For example, assigning a value of
b'101' to a
BIT(6) column is, in effect, the same as assigning
When asked to store a value in a numeric column that is outside the data type's allowable range, MySQL's behavior depends on the SQL mode in effect at the time. For example, if no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. However, if the mode is set to
TRADITIONAL, MySQL rejects a value that is out of range with an error, and the insert fails, in accordance with the SQL standard.
In non-strict mode, when an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range. If you store 256 into a
TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively. When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range.
Conversions that occur due to clipping when MySQL is not operating in strict mode are reported as warnings for
LOAD DATA INFILE,
UPDATE, and multiple-row
INSERT 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. For details, see Section 5.2.6, “SQL Modes”.
Source code editor What Is Ajax