MySql: Data Types

johna by | May 3, 2008 | Databases Web Development

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

Web Development

How to set up a debugging using the Turnkey Linux LAMP stack and VS Code

by johna | December 19, 2023
The second part in my guide to setting up a website and database using the Turnkey Linux LAMP stack.

Website Hosting Web Development

How to set up a website and database using the Turnkey Linux LAMP stack

by johna | November 18, 2023
If you need to host your own website for the purposes of web development, Turnkey Linux LAMP Stack is an easy to install all-in-one solution that you can set up on a spare computer or a VM (Virtual Machine).

Web Development

Intermittent "Unable to read data from the transport connection: net_io_connectionclosed" errors

by johna | May 6, 2020
If you are having intermittent problems sending email in .NET using System.Net.Mail consider switching libraries.

Comments

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

Leave a Comment

About

...random postings about web development and programming, Internet, computers and electronics topics.

I recommend ASPnix for web hosting and Crazy Domains for domain registration.

Subscribe

Get the latest posts delivered to your inbox.