Reference:SQL/Data Types

From CoderGuide

Jump to: navigation, search


MySQL/SQL Data Types

Note: Not all of these may be present in your SQL server, but they are in MySQL. Other names may be valid as well. For instance, MySQL will treat "LONG VARCHAR" as the MEDIUMTEXT type. Your server may have other types. Consult your manual.

Numeric Types:

If you remember from Math, integers are whole numbers. These may either be signed or unsigned values. The default is signed integers. Pay close attention to the valid range of these data types.

Type NameData typeSigned RangeUnsigned Range
TINYINTan 8-bit integer-128 to 1270 to 255
SMALLINT16-bit integer-32,768 to 32,7670 to 65,535
MEDIUMINT24-bit integer-8,388,608 to 8,388,6070 to 16,777,215
INT32-bit integer-2,147,483,648 to 2,147,483,6470 to 4,294,967,295
BIGINT64-bit integer-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 0 to 18,446,744,073,709,551,615

Values taken from MySQL supports the use of INT(number) to specify the maximum expected display width of the type-- IT DOES NOT EFFECT THE STORAGE RANGE OF THE TYPE. Also, if you wish to zero pad your number, you can use add ZEROFILL. For example, if you use INT(4) ZEROFILL, and the value is 4, MySQL wil display 0004.

Other numeric types:

NameData Type
FLOAT(see note) a floating point number. The exact range varies from architecture and compilers. This generally would be the same range as the Float type in C/C++ for the compiler that was used to compile MySQL/WhateverSQL on your system.
DOUBLE(see note)another floating point number type, with more significant digits than FLOAT. The last digit is estimated, and is always either 0 or even (traditionally, though this can vary between systems). if you had 27.23 as a double, the software would store it as 27.230. If you ran out of significant digits, then you'll see the effects of double precision.
DECIMALStores a number up to 65 digits in length, can be constrained with DECIMAL(M,D), where M is the total number of digits allowed, and D is the number allowed to the right of the decimal point. MySQL 5.1 stores DECIMAL values as binary data, earlier versions store it as an ASCII string. D and M may or may not be required. MySQL versions prior to 3.23.6 require both, while versions after will use zero for D if it's omitted, and ten for M if it is omitted.
NUMERICEssentially the same as DECIMAL
BITA bit field type. BIT(b), where b indicates the number of bits. Default is 1. Bit fields are binary data, specified bt b'bits'. Exampe: b'1001'

Note: Float and Double types should never be used to store money values, as these number types are approximates, and not the actual value. Either used fixed point integers (i.e. 10000 would be $100.00), or use DECIMAL which stores a number as a string in MySQL versions prior to 5.1. The ideal method is to store the number as a bigint fixed point type.

Character/Text types

Type NameData type
CHARfixed length string, left padded with spaces. Trailing spaces are stripped off on retrieval.
VARCHARvariable length string that uses no more memory than required. MySQL currently strips off trailing spaces when the string is stored, which is different from the ANSI SQL standard.
TINYTEXTfor storing text/string (254 bytes)
TEXTalso for storing text (64 Kilobytes)
MEDIUMTEXTfor storing more text (16 Megabytes)
LONGTEXTfor storing a lot of text (4 Gigabytes)
ENUMAn Enumeration type. Columns may only be assigned to the values specified in the enumeration. In other words, ENUM('a','b','Zippidy-Doo-Day') would only allow for the field to contain "a","b", or "Zippidy-Do-Day", anything else would result in an error. Another more common example would be ENUM('T','F'), or ENUM('M','F')
SETFor storing a set of information. It's similar to ENUM, except that the field may contain any, or all predefined values. These sets are separated by commas.So, to set all the values for SET("Alpha","Beta","Gamma") use the string 'Alpha,Beta,Gamma'.

Storing Binary Data

The BLOB (Binary Large OBject) type is used for storing binary data. It is similar to the TEXT type, except the data is treated as binary, not text. Both TEXT and BLOB types have the same storage limits.

Type NameData TypeStorage Size
TINYBLOBFor storing binary data 28-1 bytes (254 bytes)
BLOBFor storing binary data216-1 bytes (64 Kilobytes)
MEDIUMBLOBFor storing binary data224-1 bytes (16 Megabytes)
LONGBLOBFor storing binary data232-1 bytes (4 Gigabytes)

BLOB types can not be assigned a default value.

Date/Time types

For the purpose of the table below:
CC -- Century (so, if the year was 1990, 19 would be CC)
YY -- Year (if the year was 1990, 90 would be YY)
MM -- The month
DD -- The day of the month
hh -- Hours
mm -- Minutes
ss -- Seconds

Type NameData Type
TIMETime in hh:mm:ss
DATETIMEDate/Time in CCYY-MM-DD hh:mm:ss
YEARThe year in CCYY

Other Types

Type NameData Type
For true/false values. The ISO standard sets this as being an optional type, and there is some debate on how to handle NULL values (ISO allows for NULL/UNKNOWN). Currently BOOLEAN type is supported by MySQL but maps these values to 1 for true and 0 for false, PostgreSQL follows the ISO standard except doesn't allow for the UNKNOWN literal. BOOLEAN is not supported by Microsoft SQL (MSSQL), DB2, or Oracle.
Personal tools