How to Store Different Types of MySQL Data
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.
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.
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.
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 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 255.|
|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 18446744073709551615.|
|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 HH:MM:SS.|
|DATETIME||Date and time are stored together. Displays as YYYY-MM-DD HH:MM:SS.|
|ENUM (val1,val2...)||Only the values listed can be stored. A maximum of 65,535 values can be listed.|
|SERIAL||A shortcut name for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT.|
MySQL allows many data types other than those listed, but you probably need those other data types less frequently.