Appendix D. System, Status, and User Variable Reference


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



MySQL The definitive guide to using, programming, and administering MySQL 4. 1 and 5. 0
Mysql: the Definitive Guide to Using, Programming, and Administering Mysql 4.1 and 5.0
ISBN: B003A1PKHY
EAN: N/A
Year: 2004
Pages: 190
Authors: Paul Dubois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net