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.
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.
2. Numerical 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. If you don’t plan to use data for numerical operations, however, you should store it as a character string because the programmer will be using it as a character string.
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.
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.
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.
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. Maximum length for each type is:
- TINYTEXT: 28-1 = 255 bytes
- TEXT: 216-1 bytes = 65,535 bytes = 64 KiB
- MEDIUMTEXT: 224-1 bytes = 16,777,215 bytes = 16 MiB
- LONGTEXT: 232-1 bytes = 4,294,967,295 bytes = 4 GiB
- TINYINT: 1 byte, -128 to +127 / 0 to 255 (unsigned)
- SMALLINT: 2 bytes, -32,768 to +32,767 / 0 to 65,535 (unsigned)
- MEDIUMINT: 3 bytes, -8,388,608 to 8,388,607 / 0 to 16,777,215 (unsigned)
- INT: 4 bytes, -2,147,483,648 to +2,147,483,647 / 0 to 4,294,967,295 (unsigned)
- BIGINT: 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 / 0 to 18,446,744,073,709,551,615 (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