featured computer 850

MySQL Data Types: Quick Reference Table

The MySQL data-types table in Open Document format (much better for printing).

Type {storage}NameRangeAttributesDefault
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, ZEROFILLNULL
[0 if NOT NULL]
Numeric
{4 bytes}
FLOAT[(M,D)]Min=+/-1.175E-38
Max=+/-3.403E+38
UNSIGNED, ZEROFILLNULL
[0 if NOT NULL]
Numeric
{8 bytes}
DOUBLE[(M,D)]Min=+/-2.225E-308
Max=+/-1.798E+308
UNSIGNED, ZEROFILLNULL
[0 if NOT NULL]
Numeric
{M+2}
DECIMAL[(M,[D])]
Stored as string
Max Range = DOUBLE range
Fixed point vs. DOUBLE float
UNSIGNED, ZEROFILLNULL
[0 if NOT NULL]
Bit
{8 bytes}
BIT[(M)]Binary. Display by [add zero or converting with BIN()]. M=1-64Prior 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 SETNULL
[“” if NOT NULL]
String
{M char’s1}
VARCHAR(M)M=0-65,535 Characters
M=0-255 <v5.0.3
BINARY, CHARACTER SETNULL
[“” if NOT NULL]
String
{#char’s1}
TINYTEXT20-255 CharactersBINARY, CHARACTER SETNULL
[“” if NOT NULL]
String
{#char’s1}
TEXT20-65,535 Char’sBINARY, CHARACTER SETNULL
[“” if NOT NULL]
String
{#char’s1}
MEDIUMTEXT20-16,777,215 Char’sBINARY, CHARACTER SETNULL
[“” if NOT NULL]
String
{#char’s1}
LONGTEXT20-4,294,967,295 Char’sBINARY, CHARACTER SETNULL
[“” 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}
TINYBLOB0-255 bytesGlobal Only
(case sensitive)
NULL
[“” if NOT NULL]
String
{#bytes1}
BLOB0-65,535 bytesGlobal Only
(case sensitive)
NULL
[“” if NOT NULL]
String
{#bytes1}
MEDIUMBLOB0-16,777,215 bytesGlobal Only
(case sensitive)
NULL
[“” if NOT NULL]
String
{#bytes1}
LONGBLOB0-4,294,967,295 bytesGlobal Only
(case sensitive)
NULL
[“” if NOT NULL]
String
{1-2 bytes}
ENUM2
(“A1″,”A2”,…)
Column is exactly 1 of 1-65,535 valuesCHARACTER SETNULL [1st value if NOT NULL]
String
{1-8 bytes}
SET2
(“A1″,”A2”,…)
Column is 0 or more values in list of 1-64 membersCHARACTER SETNULL
[“” 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-00-00 00:00:00”
if NOT NULL]
Date & Time
{3 bytes}
TIME“-838:59:59” – “838:59:59”Global Only
(hh:mm:ss)
NULL
[“00:00:00” if NOT NULL]
Date & Time
{4 bytes}
TIMESTAMP19700101000000 –
2037+
Global Only
(YYYYMMDDhhmmss)
Current Date & Time
Date & Time
{1 bytes}
YEAR1900 – 2155Global Only
(YYYY)
NULL
[“0000” if NOT NULL]

Notes:

  • 1 Storage will be # of characters or bytes, plus byte(s) to record length.
  • 2 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:
    • CHAR vs. BINARY
    • VARCHAR vs. VARBINARY
    • TEXT vs. BLOB

The latest MySQL documentation is at the MySQL Web Site, but I highly recommend getting the book “MySQL” by Paul DuBois for a very readable and thorough book on the subject. There is a sample database that you can download to follow along with the examples in the book. Good Stuff. These notes are from his book on the 4.1 and 5.0 versions of MySQL.

Similar Posts