Scribd Logo
Upload

Welcome to Scribd!

  • 364 views

    MySQL Data Types Quick Reference Table

    Uploaded by

    Bishnu Das
    This document provides a quick reference table summarizing MySQL data types in version 5.0. It lists various numeric, string, date and time data types along with their storage requirements, ranges of values, and default attributes. Key data types include TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, CHAR, VARCHAR, TEXT, BLOB, DATE, DATETIME and TIMESTAMP.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd

    MySQL Data Types Quick Reference Table

    Uploaded by

    Bishnu Das
    364 views3 pages
    This document provides a quick reference table summarizing MySQL data types in version 5.0. It lists various numeric, string, date and time data types along with their storage requirements, ranges of values, and default attributes. Key data types include TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, CHAR, VARCHAR, TEXT, BLOB, DATE, DATETIME and TIMESTAMP.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides a quick reference table summarizing MySQL data types in version 5.0. It lists various numeric, string, date and time data types along with their storage requirements, ranges of values, and default attributes. Key data types include TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, CHAR, VARCHAR, TEXT, BLOB, DATE, DATETIME and TIMESTAMP.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    364 views3 pages

    MySQL Data Types Quick Reference Table

    Uploaded by

    Bishnu Das
    This document provides a quick reference table summarizing MySQL data types in version 5.0. It lists various numeric, string, date and time data types along with their storage requirements, ranges of values, and default attributes. Key data types include TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, CHAR, VARCHAR, TEXT, BLOB, DATE, DATETIME and TIMESTAMP.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 3

    MySQL Data Types Quick Reference Table: MySQL Version 5.

    0
    Type {storage} Name Range Attributes Default

    Numeric {1 byte}

    TINYINT[(M)]

    -128 TO 127 [0 to 255 if UNSIGNED]

    AUTO_INCREMENT UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE

    NULL [0 if NOT NULL]

    Numeric {2 bytes}

    SMALLINT[(M)]

    -32,768 to 32,767 [0 to 65,535]

    AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE

    NULL [0 if NOT NULL]

    Numeric {3 bytes}

    MEDIUMINT[(M)]

    -8,388,608 to 8,388,607 [0 to 16,777,215]

    AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE

    NULL [0 if NOT NULL]

    Numeric {4 bytes}

    INT[(M)]

    -/+2.147E+9 [0 to 4.294E+9]

    AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE

    NULL [0 if NOT NULL]

    Numeric {8 bytes}

    BIGINT[(M)]

    -/+9.223E+18 [0 to 18.45E+18]

    AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE

    NULL [0 if NOT NULL]

    Numeric {4 or 8}

    FLOAT(p)

    p=0-24 --> "FLOAT" p=25-53 --> "DOUBLE"

    UNSIGNED, ZEROFILL

    NULL [0 if NOT NULL]

    Numeric {4 bytes}

    FLOAT[(M,D)]

    Min=+/-1.175E-38 Max=+/-3.403E+38

    UNSIGNED, ZEROFILL

    NULL [0 if NOT NULL]

    Numeric {8 bytes}

    DOUBLE[(M,D)]

    Min=+/-2.225E-308 Max=+/-1.798E+308

    UNSIGNED, ZEROFILL

    NULL [0 if NOT NULL]

    Numeric {M+2}

    DECIMAL[(M,[D])] Stored as string

    Max Range = DOUBLE range Fixed point vs. DOUBLE float

    UNSIGNED, ZEROFILL

    NULL [0 if NOT NULL]

    Bit {8 bytes}

    BIT[(M)]

    Binary. Display by [add zero or converting with BIN()]. M=1-64

    Prior to 5.03 TINYINT(1) Synonym

    NULL [0 if NOT NULL]

    String {M char's}

    CHAR[(M)]

    M=0-255 Characters, FIXED. Right padded with spaces.

    BINARY, CHARACTER SET

    NULL ["" if NOT NULL]

    String {M char's1}

    VARCHAR(M)

    M=0-65,535 Characters M=0-255 <v5.0.3

    BINARY, CHARACTER SET

    NULL ["" if NOT NULL]

    String {#char's1}

    TINYTEXT2

    0-255 Characters

    BINARY, CHARACTER SET

    NULL ["" if NOT NULL]

    String {#char's1}

    TEXT2

    0-65,535 Char's

    BINARY, CHARACTER SET

    NULL ["" if NOT NULL]

    String {#char's1}

    MEDIUMTEXT2

    0-16,777,215 Char's

    BINARY, CHARACTER SET

    NULL ["" if NOT NULL]

    String {#char's1}

    LONGTEXT2

    0-4,294,967,295 Char's

    BINARY, CHARACTER SET

    NULL ["" if NOT NULL]

    String {M bytes}

    BINARY[(M)]

    M=0-255 bytes, FIXED.

    Global Only (case sensitive)

    NULL ["" if NOT NULL]

    String {M bytes}

    VARBINARY(M)

    0-65,535 bytes M=0-255 <v5.0.3

    Global Only (case sensitive)

    NULL ["" if NOT NULL]

    String {#bytes1}

    TINYBLOB

    0-255 bytes

    Global Only (case sensitive)

    NULL ["" if NOT NULL]

    String {#bytes1}

    BLOB

    0-65,535 bytes

    Global Only (case sensitive)

    NULL ["" if NOT NULL]

    String {#bytes1}

    MEDIUMBLOB

    0-16,777,215 bytes

    Global Only (case sensitive)

    NULL ["" if NOT NULL]

    String {#bytes1}

    LONGBLOB

    0-4,294,967,295 bytes

    Global Only (case sensitive)

    NULL ["" if NOT NULL]

    String {1-2 bytes}

    ENUM2 ("A1","A2",...) SET2 ("A1","A2",...)

    Column is exactly 1 of 1-65,535 values

    CHARACTER SET

    NULL [1st value if NOT NULL]

    String {1-8 bytes}

    Column is 0 or more values in list of 1-64 members

    CHARACTER SET

    NULL ["" if NOT NULL]

    Date & Time {3 bytes}

    DATE

    "1000-01-01" - "9999-12-31"

    Global Only (YYYY-MM-DD)

    NULL ["0000-00-00" if NOT NULL]

    Date & Time {8 bytes}

    DATETIME

    "1000-01-01 00:00:00" "9999-12-31 23:59:59"

    Global Only (YYYY-MM-DD hh:mm:ss)

    NULL ["0000-0000 00:00:00" if NOT NULL]

    Date & Time

    TIME

    "-838:59:59" - "838:59:59"

    Global Only (hh:mm:ss)

    NULL ["00:00:00" if

    {3 bytes}

    NOT NULL]

    Date & Time {4 bytes}

    TIMESTAMP

    19700101000000 2037+

    Global Only (YYYYMMDDhhmmss)

    Current Date & Time

    Date & Time {1 bytes}

    YEAR

    1900 - 2155

    Global Only (YYYY)

    NULL ["0000" if NOT NULL]

    Notes:
    1 2

    Storage will be # of characters or bytes, plus byte(s) to record length. These String data types are NOT case sensitive, unless given the "binary"

    attribute or have a case-sensitive CHARACTER SET collation. "E" is an abbreviation for "exponent". E18 means move the decimal over 18 places (search "scientific notation"). SERIAL DEFAULT VALUE attribute is an alias for "AUTO_INCREMENT NOT NULL UNIQUE". SERIAL data type is a synonym for "BIGINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE". BOOL and BOOLEAN data types are synonyms for TINYINT(1). REAL[(M,D)] and DOUBLE PRECISION[(M,D)] datatypes are synonyms for DOUBLE[(M,D)]. REAL_AS_FLOAT system variable can make REAL[(M,D)] a synonym for FLOAT[(M,D)]. "UNSIGNED ZEROFILL" attributes: ZEROFILL means if you specify an M value for an integer, it will be padded with zeros to fill up the M spaces. Ex: M=6, integer=247, display="000247". UNSIGNED means no negative values and often expands your range. Corresponding non-binary and binary string types: o CHAR vs. BINARY o VARCHAR vs. VARBINARY o TEXT vs. BLOB

    You might also like