Data Types in MySQL

MySQL stores information in different formats. It allows different types of data to be used in different ways. The main types of data are character, numerical, and date and time. When you create a database, you tell MySQL what kind of data to expect in a particular column by using the MySQL names for data types.

images/articles/mysql/data-types-in-mysql.jpg

1. Character Data

The most common type of data is character data (data that is stored as strings of characters), and it can be manipulated only in strings. Most of the information that you store is character data. For example, customer name, address, phone number, description, and so on. You can move and print character data. Two character strings can be put together (concatenated), a sub string can be selected from a longer string, and one string can be substituted for another.

Character data can be stored in a fixed-length or variable-length format.

Fixed Length Format

In this format, MySQL reserves a fixed space for the data. If the data is longer than the fixed length, only the characters that fit are stored. The remaining characters on the end are not stored. If the string is shorter than the fixed length, the extra spaces are left empty and wasted.

Variable Length Format

In this format, MySQL stores the string in a field that’s the same length as the string. You specify a string length, but if the string itself is shorter than the specified length, MySQL uses only the space required, instead of leaving the extra space empty. If the string is longer than the space specified, the extra characters are not stored.

Which one to use? If a character string length varies only a little, use the fixed-length format. For example, zip code or phone number. However, if character string can vary more than a few characters, use a variable-length format to save space. For example, description.

CHAR

The CHAR data type offers MySQL’s fixed-length string representation, supporting a maximum length of 255 characters.

VARCHAR

The VARCHAR data type is MySQL’s variable-length string representation, supporting a length of 0 to 65,535 characters.

LONGTEXT

The LONGTEXT data type is MySQL’s largest nonbinary string representation, supporting a maximum length of 4,294,967,295 characters. 

MEDIUMTEXT

The MEDIUMTEXT data type is MySQL’s second-largest nonbinary text string, capable of storing a maximum length of 16,777,215 characters.

TEXT

The TEXT data type is MySQL’s third-largest nonbinary string representation, supporting a maximum length of 65,535 characters.

TINYTEXT

The TINYTEXT data type is MySQL’s smallest nonbinary string representation, supporting a maximum length of 255 characters.

ENUM

The ENUM data type provides a means for storing a maximum of one member chosen from a predefined group consisting of a maximum of 65,535 distinct members.

2. Numeric Data

Numerical data is stored as a number. You can store decimal numbers (for example, 10.5, 2.34567, 23456.7) as well as integers (for example, 1, 2, 248). When you store data as a number, you can use that data in numerical operations, such as adding, subtracting, and squaring.

Positive and Negative Numbers

MySQL stores positive and negative numbers, but you can tell MySQL to store only positive numbers. If your data is never negative, store the data as UNSIGNED (without a + or – sign before the number). For example, a city population or the number of pages in a document can never be negative.

Auto Increment

MySQL provides a specific type of numeric column called an auto-increment column. This type of column is automatically filled with a sequential number if no specific number is provided. For example, when a table row is added with 5 in the auto-increment column, the next row is automatically assigned 6 in that column unless a different number is specified. Auto-increment columns are useful when you need unique numbers, such as a product number or an order number.

BOOL, BOOLEAN

BOOL and BOOLEAN are just aliases for TINYINT(1), intended for assignments of either 0 or 1.

BIGINT

The BIGINT data type offers MySQL’s largest integer range, supporting a signed range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 and an unsigned range of 0 to 18,446,744,073,709,551,615.

INT

The INT data type offers MySQL’s second-largest integer range, supporting a signed range of -2,147,483,648 to 2,147,483,647 and an unsigned range of 0 to 4,294,967,295.

MEDIUMINT

The MEDIUMINT data type offers MySQL’s third-largest integer range, supporting a signed range of -8,388,608 to 8,388,607 and an unsigned range of 0 to 16,777,215.

SMALLINT

The SMALLINT data type offers MySQL’s fourth-largest integer range, supporting a signed range of -32,768 to 32,767 and an unsigned range of 0 to 65,535.

TINYINT

The TINYINT data type is MySQL’s smallest integer range, supporting a signed range of -128 to 127 and an unsigned range of 0 to 255.

3. Date and Time Data

Data stored as a date can be displayed in a variety of date formats. You can use that data to determine the length of time between two dates or two times or between a specific date or time and some arbitrary date or time.

DATE

The DATE data type is responsible for storing date information. The MySQL displays DATE values in a standard YYYY-MM-DD format.

DATETIME

The DATETIME data type is responsible for storing a combination of date and time information. Like DATE, DATETIME values are stored in a standard format, YYYY-MM-DD HH:MM:SS

TIME

The TIME data type is responsible for storing time information and supports a range large enough not only to represent both standard and military-style time formats, but also to represent extended time intervals.

TIMESTAMP

The TIMESTAMP data type differs from DATETIME in that MySQL's default behavior is to automatically update it to the current date and time whenever an INSERT or UPDATE operation affecting it is executed.

YEAR

The YEAR data type is responsible for storing year-specific information, in either two-digit or four-digit format.

4. Enumeration Data

Sometimes, data can have only a limited number of values. For example, the only possible values for a column might be yes or no. MySQL provides a data type called enumeration for use with this type of data. You tell MySQL what values can be stored in the column (for example, yes and no), and MySQL does not store any other values in that column.

Data Type Names

CHAR(length): Fixed-length character string

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255.

VARCHAR(length): Variable-length character string

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.

TEXT: Variable-length character string with a maximum length of 64K of text.

The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

INT(length): Integer

  • TINYINT: 1 byte
  • SMALLINT: 2 bytes
  • MEDIUMINT: 3 bytes
  • INT: 4 bytes
  • BIGINT: 8 bytes

INT(length) UNSIGNED

BIGINT: A large integer

DECIMAL(length,dec): Decimal number

DATE: Date value with year, month, and date

TIME: Time value with hour, minute, and second

DATETIME: Date and time are stored together

SERIAL: A shortcut name for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT