MySQL provides numeric types for integer and floating point values. Types can be chosen according to the range of values you need to represent.
Values for numeric types are padded with leading zeroes to the column's display width if the ZEROFILL attribute is specified.
For integer types, a column must be a PRIMARY KEY or a UNIQUE index if the AUTO_INCREMENT attribute is specified. Inserting NULL into an AUTO_INCREMENT column inserts the next sequence value for the column (typically a value that is one greater than the column's current maximum value). More information on the precise behavior of AUTO_INCREMENT columns
If the UNSIGNED attribute is specified for a numeric type, negative values are disallowed, except that UNSIGNED for FLOAT or DOUBLE columns is unsupported prior to MySQL 4.0.2. (UNSIGNED can be given in the definition for such a column prior to 4.0.2, but is ineffective in preventing negative values from being stored, and may result in negative values being displayed incorrectly if the column also is declared with the ZEROFILL attribute.)
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. As of MySQL 3.23, specifying AUTO_INCREMENT automatically causes the column to be NOT NULL.
Note that DESCRIBE and SHOW COLUMNS report the default value for an AUTO_INCREMENT column as NULL, although you cannot insert 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.
TINYINT[(M)]
Meaning: A very small integer
Allowable attributes: AUTO_INCREMENT, UNSIGNED, ZEROFILL
Range: –128 to 127 (–27to 27–1) or 0 to 255 (0 to 28–1) if UNSIGNED
Default value: NULL if column can be NULL, 0 if NOT NULL.
Storage required: 1 byte
Synonyms: INT1[(M)]. BIT and BOOL are synonyms for TINYINT(1)
SMALLINT[(M)]
Meaning: A small integer
Allowable attributes: AUTO_INCREMENT, UNSIGNED, ZEROFILL
Range: –32768 to 32767 (–215to 215–1) or 0 to 65535 (0 to 216–1) if UNSIGNED
Default value: NULL if column can be NULL, 0 if NOT NULL
Storage required: 2 bytes
Synonyms: INT2[(M)]
MEDIUMINT[(M)]
Meaning: A medium-sized integer
Allowable attributes: AUTO_INCREMENT, UNSIGNED, ZEROFILL
Range: –8388608 to 8388607 (–223to 223–1) or 0 to 16777215 (0 to 224–1) if UNSIGNED
Default value: NULL if 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
Allowable attributes: AUTO_INCREMENT, UNSIGNED, ZEROFILL
Range: –2147483648 to 2147483647 (–231to 231–1) or 0 to 4294967295 (0 to 232–1) if UNSIGNED
Default value: NULL if column can be NULL, 0 if NOT NULL
Storage required: 4 bytes
Synonyms: INTEGER[(M)] and INT4[(M)]
BIGINT[(M)]
Meaning: A large integer
Allowable attributes: AUTO_INCREMENT, UNSIGNED, ZEROFILL
Range: –9223372036854775808 to 9223372036854775807 (–263to 263–1) or 0 to 18446744073709551615 (0 to 264–1) if UNSIGNED
Default value: NULL if column can be NULL, 0 if NOT NULL
Storage required: 8 bytes
Synonyms: INT8[(M)]
FLOAT(p)
Meaning: A floating-point number, with the minimum required bits of precision given by p. For p values from 0 to 24, the type is treated as a single-precision column, equivalent to FLOAT with no M or D specifiers. For values from 25 to 53, the type is treated as a double-precision column, equivalent to DOUBLE with no M or D specifiers.
Allowable attributes: UNSIGNED (as of MySQL 4.0.2), ZEROFILL.
Range: See the FLOAT and DOUBLE type descriptions in the following sections.
Default value: NULL if column can be NULL, 0 if NOT NULL.
Storage required: 4 bytes for single precision, 8 bytes for double precision.
Note: Prior to MySQL 3.23.6, the allowable p values are 4 and 8. From MySQL 3.23.0 to 3.23.5, FLOAT(4) and FLOAT(8) are treated as single-precision and double-precision columns stored to full hardware precision. Prior to MySQL 3.23, FLOAT(4) and FLOAT(8) are equivalent to FLOAT(10,2) and DOUBLE(16,4) and have values rounded to 2 and 4 decimals, not stored to the hardware precision.
FLOAT[(M,D)]
Meaning: A small floating-point number; single-precision (less precise than DOUBLE). M is the display width, D is the minimum required number of decimals precision. 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.
Allowable attributes: UNSIGNED (as of MySQL 4.0.2), ZEROFILL.
Range: Minimum non-zero values are ±1.175494351E–38; maximum non-zero values are ±3.402823466E+38. Negative values are disallowed if the column is UNSIGNED.
Default value: NULL if column can be NULL, 0 if NOT NULL.
Storage required: 4 bytes.
Synonyms: Prior to MySQL 3.23.6, FLOAT and FLOAT4 are synonyms for LOAT(10,2).
Note: Prior to MySQL 3.23.6, FLOAT(M,D) column values are rounded to D decimals, not stored to the full precision allowed by your hardware.
DOUBLE[(M,D)]
Meaning: A large floating-point number; double-precision (more precise than FLOAT). M is the display width, D is the minimum required number of decimals precision. 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.
Allowable attributes: UNSIGNED (as of MySQL 4.0.2), ZEROFILL.
Range: Minimum non-zero values are ±2.2250738585072014E–308, maximum non-zero values are ±1.7976931348623157E+308. Negative values are disallowed if the column is UNSIGNED.
Default value: NULL if column can be NULL, 0 if NOT NULL.
Storage required: 8 bytes.
Synonyms: DOUBLE PRECISION[(M,D)] and REAL[(M,D)] are synonyms for DOUBLE[(M,D)]. Prior to MySQL 3.23.6, DOUBLE and FLOAT8 are synonyms for DOUBLE(16,4).
Note: Prior to MySQL 3.23.6, DOUBLE(M,D) column values are rounded to D decimals, not stored to the full precision allowed by your hardware.
DECIMAL[(M,[D])]
Meaning: A floating-point number, stored as a string (1 byte per digit, decimal point, or – sign). M is the display width, D is the minimum required number of decimals precision. If D is 0, column values have no decimal point or fractional part. M and D default to 10 and 0 if omitted. (Prior to MySQL 3.23.6, M and D are required, not optional.)
Allowable attributes: UNSIGNED (as of MySQL 4.0.2), ZEROFILL.
Range: Maximum range is the same as for DOUBLE; effective range for a given DECIMAL type is determined by M and D.
Default value: NULL if column can be NULL, 0 if NOT NULL.
Storage required: M+2 bytes normally (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. Prior to MySQL 3.23, DECIMAL requires M bytes, and the sign and decimal characters are stored within the M bytes allocated to each column value. This means that any given DECIMAL(M,D) specification has a larger range as of MySQL 3.23 than before, but also requires more storage.
Synonyms: NUMERIC[(M,[D])] and DEC[(M,[D])].
Note: In conformance with ANSI SQL, the value of M does not include the bytes needed for the sign character or decimal point as of MySQL 3.23.
No comments:
Post a Comment