Every column within a database table necessitates a name and a data type assignment.
When crafting a table, an SQL developer must determine the kind of data each column will store. The data type serves as a directive for SQL, indicating the expected data type for each column and governing how SQL interacts with the stored data.
| Reminder: Data types can vary in name across different databases. Additionally, even if the name is consistent, differences in size and other specifications may exist. It’s advisable to refer to the documentation for accurate details. |
MySQL primarily categorizes data into three main types: strings, numerics, and date/time.
|
Data type |
Description |
|
CHAR(size) |
A fixed-length string in MySQL can accommodate letters, numbers, and special characters. The ‘size’ parameter denotes the column length, ranging from 0 to 255 characters, with a default of 1. |
|
VARCHAR(size) |
A variable-length string in MySQL can include letters, numbers, and special characters. The ‘size’ parameter indicates the maximum string length, ranging from 0 to 65535 characters. |
|
BINARY(size) |
Similar to CHAR(), BINARY in MySQL also stores binary byte strings. The ‘size’ parameter determines the column length in bytes, with a default of 1. |
|
VARBINARY(size) |
Equivalent to VARCHAR(), BINARY in MySQL stores binary byte strings. The ‘size’ parameter indicates the maximum column length in bytes. |
|
TINYBLOB |
Regarding Binary Large Objects (BLOBs), their maximum length is 255 bytes. |
|
TINYTEXT |
This data type accommodates a string with a maximum length of 255 characters. |
|
TEXT(size) |
This type can store a string with a maximum length of 65,535 bytes. |
|
BLOB(size) |
For Binary Large Objects (BLOBs), it can contain data of up to 65,535 bytes. |
|
MEDIUMTEXT |
This type accommodates a string with a maximum length of 16,777,215 characters. |
|
MEDIUMBLOB |
For Binary Large Objects (BLOBs), it can contain data of up to 16,777,215 bytes. |
|
LONGTEXT |
This data type supports a string with a maximum length of 4,294,967,295 characters. |
|
LONGBLOB |
For Binary Large Objects (BLOBs), it can store data of up to 4,294,967,295 bytes. |
|
ENUM(val1, val2, val3, …) |
An ENUM in MySQL represents a string object with a single value chosen from a predetermined list, which can contain up to 65,535 values. When inserting a value outside the list, it defaults to a blank value. The values maintain the order in which they are entered. |
|
SET(val1, val2, val3, …) |
This type represents a string object capable of containing zero or more values selected from a defined list of options. In a SET list, you can specify up to 64 values. |
|
Data type |
Description |
|
BIT(size) |
A bit-value type where the size parameter specifies the number of bits per value, ranging from 1 to 64, with a default size of 1. |
|
TINYINT(size) |
A tiny integer, with a signed range of -128 to 127 and an unsigned range of 0 to 255. The size parameter determines the maximum display width, capped at 255. |
|
BOOL |
In this context, zero is regarded as false, while any nonzero value is regarded as true. |
|
BOOLEAN |
Equivalent to a BOOL type |
|
SMALLINT(size) |
A compact integer with a signed range from -32768 to 32767 and an unsigned range from 0 to 65535. The size parameter indicates the maximum display width, constrained to 255. |
|
MEDIUMINT(size) |
A medium-sized integer, with a signed range spanning from -8388608 to 8388607 and an unsigned range from 0 to 16777215. The size parameter determines the maximum display width, capped at 255. |
|
INT(size) |
A medium-sized integer, with a signed range extending from -2147483648 to 2147483647 and an unsigned range from 0 to 4294967295. The size parameter determines the maximum display width, limited to 255. |
|
INTEGER(size) |
Equivalent to INT(size) |
|
BIGINT(size) |
A substantial integer, with a signed range from -9223372036854775808 to 9223372036854775807 and an unsigned range from 0 to 18446744073709551615. The size parameter determines the maximum display width, capped at 255. |
|
FLOAT(size, d) |
A floating-point number where the total number of digits is defined by the size parameter, and the number of digits after the decimal point is specified by the d parameter. This syntax, deprecated in MySQL 8.0.17, will be eliminated in future MySQL versions. |
|
FLOAT(p) |
A floating-point number in MySQL, where the p value determines whether FLOAT or DOUBLE is used for the resulting data type. If p ranges from 0 to 24, the data type becomes FLOAT(); if p ranges from 25 to 53, the data type becomes DOUBLE(). |
|
DOUBLE(size, d) |
A standard floating-point number, where the total number of digits is determined by the size parameter, and the number of digits after the decimal point is specified by the d parameter. |
|
DOUBLE PRECISION(size, d) |
|
|
DECIMAL(size, d) |
A precise fixed-point number with a specified total number of digits denoted by the size parameter and the number of digits after the decimal point specified by the d parameter. The maximum values allowed are 65 for size and 30 for d. The default value for size is 10, and for d, it’s 0. |
|
DEC(size, d) |
Equivalent to DECIMAL(size, d) |
Please note that for all numeric data types, there’s an additional option available: either UNSIGNED or ZEROFILL. When you use the UNSIGNED option, MySQL restricts the column from accepting negative values. If you opt for the ZEROFILL option, MySQL automatically includes the UNSIGNED attribute for the column, and it pads the displayed values with zeros.
|
Data type |
Description |
|
DATE |
A date in the format YYYY-MM-DD is supported, ranging from ‘1000-01-01’ to ‘9999-12-31’. |
|
DATETIME(fsp |
A date and time combination in the format YYYY-MM-DD hh:mm:ss is supported, with a range from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. You can add DEFAULT and ON UPDATE in the column definition to enable automatic initialization and updating to the current date and time. |
|
TIMESTAMP(fsp) |
A timestamp is stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC), formatted as YYYY-MM-DD hh:mm:ss. The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. To enable automatic initialization and updating to the current date and time, you can use DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition. |
|
TIME(fsp) |
A time is formatted as hh:mm:ss, with a supported range from ‘-838:59:59’ to ‘838:59:59’. |
|
YEAR |
A year in four-digit format is supported, with values allowed ranging from 1901 to 2155, inclusive of 0000. It’s worth noting that MySQL 8.0 does not support the year in two-digit format. |
|
Data type |
Description |
Max size |
Storage |
|
char(n) |
A fixed-width character string. |
8,000 characters |
Defined width |
|
varchar(n) |
A variable-width character string. |
8,000 characters |
2 bytes + number of chars |
|
varchar(max) |
A variable-width character string. |
1,073,741,824 characters |
2 bytes + number of chars |
|
text |
A variable-width character string. |
2GB of text data |
4 bytes + number of chars |
|
nchar |
A fixed-width Unicode string. |
4,000 characters |
Defined width x 2 |
|
nvarchar |
A variable-width Unicode string. |
4,000 characters |
|
|
nvarchar(max) |
A variable-width Unicode string. |
536,870,912 characters |
|
|
ntext |
A variable-width Unicode string. |
2GB of text data |
|
|
binary(n) |
A fixed-width binary string. |
8,000 bytes |
|
|
varbinary |
A variable-width binary string. |
8,000 bytes |
|
|
varbinary(max) |
A variable-width binary string. |
2GB |
|
|
image |
A variable-width binary string. |
2GB |
|
Data type |
Description |
Storage |
|
bit |
An integer that may have a value of 0, 1, or be unspecified (NULL). |
|
|
tinyint |
Permits integers ranging from 0 to 255. |
1 byte |
|
smallint |
Permits integers within the range of -32,768 to 32,767. |
2 bytes |
|
int |
Permits integers within the range of -2,147,483,648 to 2,147,483,647. |
4 bytes |
|
bigint |
Permits integers within the range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. |
8 bytes |
|
decimal(p,s) |
Fixed precision and scale numbers are supported. They accommodate values ranging from -10^38 +1 to 10^38 –1. The parameter “p” denotes the maximum total number of digits that can be stored, encompassing both sides of the decimal point. It must be a value between 1 and 38, with a default setting of 18. The parameter “s” represents the maximum number of digits stored to the right of the decimal point. It must be a value between 0 and “p”. The default value is 0. |
5-17 bytes |
|
numeric(p,s) |
Fixed precision and scale numbers support a range of values from -10^38 +1 to 10^38 –1. The “p” parameter signifies the maximum total number of digits that can be accommodated, inclusive of both sides of the decimal point. Its acceptable range is from 1 to 38, with a default setting of 18. The “s” parameter denotes the maximum number of digits retained to the right of the decimal point. Its acceptable range is from 0 to “p”. The default value is 0. |
5-17 bytes |
|
smallmoney |
Monetary data ranges from -214,748.3648 to 214,748.3647. |
4 bytes |
|
money |
Monetary data spans from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. |
8 bytes |
|
float(n) |
Floating-point numbers range from -1.79E+308 to 1.79E+308. The “n” parameter specifies whether the field should accommodate 4 or 8 bytes. A float(24) designates a 4-byte field, while a float(53) designates an 8-byte field. By default, “n” is set to 53. |
4 or 8 bytes |
|
real |
Floating-point number data spans from -3.40E + 38 to 3.40E + 38. |
4 bytes |
|
Data type |
Description |
Storage |
|
datetime |
From January 1, 1753, to December 31, 9999, with a precision of 3.33 milliseconds. |
8 bytes |
|
datetime2 |
From January 1, 0001, to December 31, 9999, with a precision of 100 nanoseconds. |
6-8 bytes |
|
smalldatetime |
From January 1, 1900, to June 6, 2079, with a precision of 1 minute. |
4 bytes |
|
date |
Store dates exclusively, covering the range from January 1, 0001, to December 31, 9999. |
3 bytes |
|
time |
Store time exclusively with an accuracy of 100 nanoseconds. |
3-5 bytes |
|
datetimeoffset |
Similar to datetime2, but with the inclusion of a time zone offset. |
8-10 bytes |
|
timestamp |
Stores a unique number that updates each time a row is created or modified. The timestamp value derives from an internal clock and does not reflect real-time. Each table can only have one timestamp variable |
|
Data type |
Description |
|
sql_variant |
Stores data of various data types, excluding text, ntext, and timestamp, with a capacity of up to 8,000 bytes. |
|
uniqueidentifier |
Stores a universally unique identifier (UUID). |
|
xml |
Stores data formatted in XML, with a maximum capacity of 2GB. |
|
cursor |
Stores a reference to a cursor utilized for database operations. |
|
table |
Stores a result-set for subsequent processing. |
|
Data type |
Description |
Storage |
|
Text |
Suitable for text or combinations of text and numbers, with a maximum length of 255 characters. |
|
|
Memo |
Memo is employed for handling larger amounts of text, capable of storing up to 65,536 characters. Note: Memo fields cannot be sorted, but they are searchable. |
|
|
Byte |
Permits integers ranging between 0 and 255. |
1 byte |
|
Integer |
Permits integers within the range of -32,768 to 32,767. |
2 bytes |
|
Long |
Permits integers within the range of -2,147,483,648 to 2,147,483,647. |
4 bytes |
|
Single |
Single precision floating-point. Suitable for handling most decimals. |
4 bytes |
|
Double |
Double precision floating-point. Suitable for handling most decimals. |
8 bytes |
|
Currency |
This datatype is intended for currency usage. It can hold up to 15 digits of whole dollars, with an additional 4 decimal places. Note: You have the option to specify the currency of your choice. |
8 bytes |
|
AutoNumber |
AutoNumber fields automatically assign a unique number to each record, typically starting from 1. |
4 bytes |
|
Date/Time |
Designed for storing dates and times. |
8 bytes |
|
Yes/No |
A logical field can be represented as Yes/No, True/False, or On/Off. In programming, employ the constants True and False (equivalent to -1 and 0). Note: Null values are not permitted in Yes/No fields. |
1 bit |
|
Ole Object |
Capable of storing pictures, audio, video, or other Binary Large Objects (BLOBs). |
up to 1GB |
|
Hyperlink |
Contain references or links to other files, including web pages. |
|
|
Lookup Wizard |
Enable the input of a list of options, which can subsequently be selected from a drop-down menu. |
4 bytes |
