Difference between revisions of "MySQL Datatypes - Overview"

Line 46: Line 46:
 
This allow both fixed and variable length strings.  
 
This allow both fixed and variable length strings.  
  
Data Type Description Storage
+
{|
CHAR(size) Holds up to 255 characters and allows a fixed length string. (Declared column length of characters * Number of bytes) <= 255
+
|width="33%"| '''Data Type'''
VARCHAR(size) Holds up to 255 characters and allows a variable length string. If you store characters greater than 55, then the data type will be converted to TEXT type.
+
|width="33%"| '''Description'''
 
+
|width="33%"| '''Storage'''
    String value(Len) + 1 WHERE column values require 0 − 255 bytes
+
|-
    String value(Len) + 2 bytes WHERE column values may require more than 255 bytes
+
| '''CHAR(size)'''
 
+
| Holds up to 255 characters and allows a fixed length string.
TINYTEXT Allows a string with a maximum length of 255 characters Actual length in bytes of String value(Len) + 1 bytes, where Len < 28
+
| (Declared column length of characters * Number of bytes) <= 255
TEXT Allows a string with a maximum length of 65,535 characters Actual length in bytes of String value(Len) + 2 bytes, where Len < 216
+
|-
BLOB Holds up to 65,535 bytes of data, and is used for Binary Large Objects. Actual length in bytes of String value(Len) + 2 bytes, where Len < 216
+
| '''VARCHAR(size)'''
MEDIUMTEXT Allows a string with a maximum length of 16,777,215 characters Actual length in bytes of String value(Len) + 3 bytes, where Len < 224
+
| Holds up to 255 characters and allows a variable length string. If you store characters greater than 55, then the data type will be converted to TEXT type.
MEDIUMBLOB Holds up to 16,777,215 bytes of data, and is used for Binary Large Objects. Actual length in bytes of String value(Len) + 3 bytes, where Len < 224
+
|
LONGTEXT Allows a string with a maximum length of 4,294,967,295 characters Actual length in bytes of String value(Len) + 4 bytes, where Len < 232
+
* String value(Len) + 1 WHERE column values require 0 − 255 bytes
LONGBLOB Holds up to 4,294,967,295 bytes of data, and is used for Binary Large Objects. Actual length in bytes of String value(Len) + 4 bytes, where Len < 232
+
* String value(Len) + 2 bytes WHERE column values may require more than 255 bytes
ENUM(x,y,z,etc.) Allows you to enter a list of possible values, with the maximum to be 65535 values. Just in case a value is inserted which is not present in the list, a blank value will be inserted. 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
+
|-
SET This data type is similar to ENUM, but SET can have up to 64 list items and can store more than one choice. 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)
+
| '''TINYTEXT'''
 +
| Allows a string with a maximum length of 255 characters
 +
| Actual length in bytes of String value(Len) + 1 bytes, where Len < 2<sup>8</sup>
 +
|-
 +
| '''TEXT'''
 +
| Allows a string with a maximum length of 65,535 characters
 +
| Actual length in bytes of String value(Len) + 2 bytes, where Len < 2<sup>16</sup>
 +
|-
 +
| '''BLOB'''
 +
| Holds up to 65,535 bytes of data, and is used for Binary Large Objects.
 +
| Actual length in bytes of String value(Len) + 2 bytes, where Len < 2<sup>16</sup>
 +
|-
 +
| '''MEDIUMTEXT'''
 +
| Allows a string with a maximum length of 16,777,215 characters
 +
| Actual length in bytes of String value(Len) + 3 bytes, where Len < 2<sup>24</sup>
 +
|-
 +
| '''MEDIUMBLOB'''
 +
| Holds up to 16,777,215 bytes of data, and is used for Binary Large Objects.
 +
| Actual length in bytes of String value(Len) + 3 bytes, where Len < 2<sup>24</sup>
 +
|-
 +
| '''LONGTEXT'''
 +
| Allows a string with a maximum length of 4,294,967,295 characters
 +
| Actual length in bytes of String value(Len) + 4 bytes, where Len < 2<sup>32</sup>
 +
|-
 +
| '''LONGBLOB'''
 +
| Holds up to 4,294,967,295 bytes of data, and is used for Binary Large Objects.
 +
| Actual length in bytes of String value(Len) + 4 bytes, where Len < 2<sup>32</sup>
 +
|-
 +
| '''ENUM(x,y,z,etc.)'''
 +
| Allows you to enter a list of possible values, with the maximum to be 65535 values. Just in case a value is inserted which is not present in the list, a blank value will be inserted.
 +
| 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
 +
|-
 +
| '''SET'''
 +
| This data type is similar to ENUM, but SET can have up to 64 list items and can store more than one choice.
 +
| 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)
 +
|}
  
 
=== Date and Time Types ===  
 
=== Date and Time Types ===  
 
This data type enables us to mention the date and time.  
 
This data type enables us to mention the date and time.  
Data Type Description Storage Required Before MySQL 5.6.4 Storage Required as of MySQL 5.6.4
+
{|
YEAR() Holds the value of year either in a two digit or in a four-digit format. Year values in the range (70-99) are converted to (1970-1999), and year values in the range (00-69) are converted to (2000-2069) 1 byte 1 byte
+
| '''Data Type'''
DATE() Holds the date values in the format: YYYY-MM-DD, where the supported range is (1000-01-01) to (9999-12-31) 3 bytes 3 bytes
+
| '''Description'''
TIME() Holds the time values in the format: HH:MI:SS, where the supported range is (-838:59:59) to (838:59:59) 3 bytes 3 bytes + fractional seconds storage
+
| '''Storage Required Before MySQL 5.6.4'''
DATETIME() A combination of date and time values in the format: YYYY-MM-DD HH:MI:SS, where the supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ 8 bytes 5 bytes + fractional seconds storage
+
| '''Storage Required as of MySQL 5.6.4'''
TIMESTAMP() Holds values which are stored as the number of seconds, with the format (YYYY-MM-DD HH:MI: SS). The supported range is from (1970-01-01 00:00:01) UTC to (2038-01-09 03:14:07) UTC 4 bytes 4 bytes + fractional second storage
+
|-
MySQL Data Types: Using Data Types from Other Database Engines
+
| '''YEAR()'''
 +
| Holds the value of year either in a two digit or in a four-digit format. Year values in the range (70-99) are converted to (1970-1999), and year values in the range (00-69) are converted to (2000-2069)
 +
| 1 byte
 +
| 1 byte
 +
|-
 +
| '''DATE()'''
 +
| Holds the date values in the format: YYYY-MM-DD, where the supported range is (1000-01-01) to (9999-12-31)
 +
| 3 bytes
 +
| 3 bytes
 +
|-
 +
| '''TIME()'''
 +
| Holds the time values in the format: HH:MI:SS, where the supported range is (-838:59:59) to (838:59:59)
 +
| 3 bytes
 +
| 3 bytes + fractional seconds storage
 +
|-
 +
| '''DATETIME()'''
 +
| A combination of date and time values in the format: YYYY-MM-DD HH:MI:SS, where the supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
 +
| 8 bytes
 +
| 5 bytes + fractional seconds storage
 +
|-
 +
| '''TIMESTAMP()'''
 +
| Holds values which are stored as the number of seconds, with the format (YYYY-MM-DD HH:MI: SS). The supported range is from (1970-01-01 00:00:01) UTC to (2038-01-09 03:14:07) UTC
 +
| 4 bytes
 +
| 4 bytes + fractional second storage
 +
|}
 +
 
  
 
=== Outside MYSQL - In other SQL versions ===
 
=== Outside MYSQL - In other SQL versions ===
 
If you want to implement the code written by other vendors in SQL, then, MySQL facilitates this by mapping data types:
 
If you want to implement the code written by other vendors in SQL, then, MySQL facilitates this by mapping data types:
  
Other Vendor Type MySQL Type
+
{|
BOOL TINYINT
+
| '''Other Vendor Type'''
BOOLEAN TINYINT
+
| '''MySQL Type'''
CHARACTER VARYING(M) VARCHAR(M)
+
|-
FIXED DECIMAL
+
| BOOL
FLOAT4 FLOAT
+
| TINYINT
FLOAT8 DOUBLE
+
|-
INT1 TINYINT
+
| BOOLEAN
INT2 SMALLINT
+
| TINYINT
INT3 MEDIUMINT
+
|-
INT4 INT
+
| CHARACTER VARYING(M)
INT8 BIGINT
+
| VARCHAR(M)
LONG VARBINARY
+
|-
MEDIUMBLOB
+
| FIXED
LONG VARCHAR MEDIUMTEXT
+
| DECIMAL
LONG MEDIUMTEXT
+
|-
MIDDLEINT MEDIUMINT
+
| FLOAT4
NUMERIC DECIMAL
+
| FLOAT
 +
|-
 +
| FLOAT8
 +
| DOUBLE
 +
|-
 +
| INT1
 +
| TINYINT
 +
|-
 +
| INT2
 +
| SMALLINT
 +
|-
 +
| INT3
 +
| MEDIUMINT
 +
|-
 +
| INT4
 +
| INT
 +
|-
 +
| INT8
 +
| BIGINT
 +
|-
 +
| LONG VARBINARY<br />
 +
| MEDIUMBLOB
 +
|-
 +
| LONG VARCHAR
 +
| MEDIUMTEXT
 +
|-
 +
| LONG
 +
| MEDIUMTEXT
 +
|-
 +
| MIDDLEINT
 +
| MEDIUMINT
 +
|-
 +
| NUMERIC
 +
| DECIMAL
 +
|}

Revision as of 15:26, 15 August 2019

An overview of database (tables) datatypes in MySQL. Used in creation of tables.

MySQL Data Types: Numeric Types

Numeric Datatypes allow both signed and unsigned integers.

Data Type Description Storage
TINYINT(size) Allows signed integers -128 to 127 and 0 to 255 unsigned integers. 1 byte
SMALLINT(size) Allows signed integers from -32768 to 32767 and 0 to 65535 unsigned integers. 2 bytes
MEDIUMINT(size) Allows signed integers from -8388608 to 8388607 and 0 to 16777215 unsigned integers. 3 bytes
INT(size) Allows signed integers from -2147483638 to 214747483637 and 0 to 4294967925 unsigned integers. 4 bytes
BIGINT(size) Allows signed integers from -9223372036854775808 to 9223372036854775807 and 0 to 18446744073709551615 unsigned integers. 8 bytes
FLOAT(size,d) Allows small numbers with floating decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal. 4 bytes
DOUBLE(size,d) Allows large numbers with floating decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal. 8 bytes
DECIMAL(size,d)
Allows storing DOUBLE as a string, so that there is a fixed decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal. Varies

String Data types

This allow both fixed and variable length strings.

Data Type Description Storage
CHAR(size) Holds up to 255 characters and allows a fixed length string. (Declared column length of characters * Number of bytes) <= 255
VARCHAR(size) Holds up to 255 characters and allows a variable length string. If you store characters greater than 55, then the data type will be converted to TEXT type.
  • String value(Len) + 1 WHERE column values require 0 − 255 bytes
  • String value(Len) + 2 bytes WHERE column values may require more than 255 bytes
TINYTEXT Allows a string with a maximum length of 255 characters Actual length in bytes of String value(Len) + 1 bytes, where Len < 28
TEXT Allows a string with a maximum length of 65,535 characters Actual length in bytes of String value(Len) + 2 bytes, where Len < 216
BLOB Holds up to 65,535 bytes of data, and is used for Binary Large Objects. Actual length in bytes of String value(Len) + 2 bytes, where Len < 216
MEDIUMTEXT Allows a string with a maximum length of 16,777,215 characters Actual length in bytes of String value(Len) + 3 bytes, where Len < 224
MEDIUMBLOB Holds up to 16,777,215 bytes of data, and is used for Binary Large Objects. Actual length in bytes of String value(Len) + 3 bytes, where Len < 224
LONGTEXT Allows a string with a maximum length of 4,294,967,295 characters Actual length in bytes of String value(Len) + 4 bytes, where Len < 232
LONGBLOB Holds up to 4,294,967,295 bytes of data, and is used for Binary Large Objects. Actual length in bytes of String value(Len) + 4 bytes, where Len < 232
ENUM(x,y,z,etc.) Allows you to enter a list of possible values, with the maximum to be 65535 values. Just in case a value is inserted which is not present in the list, a blank value will be inserted. 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET This data type is similar to ENUM, but SET can have up to 64 list items and can store more than one choice. 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

Date and Time Types

This data type enables us to mention the date and time.

Data Type Description Storage Required Before MySQL 5.6.4 Storage Required as of MySQL 5.6.4
YEAR() Holds the value of year either in a two digit or in a four-digit format. Year values in the range (70-99) are converted to (1970-1999), and year values in the range (00-69) are converted to (2000-2069) 1 byte 1 byte
DATE() Holds the date values in the format: YYYY-MM-DD, where the supported range is (1000-01-01) to (9999-12-31) 3 bytes 3 bytes
TIME() Holds the time values in the format: HH:MI:SS, where the supported range is (-838:59:59) to (838:59:59) 3 bytes 3 bytes + fractional seconds storage
DATETIME() A combination of date and time values in the format: YYYY-MM-DD HH:MI:SS, where the supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ 8 bytes 5 bytes + fractional seconds storage
TIMESTAMP() Holds values which are stored as the number of seconds, with the format (YYYY-MM-DD HH:MI: SS). The supported range is from (1970-01-01 00:00:01) UTC to (2038-01-09 03:14:07) UTC 4 bytes 4 bytes + fractional second storage


Outside MYSQL - In other SQL versions

If you want to implement the code written by other vendors in SQL, then, MySQL facilitates this by mapping data types:

Other Vendor Type MySQL Type
BOOL TINYINT
BOOLEAN TINYINT
CHARACTER VARYING(M) VARCHAR(M)
FIXED DECIMAL
FLOAT4 FLOAT
FLOAT8 DOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY
MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
LONG MEDIUMTEXT
MIDDLEINT MEDIUMINT
NUMERIC DECIMAL