Printed from http://kimbriggs.com

MySQL Data Types Quick Reference Table: MySQL Version 5.0

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

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",...)
Column is exactly 1 of 1-65,535 values CHARACTER SET NULL [1st value if NOT NULL]
String
{1-8 bytes}
SET2
("A1","A2",...)
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-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}
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:

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.

Spider Oak Online Backup

 
CC License Ubuntu OpenOffice GIMP Graphics Geany Editor Firefox php.net Play Ogg what's this?