MySql: Data Types

John Avis by | May 3, 2008 | Databases

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 |
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]

Related Posts

ASP.NET Web Forms Databases

Using SqlDataReader NextResult to improve performance

by John Avis | July 31, 2014

On database-driven websites it is commonplace to execute multiple database queries to generate just one page. For example, on the home page of an ecommerce website you might get a list of categories, a list of banner advertisements to display, a list of featured products, and a list of top selling products. I investigate whether it was more efficient to put these multiple queries into one stored procedure and use the SqlDataReader's Next Result method to get the data all in one go.


ASP.NET Web Forms Databases

Linq to SQL - Order By Nulls Last

by John Avis | April 7, 2011

Using Linq to SQL and need to order by NULLS last?


Databases

MS-SQL: Data Types

by John Avis | May 3, 2008

Comments

There are no comments yet. Be the first to leave a comment!

Leave a Comment
Tags
ASP.NET Html Forms ASP.NET MVC ASP.NET Web Forms ASP.NET Web Pages Bootstrap C# Classic ASP Cool Websites Databases eBay and PayPal Electrical Repairs General Hardware HTML/CSS Jquery/Javascript Media Center Mobile Phones Responsive Web Design SEO and Social Networking Web Design Web Development Web Security web+db Website Hosting Windows XP

About me

...mostly about web development and programming, with a little bit of anything else related to the Internet, computers and technology.

Subscribe

Get the latest posts delivered to your inbox.