Once you have identified all of the tables and columns that the database will need, you should determine the fields’ MySQL data types.
MySQL data types
When creating a database, MySQL requires that the user defines what sort of information each field will contain. There are three primary categories of MySQL data types, and these are common to almost every database software:
- Dates and times.
Within each of these there are a number of variants, some of which are MySQL-specific, that you can use. Choosing the column types will not only dictate what information can be stored and how, but will also affect the overall performance of the database.
Many MySQL data types can take an optional Length attribute, limiting their size (the square brackets, , indicate an optional parameter to be put in parentheses, while parentheses themselves indicate required arguments).
Another detail is that the number types can be UNSIGNED, limiting the column to positive numbers or zero, or be defined as ZEROFILL, meaning that any extra room will be filled with zeroes (ZEROFILLs are also automatically UNSIGNED).
The different date types also have some unique characteristics. The DATE and TIME fields are mostly used without modification, but there are many other options available. There are also two extensions of the TEXT types that result in a different behavior: ENUM and SET.
These allow the user to define a series of acceptable values when creating a table. An ENUM field can only have one of a possible several thousand values, while SET allows for several of up to 64 possible values. ENUM and SET are specific MySQL data types so they will not be supported by other databases.