If you are in the field of software development or web programming, you must have frequent interaction with databases, whether Microsoft Access, MySQL, Oracle or SQL Server. In a database, once you got table data organize, the next thing is to work out data types. There are three main types of data, numbers, text, and date – time. To handle different types of data different databases have data types with same or different sizes, ranges and use. This post presents data types in MS Access, MySQL and ranges.

Microsoft Access

Text         255 characters maximum      
Memo         Stores up to 65,536 characters.
Byte         Allows whole numbers from 0 to 255    
Integer     Allows whole numbers between -32,768 and 32,767
Long         Allows whole numbers between -2,147,483,648 and 2,147,483,647
Single         Will handle most decimals
Double     Will handle most decimals
Currency     Holds up to 15 digits of whole dollars, plus 4 decimal places
AutoNumber     Automatically give each record its own number, starting at 1- 4 bytes
Date/Time     Use for dates and times
Yes/No     A logical field can be displayed as Yes/No, True/False, or On/Off.
Ole Object     Can store pictures, audio, video, or other BLOBs up to 1GB
Hyperlink     Contain links to other files, including web pages

MySQL

Text types:

CHAR(size)         Can store up to 255 characters
VARCHAR(size)     Holds a variable length Can store up to 255 characters. TINYTEXT         Holds a string with a maximum length of 255 characters
TEXT             Holds a string with a maximum length of 65,535 characters
BLOB             Binary Large Objects Holds up to 65,535 bytes of data
MEDIUMTEXT     Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB     BLOBs. Holds up to 16,777,215 bytes of data
LONGTEXT         Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB         Binary Large OBjects. Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.)     Let you enter a list of possible values. You can list up to 65535
SET             Similar to ENUM except that SET may contain up to 64 list items

Number types:

TINYINT(size)     -128 to 127 normal. 0 to 255
SMALLINT(size)     -32768 to 32767 normal. 0 to 65535
MEDIUMINT(size)     -8388608 to 8388607 normal. 0 to 16777215
INT(size)         -2147483648 to 2147483647 normal. 0 to 4294967295
BIGINT(size)         -9223372036854775808 to 9223372036854775807 normal
FLOAT(size)         A small number with a floating decimal point.
DOUBLE(size)     A large number with a floating decimal point.
DECIMAL(size)     A DOUBLE stored as a string

Date types:

DATE()         A date. Format: YYYY-MM-DD
DATETIME()     A date and time combination. YYYY-MM-DD HH:MM:SS
TIMESTAMP()     A timestamp. TIMESTAMP values ('1970-01-01 00:00:00' UTC).
TIME()         A time. Format: HH:MM:SS
YEAR()         A year in two-digit or four-digit format.