Section 4: Persistent Data Storage

Chapter 39: MySQL Data Types

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.

Table 14 MySQL Data Types

Integer Types

Type

Length in Bytes

Minimum Value(Signed/Unsigned)

Maximum Value(Signed/Unsigned)

TINYINT

1

-128 to 0

127 to 255

SMALLINT

2

-32768 to 0

32767 to 65535

MEDIUMINT

3

-8388608 to 0

8388607 to 16777215

INT

4

-2147483648 to 0

2147483647 to 4294967295

Floating-Point Types

Types

Description

FLOAT

A precision from 0 to 23 results in a four-byte single-precision FLOAT column.

Fixed-Point Types

Types

Description

DECIMAL

In the format DECIMAL(precision,scale). Maximum number of digits allowed are 65 before MySQL 5.03 and 64 after 5.03.

Bit Value Types

Types

Description

Numeric type Attributes

Types

Description

TYPE(N)

Where N is an integer and display width of the type is up to N digits.

DATETIME, DATE, and TIMESTAMP Types

Types

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

Types

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.

BINARY and VARBINARY Types

Types

Description

Range in bytes

BINARY

Contains binary strings.

0 to 25.

BLOB and TEXT Types

Types

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.

TINYBLOB

Maximum length of 255 characters.

MEDIUMBLOB

Maximum length of 16777215 characters.

ENUM Types

A string object whose value is chosen from a list of values given at the time of table creation. For example:

  1. ENUM(‘small’, ‘medium’, ‘large’)

SET Types

A string object having zero or more comma separated values (maximum 64). Values are chosen from a list of values given at the time of table creation.

License

Icon for the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License

Chapter 39: MySQL Data Types by Michael Mendez is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License, except where otherwise noted.