Data types define the type of value that can be stored in a column. To decide which data type a column should be, you need to know what kind of data will be stored in that column. In MySQL, there are three main data types: string, numeric, and datetime.
Each data type has different characteristics based on the amount of space it takes up, the kind of values that can be stored, whether the values can be indexed, and how the values are compared to each other. It's best to choose the most precise type to optimize storage.
For example, don't store the first name in a column with varchar(max), but instead store the first name in a column that would accommodate a long first name, such as varchar(20); this way, you use much less storage for your first-name column. It's also vital to use numeric data types for numerical values and datetime data types for datetime values so that you don't have to convert these values from strings if you store numerical or datetime values in a string instead. Data types are covered in more detail in Chapter 3, Understanding Data Types.
Here are some reasons why you need to choose wisely:
- If you choose a data type that is too large for the data that it will hold, it will cause extra stress for your database because you will be using additional storage. The less storage you use, the more data you can have in memory (RAM). This will increase your database's performance.
- If you choose a data type that is too small for the data it will hold, this will cause your data to be truncated have failures upon insertion because the data type won't allow those sizes of data to be inserted.
The following table takes you through some examples of how to choose data types. It also helps you understand the reasoning behind why you would choose each data type:
| Value(s) or type of data | Type in MySQL |
| State abbreviations that are always two letters, such as CA, CO—we would use CHAR here instead of VARCHAR because these values will always be the same length | CHAR(2) |
| State names such as California or Colorado—we would use VARCHAR here because there is a variable length, and we would set the VARCHAR to the longest length string, which in this case would be South or North Carolina. | VARCHAR(14) |
| Primary key, autoincremented column for a table. | Unsigned INT with auto_increment |
| Large amounts of text—consider putting TEXT columns in a separate table to optimize table performance. Database and table design will be covered in more detail in Chapter 4, Designing and Creating a Database. | TEXT |
| Storing files, including images—for the most part, you should use the filesystem for what it was intended for—storing files—and you should not store them in the database. If you do store them in the database, then store them in a separate table to avoid performance problems. | BLOB |
| Enumerated and set values—you should avoid these data types because if you ever decide to add something else to the ENUM or SET declaration, then MySQL will have to rebuild the table, and if you have a lot of rows, this could be very time consuming. In addition, developers can use logic on the application side to handle this much better than a MySQL table can. | ENUM or SET |
| Storing 0 and 1 values, such as whether a value is true or false. | BIT |
| Storing zip codes (such as 11155) | TINYINT |
| Storing money values (such as $115.25) | DECIMAL |
| Social security numbers (123-45-6789)—these are numbers, but you won't be doing calculations on them, and you may want to store the hyphens for proper formatting. | VARCHAR |
| Dates with time—don't use string types to store dates. | DATETIME |
| Scientific data or data where you don't need exact precision |
Float or Double |
The preceding table took you through some examples of how to choose data types. It also helped you understand the reason for selecting each data type.