11.1 Numeric Data Types

MySQL supports all standard SQL numeric data types. These types include the exact numeric data types (note 'INTEGER': integer-types, note 'SMALLINT': integer-types, note 'DECIMAL': fixed-point-types, and note 'NUMERIC': fixed-point-types.), as well as the approximate numeric data types (note 'FLOAT': floating-point-types, note 'REAL': floating-point-types, and note 'DOUBLE PRECISION': floating-point-types.). The keyword note 'INT': integer-types. is a synonym for note 'INTEGER': integer-types, and the keywords note 'DEC': fixed-point-types. and note 'FIXED': fixed-point-types. are synonyms for note 'DECIMAL': fixed-point-types. MySQL treats note 'DOUBLE': floating-point-types. as a synonym for note 'DOUBLE PRECISION': floating-point-types. (a nonstandard extension). MySQL also treats note 'REAL': floating-point-types. as a synonym for note 'DOUBLE PRECISION': floating-point-types. (a nonstandard variation), unless the 'REAL_AS_FLOAT' SQL mode is enabled.

The note 'BIT': bit-type. data type stores bit values and is supported for note 'MyISAM': myisam-storage-engine, note 'MEMORY': memory-storage-engine, note 'InnoDB': innodb-storage-engine, and *note 'NDB': mysql-cluster. tables.

For information about how MySQL handles assignment of out-of-range values to columns and overflow during expression evaluation, see *note out-of-range-and-overflow::.

For information about storage requirements of the numeric data types, see *note storage-requirements::.

For descriptions of functions that operate on numeric values, see note numeric-functions::. The data type used for the result of a calculation on numeric operands depends on the types of the operands and the operations performed on them. For more information, see note arithmetic-functions::.

 File: manual.info.tmp, Node: numeric-type-syntax, Next: integer-types, Prev: numeric-types, Up: numeric-types

11.1.1 Numeric Data Type Syntax

For integer data types, M indicates the minimum display width. The maximum display width is 255. Display width is unrelated to the range of values a type can store, as described in *note numeric-type-attributes::.

For floating-point and fixed-point data types, M is the total number of digits that can be stored.

If you specify 'ZEROFILL' for a numeric column, MySQL automatically adds the 'UNSIGNED' attribute to the column.

Numeric data types that permit the 'UNSIGNED' attribute also permit 'SIGNED'. However, these data types are signed by default, so the 'SIGNED' attribute has no effect.

'SERIAL' is an alias for 'BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE'.

'SERIAL DEFAULT VALUE' in the definition of an integer column is an alias for 'NOT NULL AUTO_INCREMENT UNIQUE'.

Warning:

When you use subtraction between integer values where one is of type 'UNSIGNED', the result is unsigned unless the 'NO_UNSIGNED_SUBTRACTION' SQL mode is enabled. See *note cast-functions::.

 File: manual.info.tmp, Node: integer-types, Next: fixed-point-types, Prev: numeric-type-syntax, Up: numeric-types

11.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

MySQL supports the SQL standard integer types 'INTEGER' (or 'INT') and 'SMALLINT'. As an extension to the standard, MySQL also supports the integer types 'TINYINT', 'MEDIUMINT', and 'BIGINT'. The following table shows the required storage and range for each integer type.

Required Storage and Range for Integer Types Supported by MySQL

Type Storage Minimum Minimum Maximum Maximum (Bytes) Value Value Value Value Signed Unsigned Signed Unsigned

'TINYINT'

1 '-128' '0' '127' '255'

'SMALLINT'

2 '-32768' '0' '32767' '65535'

'MEDIUMINT'

3 '-8388608' '0' '8388607' '16777215'

'INT'

4 '-2147483648''0' '2147483647' '4294967295'

'BIGINT'

8 '-2^63' '0' '2^63-1' '2^64-1'

 File: manual.info.tmp, Node: fixed-point-types, Next: floating-point-types, Prev: integer-types, Up: numeric-types

11.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC

The 'DECIMAL' and 'NUMERIC' types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, 'NUMERIC' is implemented as 'DECIMAL', so the following remarks about 'DECIMAL' apply equally to 'NUMERIC'.

MySQL stores 'DECIMAL' values in binary format. See *note precision-math::.

In a 'DECIMAL' column declaration, the precision and scale can be (and usually is) specified. For example:

 salary DECIMAL(5,2)

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.

Standard SQL requires that 'DECIMAL(5,2)' be able to store any value with five digits and two decimals, so values that can be stored in the 'salary' column range from '-999.99' to '999.99'.

In standard SQL, the syntax 'DECIMAL(M)' is equivalent to 'DECIMAL(M,0)'. Similarly, the syntax 'DECIMAL' is equivalent to 'DECIMAL(M,0)', where the implementation is permitted to decide the value of M. MySQL supports both of these variant forms of 'DECIMAL' syntax. The default value of M is 10.

If the scale is 0, 'DECIMAL' values contain no decimal point or fractional part.

The maximum number of digits for 'DECIMAL' is 65, but the actual range for a given 'DECIMAL' 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 permitted 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 permissible number of digits.)

 File: manual.info.tmp, Node: floating-point-types, Next: bit-type, Prev: fixed-point-types, Up: numeric-types

11.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

The 'FLOAT' and 'DOUBLE' types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

For 'FLOAT', the SQL standard permits an optional specification of the precision (but not the range of the exponent) in bits following the keyword 'FLOAT' in parentheses, that is, note 'FLOAT(P)': floating-point-types. MySQL also supports this optional precision specification, but the precision value in note 'FLOAT(P)': floating-point-types. is used only to determine storage size. A precision from 0 to 23 results in a 4-byte single-precision 'FLOAT' column. A precision from 24 to 53 results in an 8-byte double-precision 'DOUBLE' column.

MySQL permits a nonstandard syntax: 'FLOAT(M,D)' or 'REAL(M,D)' or 'DOUBLE PRECISION(M,D)'. Here, '(M,D)' 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)' looks 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 '999.0001'.

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see *note problems-with-float::.

For maximum portability, code requiring storage of approximate numeric data values should use 'FLOAT' or 'DOUBLE PRECISION' with no specification of precision or number of digits.

 File: manual.info.tmp, Node: bit-type, Next: numeric-type-attributes, Prev: floating-point-types, Up: numeric-types

11.1.5 Bit-Value Type - BIT

The 'BIT' data type is used to store bit values. A type of 'BIT(M)' enables storage of M-bit values. M can range from 1 to 64.

To specify bit values, 'b'VALUE'' notation can be used. VALUE is a binary value written using zeros and ones. For example, 'b'111'' and 'b'10000000'' represent 7 and 128, respectively. See *note bit-value-literals::.

If you assign a value to a 'BIT(M)' 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 'b'000101''.

NDB Cluster

The maximum combined size of all 'BIT' columns used in a given *note 'NDB': mysql-cluster. table must not exceed 4096 bits.

 File: manual.info.tmp, Node: numeric-type-attributes, Next: out-of-range-and-overflow, Prev: bit-type, Up: numeric-types

11.1.6 Numeric Type Attributes

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, note 'INT(4)': integer-types. specifies an note 'INT': integer-types. with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used is up to the application.)

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as note 'SMALLINT(3)': integer-types. has the usual note 'SMALLINT': integer-types. range of '-32768' to '32767', and values outside the range permitted by three digits are displayed in full using more than three digits.

When used in conjunction with the optional (nonstandard) 'ZEROFILL' attribute, the default padding of spaces is replaced with zeros. For example, for a column declared as *note 'INT(4) ZEROFILL': integer-types, a value of '5' is retrieved as '0005'.

Note:

The 'ZEROFILL' attribute is ignored for columns involved in expressions or *note 'UNION': union. queries.

If you store values larger than the display width in an integer column that has the 'ZEROFILL' attribute, you may experience problems when MySQL generates temporary tables for some complicated joins. In these cases, MySQL assumes that the data values fit within the column display width.

All integer types can have an optional (nonstandard) 'UNSIGNED' attribute. An unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an *note 'INT': integer-types. column is 'UNSIGNED', the size of the column's range is the same but its endpoints shift up, from '-2147483648' and '2147483647' to '0' and '4294967295'.

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. 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.

Integer or floating-point data types can have the 'AUTO_INCREMENT' attribute. When you insert a value of 'NULL' into an indexed 'AUTO_INCREMENT' column, the column is set to the next sequence value. Typically this is 'VALUE+1', where VALUE is the largest value for the column currently in the table. ('AUTO_INCREMENT' sequences begin with '1'.)

Storing '0' into an 'AUTO_INCREMENT' column has the same effect as storing 'NULL', unless the 'NO_AUTO_VALUE_ON_ZERO' SQL mode is enabled.

Inserting 'NULL' to generate 'AUTO_INCREMENT' values requires that the column be declared 'NOT NULL'. If the column is declared 'NULL', inserting 'NULL' stores a 'NULL'. When you insert any other value into an 'AUTO_INCREMENT' column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the inserted value.

Negative values for 'AUTO_INCREMENT' columns are not supported.

 File: manual.info.tmp, Node: out-of-range-and-overflow, Prev: numeric-type-attributes, Up: numeric-types

11.1.7 Out-of-Range and Overflow Handling

When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time:

Suppose that a table 't1' has this definition:

 CREATE TABLE t1 (i1 TINYINT, i2 TINYINT UNSIGNED);

With strict SQL mode enabled, an out of range error occurs:

 mysql> SET sql_mode = 'TRADITIONAL';
 mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
 ERROR 1264 (22003): Out of range value for column 'i1' at row 1
 mysql> SELECT * FROM t1;
 Empty set (0.00 sec)

With strict SQL mode not enabled, clipping with warnings occurs:

 mysql> SET sql_mode = '';
 mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
 mysql> SHOW WARNINGS;
 +---------+------+---------------------------------------------+
 | Level   | Code | Message                                     |
 +---------+------+---------------------------------------------+
 | Warning | 1264 | Out of range value for column 'i1' at row 1 |
 | Warning | 1264 | Out of range value for column 'i2' at row 1 |
 +---------+------+---------------------------------------------+
 mysql> SELECT * FROM t1;
 +------+------+
 | i1   | i2   |
 +------+------+
 |  127 |  255 |
 +------+------+

When strict SQL mode is not enabled, column-assignment conversions that occur due to clipping are reported as warnings for note 'ALTER TABLE': alter-table, note 'LOAD DATA': load-data, note 'UPDATE': update, and multiple-row note 'INSERT': insert. statements. In strict mode, these statements fail, and some or all the values are not inserted or changed, depending on whether the table is a transactional table and other factors. For details, see *note sql-mode::.

Overflow during numeric expression evaluation results in an error. For example, the largest signed *note 'BIGINT': integer-types. value is 9223372036854775807, so the following expression produces an error:

 mysql> SELECT 9223372036854775807 + 1;
 ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

To enable the operation to succeed in this case, convert the value to unsigned;

 mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
 +-------------------------------------------+
 | CAST(9223372036854775807 AS UNSIGNED) + 1 |
 +-------------------------------------------+
 |                       9223372036854775808 |
 +-------------------------------------------+

Whether overflow occurs depends on the range of the operands, so another way to handle the preceding expression is to use exact-value arithmetic because *note 'DECIMAL': fixed-point-types. values have a larger range than integers:

 mysql> SELECT 9223372036854775807.0 + 1;
 +---------------------------+
 | 9223372036854775807.0 + 1 |
 +---------------------------+
 |     9223372036854775808.0 |
 +---------------------------+

Subtraction between integer values, where one is of type 'UNSIGNED', produces an unsigned result by default. If the result would otherwise have been negative, an error results:

 mysql> SET sql_mode = '';
 Query OK, 0 rows affected (0.00 sec)

 mysql> SELECT CAST(0 AS UNSIGNED) - 1;
 ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

If the 'NO_UNSIGNED_SUBTRACTION' SQL mode is enabled, the result is negative:

 mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
 mysql> SELECT CAST(0 AS UNSIGNED) - 1;
 +-------------------------+
 | CAST(0 AS UNSIGNED) - 1 |
 +-------------------------+
 |                      -1 |
 +-------------------------+

If the result of such an operation is used to update an 'UNSIGNED' integer column, the result is clipped to the maximum value for the column type, or clipped to 0 if 'NO_UNSIGNED_SUBTRACTION' is enabled. If strict SQL mode is enabled, an error occurs and the column remains unchanged.

 File: manual.info.tmp, Node: date-and-time-types, Next: string-types, Prev: numeric-types, Up: data-types