MySql: Data Types

2008-05-03T12:00:00+10:00

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's¹}	VARCHAR(M)	M=0-65,535 Characters M=0-255 <v5.0.3	BINARY, CHARACTER SET	NULL ["" if NOT NULL]
String {#char's¹}	TINYTEXT²	0-255 Characters	BINARY, CHARACTER SET	NULL ["" if NOT NULL]
String {#char's¹}	TEXT²	0-65,535 Char's	BINARY, CHARACTER SET	NULL ["" if NOT NULL]
String {#char's¹}	MEDIUMTEXT²	0-16,777,215 Char's	BINARY, CHARACTER SET	NULL ["" if NOT NULL]
String {#char's¹}	LONGTEXT²	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 {#bytes¹}	TINYBLOB	0-255 bytes	Global Only (case sensitive)	NULL ["" if NOT NULL]
String {#bytes¹}	BLOB	0-65,535 bytes	Global Only (case sensitive)	NULL ["" if NOT NULL]
String {#bytes¹}	MEDIUMBLOB	0-16,777,215 bytes	Global Only (case sensitive)	NULL ["" if NOT NULL]
String {#bytes¹}	LONGBLOB	0-4,294,967,295 bytes	Global Only (case sensitive)	NULL ["" if NOT NULL]
String {1-2 bytes}	ENUM² ("A1","A2",...)	Column is exactly 1 of 1-65,535 values	CHARACTER SET	NULL [1st value if NOT NULL]
String {1-8 bytes}	SET² ("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]

MS-SQL: Data Types

2008-05-03T12:00:00+10:00

Data Types	Description
bigint	Integer data from -2^63 through 2^63-1
int	Integer data from -2^31 through 2^31 - 1
smallint	Integer data from -2^15 through 2^15 - 1
tinyint	Integer data from 0 through 255
bit	Integer data with either a 1 or 0 value
decimal	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
numeric	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
money	Monetary data values from -2^63 through 2^63 - 1
smallmoney	Monetary data values from -214,748.3648 through +214,748.3647
float	Floating precision number data from -1.79E + 308 through 1.79E + 308
real	Floating precision number data from -3.40E + 38 through 3.40E + 38
datetime	Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds
smalldatetime	Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute
char	Fixed-length character data with a maximum length of 8,000 characters
varchar	Variable-length data with a maximum of 8,000 characters
text	Variable-length data with a maximum length of 2^31 - 1 characters
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters
ntext	Variable-length Unicode data with a maximum length of 2^30 - 1 characters
binary	Fixed-length binary data with a maximum length of 8,000 bytes
varbinary	Variable-length binary data with a maximum length of 8,000 bytes
image	Variable-length binary data with a maximum length of 2^31 - 1 bytes
cursor	A reference to a cursor
sql_variant	A data type that stores values of various data types, except text, ntext, timestamp, and sql_variant
table	A special data type used to store a result set for later processing
timestamp	A database-wide unique number that gets updated every time a row gets updated
uniqueidentifier	A globally unique identifier

John's Technology Blog

MySql: Data Types

MS-SQL: Data Types