4.3: MySQL Data Types
- Page ID
- 3311
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)
The next few pages will likely be a little dry. Apologies now. However, whenever we create a table structure in MySQL we must identify the data type we intend to store in any given column, and depending on the type of data and other features we may want, this is just the beginning. Since one of our goals in the relational approach to database design is reducing overall size, it is also important to consider the best fit data type for what we want to store. Familiarizing yourself with the types available in MySQL will lend to your ability to design efficient table structures. The tables below are adopted from http://www.w3resource.com/mysql/mysql-data-types.php. They have been trimmed down in an attempt to not introduce an overwhelming amount of detail. You are encouraged to review the original version for more depth.
| Integral Type | |||
| Type | Length in Bytes | Minimum Value (Signed/Unsigned) | Maximum Value (Signed/Unsigned) |
| TINYINT | 1 | -128 / 0 | 127 / 255 |
| SMALLINT | 2 | -32768 / 0 | 32767 / 65535 |
| MEDIUMINT | 3 | -8388608 / 0 | 8388607 / 16777215 |
| INT | 4 | -2147483648 / 0 | 2147483647 / 4294967295 |
| BIGINT | 8 | -9223372036854775808 / 0 | 9223372036854775807 / 18446744073709551615 |
| Floating-Point Types | |||
| Type | Description | ||
| FLOAT | A precision from 0 to 23 results in a four-byte single-precision FLOAT column. | ||
| DOUBLE | A precision from 24 to 53 results in an eight-byte double-precision DOUBLE column. | ||
| Fixed-Point Types | |||
| Type | Description | ||
| DECIMAL | In the format DECIMAL(precision,scale). Maximum number of digits allowed are 65 before MySQL 5.03 and 64 after 5.03. | ||
| NUMERIC | Same as DECIMAL. | ||
| Bit Value Types | |||
| Type | Description | ||
| BIT |
In the format b BIT(N), where N is an integer. |
||
| Numeric Type Attributes | |||
| Type | Description | ||
| TYPE(N) | Where N is an integer and display width of the type is up to N digits. | ||
| ZEROFILL | The default padding of spaces is replaced with zeroes. So, for a column INT(3) ZEROFILL, 7 is displayed as 007. | ||
| DATETIME, DATE, and TIMESTAMP Types | |||
| Type | Description | Display Format | Range |
| DATETIME | Use when you need values containing both date and time information. | YYYY-MM-DD HH:MM:SS | ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. |
| DATE | Use when you need only date information. | YYYY-MM-DD | ‘1000-01-01’ to ‘9999-12-31’. |
| TIMESTAMP | Values are converted from the current time zone to UTC while storing, and converted back from UTC to the current time zone when retrieved. | YYYY-MM-DD HH:MM:SS | ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. |
| String Types | |||
| Type | Description | ||
| CHAR | Contains non-binary strings. Length is fixed as you declare while creating a table. When stored, they are right-padded with spaces to the specified length. |
||
| VARCHAR | Contains non-binary strings. Columns are variable-length strings. | ||
| BINARY and VARBINARY Types | |||
| Type | Description | Range in bytes | |
| BINARY | Contains binary strings. | 0 to 25 | |
| VARBINARY | Contains binary strings. | A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. | |
| BLOB and TEXT Types | |||
| Type | Description | Categories | Range |
| BLOB | Large binary object that containing a variable amount of data. Values are treated as binary strings. You do not need to specify length while creating a column. | TINYBLOB | Maximum length of 255 characters. |
| MEDIUMBLOB | Maximum length of 16777215 characters. | ||
| LONGBLOB | Maximum length of 4294967295 characters. | ||
| TEXT | Values are treated as character strings having a character set. | TINYBLOB | Maximum length of 255 characters. |
| MEDIUMBLOB | Maximum length of 16777215 characters. | ||
| LONGBLOB | Maximum length of 4294967295 characters. | ||
| ENUM Types | |||
| Type | Description | Example | |
| ENUM | A string object whose value is chosen from a list of values given at the time of table creation. |
ENUM('small', 'medium', 'large')
|
|
| SET Types | |||
| Type | Description | Range | |
| SET | A string object having zero or more comma separated values. Values are chosen from a list of values given at the time of table creation. | Maximum 64 values. | |