Mysql DataTypes?
BOOLEAN - A boolean value either true, false or null.
CHAR(size) or CHARACTER(size) - A string of fixed length. The maximum size of a CHAR string is 1 billion characters.
VARCHAR(size), LONGVARCHAR(size), CHARACTER VARYING(size), LONG CHARACTER VARYING(size), TEXT(size) or STRING(size) - A string of variable length. The size constraint of these string types do not have to be given and defaults to the maximum possible size of strings that the database is able to store. The maximum size of these string types is 1 billion characters.
CLOB - A string of variable length without a size constraint. The maximum size of a CLOB object is 1 billion characters.
TINYINT - An 8-bit signed integer value. The range of TINYINT is -128 to 127.
SMALLINT - A 16-bit signed integer value. The range of SMALLINT is -32768 to 32767.
INTEGER or INT - A 32-bit signed integer value. The range of INTEGER is -2147483648 to 2147483647.
BIGINT - A 64-bit signed integer value. The range of BIGINT is -9223372036854775808 to 9223372036854775807.
FLOAT or DOUBLE - A 64-bit precision floating point value. These types are analogous to the Java double type.
REAL, NUMERIC or DECIMAL - A higher precision numeric value. These numeric types are represented by java.math.BigDecimal and therefore can represent numeric values of any precision and scale.
DATE - A day/month/year value. The DATE type does not have any near time bounding issues and is able to represent dates many millennia in the future and the past. The TIMESTAMP type is internally represented by java.util.Date.
TIME - A time of day value.
TIMESTAMP - A day/month/year and time of day value. The TIMESTAMP type does not have any near time bounding issues and is able to represent dates many millennia in the future and the past. The TIMESTAMP type is internally represented by java.util.Date.
BINARY(size), VARBINARY(size) or LONGVARBINARY(size) - A variable sized binary object. The size constraint is optional and defaults to the maximum size. The maximum size of a binary object is 2 billion bytes.
BLOB - A variable sized binary object with no size constraint. The maximum size of a binary object is 2 billion bytes.
JAVA_OBJECT - A Java object. A Java object must implement the java.io.Serializable interface and abide by the rules of serializable objects in Java.
-----------------------------------------------------------------------------------------------------------------------------------
Exact Numeric Data Types:
DATA TYPE FROM TO
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
bit 0 1
decimal -10^38 +1 10^38 -1
numeric -10^38 +1 10^38 -1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647
Approximate Numeric Data Types:
DATA TYPE FROM TO
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38
Date and Time Data Types:
DATA TYPE FROM TO
datetime
Jan 1, 1753 Dec 31, 9999
smalldatetime Jan 1, 1900 Jun 6, 2079
date Stores a date like June 30, 1991
time Stores a time of day like 12:30 P.M.
Note: Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.
Character Strings Data Types:
DATA TYPE FROM TO
char char Maximum length of 8,000 characters.( Fixed length non-Unicode characters)
varchar varchar Maximum of 8,000 characters.(Variable-length non-Unicode data).
varchar(max) varchar(max) Maximum length of 231characters, Variable-length non-Unicode data (SQL Server 2005 only).
text text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.
Unicode Character Strings Data Types:
DATA TYPE Description
nchar Maximum length of 4,000 characters.( Fixed length Unicode)
nvarchar Maximum length of 4,000 characters.(Variable length Unicode)
nvarchar(max) Maximum length of 231characters (SQL Server 2005 only).( Variable length Unicode)
ntext Maximum length of 1,073,741,823 characters. ( Variable length Unicode )
Binary Data Types:
DATA TYPE Description
binary Maximum length of 8,000 bytes(Fixed-length binary data )
varbinary Maximum length of 8,000 bytes.(Variable length binary data)
varbinary(max) Maximum length of 231 bytes (SQL Server 2005 only). ( Variable length Binary data)
image Maximum length of 2,147,483,647 bytes. ( Variable length Binary Data)
Misc Data Types:
DATA TYPE Description
sql_variant Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
timestamp Stores a database-wide unique number that gets updated every time a row gets updated
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
cursor Reference to a cursor object
table Stores a result set for later processing
-------------------------------------------------------------------------------
CHARACTER(n) Character string. Fixed-length n
VARCHAR(n) or
CHARACTER VARYING(n) Character string. Variable length. Maximum length n
BINARY(n) Binary string. Fixed-length n
BOOLEAN Stores TRUE or FALSE values
VARBINARY(n) or
BINARY VARYING(n) Binary string. Variable length. Maximum length n
INTEGER(p) Integer numerical (no decimal). Precision p
SMALLINT Integer numerical (no decimal). Precision 5
INTEGER Integer numerical (no decimal). Precision 10
BIGINT Integer numerical (no decimal). Precision 19
DECIMAL(p,s) Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal
NUMERIC(p,s) Exact numerical, precision p, scale s. (Same as DECIMAL)
FLOAT(p) Approximate numerical, mantissa precision p. A floating number in base 10 exponential notation. The size argument for this type consists of a single number specifying the minimum precision
REAL Approximate numerical, mantissa precision 7
FLOAT Approximate numerical, mantissa precision 16
DOUBLE PRECISION Approximate numerical, mantissa precision 16
DATE Stores year, month, and day values
TIME Stores hour, minute, and second values
TIMESTAMP Stores year, month, day, hour, minute, and second values
INTERVAL Composed of a number of integer fields, representing a period of time, depending on the type of interval
ARRAY A set-length and ordered collection of elements
MULTISET A variable-length and unordered collection of elements
XML Stores XML data
BOOLEAN - A boolean value either true, false or null.
CHAR(size) or CHARACTER(size) - A string of fixed length. The maximum size of a CHAR string is 1 billion characters.
VARCHAR(size), LONGVARCHAR(size), CHARACTER VARYING(size), LONG CHARACTER VARYING(size), TEXT(size) or STRING(size) - A string of variable length. The size constraint of these string types do not have to be given and defaults to the maximum possible size of strings that the database is able to store. The maximum size of these string types is 1 billion characters.
CLOB - A string of variable length without a size constraint. The maximum size of a CLOB object is 1 billion characters.
TINYINT - An 8-bit signed integer value. The range of TINYINT is -128 to 127.
SMALLINT - A 16-bit signed integer value. The range of SMALLINT is -32768 to 32767.
INTEGER or INT - A 32-bit signed integer value. The range of INTEGER is -2147483648 to 2147483647.
BIGINT - A 64-bit signed integer value. The range of BIGINT is -9223372036854775808 to 9223372036854775807.
FLOAT or DOUBLE - A 64-bit precision floating point value. These types are analogous to the Java double type.
REAL, NUMERIC or DECIMAL - A higher precision numeric value. These numeric types are represented by java.math.BigDecimal and therefore can represent numeric values of any precision and scale.
DATE - A day/month/year value. The DATE type does not have any near time bounding issues and is able to represent dates many millennia in the future and the past. The TIMESTAMP type is internally represented by java.util.Date.
TIME - A time of day value.
TIMESTAMP - A day/month/year and time of day value. The TIMESTAMP type does not have any near time bounding issues and is able to represent dates many millennia in the future and the past. The TIMESTAMP type is internally represented by java.util.Date.
BINARY(size), VARBINARY(size) or LONGVARBINARY(size) - A variable sized binary object. The size constraint is optional and defaults to the maximum size. The maximum size of a binary object is 2 billion bytes.
BLOB - A variable sized binary object with no size constraint. The maximum size of a binary object is 2 billion bytes.
JAVA_OBJECT - A Java object. A Java object must implement the java.io.Serializable interface and abide by the rules of serializable objects in Java.
-----------------------------------------------------------------------------------------------------------------------------------
Exact Numeric Data Types:
DATA TYPE FROM TO
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
bit 0 1
decimal -10^38 +1 10^38 -1
numeric -10^38 +1 10^38 -1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647
Approximate Numeric Data Types:
DATA TYPE FROM TO
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38
Date and Time Data Types:
DATA TYPE FROM TO
datetime
Jan 1, 1753 Dec 31, 9999
smalldatetime Jan 1, 1900 Jun 6, 2079
date Stores a date like June 30, 1991
time Stores a time of day like 12:30 P.M.
Note: Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.
Character Strings Data Types:
DATA TYPE FROM TO
char char Maximum length of 8,000 characters.( Fixed length non-Unicode characters)
varchar varchar Maximum of 8,000 characters.(Variable-length non-Unicode data).
varchar(max) varchar(max) Maximum length of 231characters, Variable-length non-Unicode data (SQL Server 2005 only).
text text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.
Unicode Character Strings Data Types:
DATA TYPE Description
nchar Maximum length of 4,000 characters.( Fixed length Unicode)
nvarchar Maximum length of 4,000 characters.(Variable length Unicode)
nvarchar(max) Maximum length of 231characters (SQL Server 2005 only).( Variable length Unicode)
ntext Maximum length of 1,073,741,823 characters. ( Variable length Unicode )
Binary Data Types:
DATA TYPE Description
binary Maximum length of 8,000 bytes(Fixed-length binary data )
varbinary Maximum length of 8,000 bytes.(Variable length binary data)
varbinary(max) Maximum length of 231 bytes (SQL Server 2005 only). ( Variable length Binary data)
image Maximum length of 2,147,483,647 bytes. ( Variable length Binary Data)
Misc Data Types:
DATA TYPE Description
sql_variant Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
timestamp Stores a database-wide unique number that gets updated every time a row gets updated
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
cursor Reference to a cursor object
table Stores a result set for later processing
-------------------------------------------------------------------------------
CHARACTER(n) Character string. Fixed-length n
VARCHAR(n) or
CHARACTER VARYING(n) Character string. Variable length. Maximum length n
BINARY(n) Binary string. Fixed-length n
BOOLEAN Stores TRUE or FALSE values
VARBINARY(n) or
BINARY VARYING(n) Binary string. Variable length. Maximum length n
INTEGER(p) Integer numerical (no decimal). Precision p
SMALLINT Integer numerical (no decimal). Precision 5
INTEGER Integer numerical (no decimal). Precision 10
BIGINT Integer numerical (no decimal). Precision 19
DECIMAL(p,s) Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal
NUMERIC(p,s) Exact numerical, precision p, scale s. (Same as DECIMAL)
FLOAT(p) Approximate numerical, mantissa precision p. A floating number in base 10 exponential notation. The size argument for this type consists of a single number specifying the minimum precision
REAL Approximate numerical, mantissa precision 7
FLOAT Approximate numerical, mantissa precision 16
DOUBLE PRECISION Approximate numerical, mantissa precision 16
DATE Stores year, month, and day values
TIME Stores hour, minute, and second values
TIMESTAMP Stores year, month, day, hour, minute, and second values
INTERVAL Composed of a number of integer fields, representing a period of time, depending on the type of interval
ARRAY A set-length and ordered collection of elements
MULTISET A variable-length and unordered collection of elements
XML Stores XML data
Comments
Post a Comment