Numeric Types
MySQL provides numeric types for integer, floating-point, and fixed-point values. These have different ranges, so choose numeric types according to the range of values you need to represent. There is also a BIT type for representing bit-field values.
For integer types, a column must be indexed if the AUTO_INCREMENT attribute is specified. Inserting NULL into an AUTO_INCREMENT column causes the next sequence value to be inserted into the column. Typically, this is a value that is one greater than the column's current maximum value. More information on the precise behavior of AUTO_INCREMENT columns is given in Chapter 3.
The ZEROFILL and UNSIGNED attributes can be given for numeric types other than BIT:
Values are padded with leading zeros to the column's display width if the ZEROFILL attribute is specified. If the UNSIGNED attribute is specified, negative values are disallowed. (SIGNED is also an allowable attribute, but has no effect because numeric types are signed by default.)
SERIAL DEFAULT VALUE as an attribute for integer data types is shorthand for NOT NULL AUTO_INCREMENT UNIQUE.
In some cases, specifying one attribute causes another to be enabled as well. Specifying ZEROFILL for a numeric type automatically causes the column to be UNSIGNED. Specifying AUTO_INCREMENT automatically causes the column to be NOT NULL.
Note that the DESCRIBE and SHOW COLUMNS statements report the default value for an AUTO_INCREMENT column as NULL, although you cannot store a literal NULL into such a column. This indicates that you produce the default column value (the next sequence number) by setting the column to NULL when you create a new record.
Integer Types
TINYINT[(M)] Meaning.
A very small integer. M is the maximum display width, from 1 to 255. If omitted, M defaults to 4 (or 3 if the column is UNSIGNED). Allowable attributes.
AUTO_INCREMENT, SERIAL DEFAULT VALUE, UNSIGNED, ZEROFILL Range.
128 to 127 (27 to 271), or 0 to 255 (0 to 281) if UNSIGNED Default value.
NULL if the column can be NULL, 0 if NOT NULL Storage required.
1 byte Synonyms.
INT1[(M)]. BOOL and BOOLEAN are synonyms for TINYINT(1). BIT also is a synonym for TINYINT(1) before MySQL 5.0.3; as of 5.0.3, BIT is a separate data type. SMALLINT[(M)] Meaning.
A small integer. M is the maximum display width, from 1 to 255. If omitted, M defaults to 6 (or 5 if the column is UNSIGNED). Allowable attributes.
AUTO_INCREMENT, SERIAL DEFAULT VALUE, UNSIGNED, ZEROFILL Range.
32768 to 32767 (215 to 2151), or 0 to 65535 (0 to 2161) if UNSIGNED Default value.
NULL if the column can be NULL, 0 if NOT NULL Storage required.
2 bytes Synonyms.
INT2[(M)] MEDIUMINT[(M)] Meaning.
A medium-sized integer. M is the maximum display width, from 1 to 255. If omitted, M defaults to 9 (or 8 if the column is UNSIGNED). Allowable attributes.
AUTO_INCREMENT, SERIAL DEFAULT VALUE, UNSIGNED, ZEROFILL Range.
8388608 to 8388607 (223 to 2231), or 0 to 16777215 (0 to 2241) if UNSIGNED Default value.
NULL if the column can be NULL, 0 if NOT NULL Storage required.
3 bytes Synonyms.
INT3[(M)] and MIDDLEINT[(M)] INT[(M)] Meaning.
A normal-sized integer. M is the maximum display width, from 1 to 255. If omitted, M defaults to 11 (or 10 if the column is UNSIGNED). Allowable attributes.
AUTO_INCREMENT, SERIAL DEFAULT VALUE, UNSIGNED, ZEROFILL Range.
2147483648 to 2147483647 (231 to 2311), or 0 to 4294967295 (0 to 2321) if UNSIGNED Default value.
NULL if the column can be NULL, 0 if NOT NULL Storage required.
4 bytes Synonyms.
INTEGER[(M)] and INT4[(M)] BIGINT[(M)] Meaning.
A large integer. M is the maximum display width, from 1 to 255. If omitted, M defaults to 20. Allowable attributes.
AUTO_INCREMENT, SERIAL DEFAULT VALUE, UNSIGNED, ZEROFILL Range.
9223372036854775808 to 9223372036854775807 (263 to 2631), or 0 to 18446744073709551615 (0 to 2641) if UNSIGNED Default value.
NULL if the column can be NULL, 0 if NOT NULL Storage required.
8 bytes Synonyms.
INT8[(M)] Note.
SERIAL as a data type name is shorthand for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
Floating-Point Types
FLOAT(p) Meaning.
A floating-point number. In standard SQL, the precision p represents the minimum required bits of precision, but in MySQL it is used only to determine whether the data type is single-precision or double-precision: For values of p from 0 to 24, the type is single-precision, equivalent to FLOAT with no M or D specifiers. For values of p from 25 to 53, the type is double-precision, equivalent to DOUBLE with no M or D specifiers.
Values outside the range from 0 to 53 are illegal. Allowable attributes.
UNSIGNED, ZEROFILL Range.
See the FLOAT and DOUBLE type descriptions later in this section. Default value.
NULL if the column can be NULL, 0 if NOT NULL Storage required.
4 bytes for single-precision, 8 bytes for double-precision. FLOAT[(M,D)] Meaning.
A small floating-point number; single-precision (less precise than DOUBLE). M is the number of significant digits that values can have, from 1 to 255. D is the number of decimal places, from 0 to 30. If D is 0, column values have no decimal point or fractional part. If M and D are omitted, the display size and number of decimals are undefined; values are stored to the full precision allowed by your hardware. Allowable attributes.
UNSIGNED, ZEROFILL Range.
Minimum non-zero values are ±1.175494351E38; maximum non-zero values are ±3.402823466E+38. Negative values are disallowed if the column is UNSIGNED. Default value.
NULL if the column can be NULL, 0 if NOT NULL Storage required.
4 bytes Synonyms.
FLOAT4 is a synonym for FLOAT with no M or D specifiers. If the REAL_AS_FLOAT SQL mode is enabled, REAL[(M,D)] is a synonym for FLOAT[(M,D)]. DOUBLE[(M,D)] Meaning.
A large floating-point number; double-precision (more precise than FLOAT). M is the number of significant digits that values can have, from 1 to 255. D is the number of decimal places, from 0 to 30. If D is 0, column values have no decimal point or fractional part. If M and D are omitted, the display size and number of decimals are undefined; values are stored to the full precision allowed by your hardware. Allowable attributes.
UNSIGNED, ZEROFILL Range.
Minimum non-zero values are ±2.2250738585072014E308, maximum non-zero values are ±1.7976931348623157E+308. Negative values are disallowed if the column is UNSIGNED. Default value.
NULL if the column can be NULL, 0 if NOT NULL Storage required.
8 bytes Synonyms.
DOUBLE PRECISION[(M,D)] is a synonym for DOUBLE[(M,D)], as is REAL[(M,D)] if the REAL_AS_FLOAT SQL mode is not enabled. FLOAT8 is a synonym for DOUBLE with no M or D specifiers.
Fixed-Point Type
DECIMAL[(M,[D])] Meaning.
A fixed-point number, stored as a string (1 byte per digit, decimal point, or '' sign). M is the number of significant digits that values can have, from 1 to 255. D is the number of decimal places, from 0 to 30. If D is 0, column values have no decimal point or fractional part. If omitted, M and D default to 10 and 0, respectively. Allowable attributes.
UNSIGNED, ZEROFILL Range.
The maximum range is the same as for DOUBLE; the effective range for a given DECIMAL column is determined by M and D. Default value.
NULL if the column can be NULL, 0 if NOT NULL Storage required.
Normally M+2 bytes, where the extra two bytes are for the sign and decimal point characters. If the column is UNSIGNED, no sign character need be stored, which reduces the storage required by one byte. If D is 0, no decimal point need be stored, which also reduces the storage required by one byte. Synonyms.
NUMERIC[(M,[D])], DEC[(M,[D])], and FIXED[(M,[D])] Note.
In conformance with standard SQL, the value of M does not include the bytes needed for the sign or decimal point characters.
BIT Type
BIT[(M])] Meaning.
A bit-field value. M should be an integer from 1 to 64 indicating the number of bits per value. If omitted, M defaults to 1. Allowable attributes.
None, other than the global attributes Default value.
NULL if the column can be NULL, 0 if NOT NULL Storage required.
Approximately (M+7)/8 bytes. Note.
The BIT type was introduced as a separate data type in MySQL 5.0.3. Prior to 5.0.3, BIT is a synonym for TINYINT(1).
 |
