How to Store Different Types of MySQL Data - dummies

By Steve Suehring, Janet Valade

MySQL stores information in different formats, based on the type of information that you tell MySQL to expect. MySQL allows different types of data to be used in different ways. The main types of data are character, numerical, and date and time data.

Character data

The most common type of data is character data (data that’s 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, and pet description. You can move and print character data.

Two character strings can be put together (concatenated), a substring 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 aren’t 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 aren’t stored.

If a character string length varies only a little, use the fixed-length format. For example, a length of ten works for all ZIP codes, including those with the ZIP+4 number. If the ZIP code doesn’t include the ZIP+4 number, only five spaces are left empty.

However, if your character string can vary more than a few characters, use a variable-length format to save space. For example, your pet description might be small bat, or it might run to several lines of description. By storing this description in a variable-length format, you only use the necessary space.

Numerical data

Another common type of data is numerical data — data that’s 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. No conversion is required.

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.

You might find auto-increment columns useful when you need unique numbers, such as a product number or an order number.

Date and time data

A third common type of data is 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.

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 doesn’t store any other values in that column.

MySQL data type names

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. Table 3-3 shows the MySQL data types used most often in web database applications.

MySQL Data Types
MySQL Data Type Description
CHAR(length) Fixed-length character string.
VARCHAR(length) Variable-length character string. The longest string that can
be stored is length, which must be between 1 and
TEXT Variable-length character string with a maximum length of 64K
of text.
INT(length) Integer with a range from –2147483648 to +2147483647. The
number that can be displayed is limited by length. For
example, if length is 4, only numbers from –999 to
9999 can be displayed, even though higher numbers are stored.
INT(length) UNSIGNED Integer with a range from 0 to 4294967295. length is the
size of the number that can be displayed. For example, if
length is 4, only numbers from 0 to 9999 can be displayed,
even though higher numbers are stored.
BIGINT A large integer. The signed range is –9223372036854775808
to 9223372036854775807. The unsigned range is 0 to
DECIMAL(length,dec) Decimal number in which length is the number of
characters that can be used to display the number, including
decimal points, signs, and exponents, and dec is the maximum
number of decimal places allowed. For example, 12.34 has a
length of 5 and a dec of 2.
DATE Date value with year, month, and date. Displays the value as
YYYY-MM-DD (for example, 2013-04-03 for April 3, 2013).
TIME Time value with hour, minute, and second. Displays as
DATETIME Date and time are stored together. Displays as YYYY-MM-DD
ENUM (“val1”,“val2”…) Only the values listed can be stored. A maximum of 65,535
values can be listed.

MySQL allows many data types other than those listed, but you probably need those other data types less frequently.