From the course: Learning phpMyAdmin

Choosing the right column types

From the course: Learning phpMyAdmin

Start my 1-month free trial

Choosing the right column types

“

- Like most database systems, MySQL expects you to specify the type of data to be stored in each column of a table. MySQL supports a wide range of data types. There are nearly 40 of them. They can be divided into the following groups. Numeric data types are used for storing numbers that can be used in calculations, such as ages, prices, and so on. But you don't use them for phone numbers or zip codes, because they often contain other characters, so should be stored as strings. MySQL considers both text and binary data to be strings, and has different data types to handle them. There are specialized data types for date and time values, and MySQL is also capable of storing spatial values. This is a highly specialized subject, so I'll deal only with numeric, string, and date and time. And I won't cover every single type, just the most important ones. When storing numbers, you need to make a distinction between integers, or whole numbers, and numbers with a decimal fraction. The INT data type is for integers. If you try to store a decimal fraction in this type of column, it'll be automatically rounded to the nearest whole number. INT is capable of storing numbers in a range from approximately -2 billion, to +2 billion. If you specify the column as being unsigned, only positive values are allowed. This means INT unsigned can store numbers from zero to about 4.2 billion. For some values, such as storing a person's age, using TINYINT is more appropriate. It stores a much smaller range of numbers, but it uses only a quarter of the disk space required by INT. There are three other data types for integers: SMALLINT, MEDIUMINT, and BIGINT, capable of storing different ranges of numbers. The bigger the range, the more disk space is required. In a small database, it's probably okay to use INT for all integers, but if you have thousands of records, choosing the most appropriate data type can make a significant difference to the size of the database. To store numbers with a decimal fraction, your choice of data type depends on the level of accuracy and the range of values you require. FLOAT or DOUBLE handle very large numbers, but are subject to rounding errors, due to the limitations of computers handling floating point numbers. Where exact values are required, such as in currency or dimensions, use DECIMAL. It handles a smaller range of values than FLOAT or DOUBLE, but is more than adequate, even for very large amounts of currency. An unusual feature about a DECIMAL column is that it defaults to a maximum of ten digits with no decimal fraction. When defining the column, you need to specify the maximum number of digits permitted, followed by a comma, and the number of decimal places you want. Now, let's look at string data types for text and binary data. VARCHAR is used for short, variable length text, such as names, titles, and so on. You need to specify the maximum number of characters permitted in a VARCHAR field. CHAR is for text values that are always the same length. For example, if you encrypt passwords with the PHP password hashing function, at the time of recording this video, they'll always be 60 characters. For longer text items, use the TEXT data type, which is capable of storing the equivalent of about 20 to 30 pages of a word document. VARCHAR and TEXT columns take up only as much space as needed to store that content. CHAR columns, on the other hand, always take up the full amount of allocated space, even if you store shorter text in them. The ENUM data type works like a radio button group in HTML. It stores a single value from a pre-defined list. It's very useful for recording gender, marital status, and similar cases where only one value is appropriate. It's also very efficient, because MySQL stores the selected value internally as a number, but treats it as text in queries. The charmingly named BLOB data type can store up to 64 kilobytes of binary data, such as images, sound files, and compressed files. However, such items tend to bloat the database, and lead to fragmented tables. It's much better to store the file name in the database, and store the file itself in the main file system. BLOB, by the way, stands for Binary Large Object. The five date and time data types are fairly self-explanatory. The main thing to notice is the format. DATE stores only the date, TIME stores only the time, and DATETIME stores both together. The DATE must always be in the order laid down by the ISO, The International Organization for Standardization. Year, month, date. You can't change it to the American convention of month, date, year, or to the European convention of date, month, year. Two other points to note. A MySQL TIMESTAMP is in the same format as DATETIME. It's not compatible with a PHP TIMESTAMP, which measures the number of seconds since midnight at the beginning of New Year's Day, 1970. Also, the YEAR data type now expects a four digit value. Older versions of MySQL accepted two digit years. If you're in doubt as which data type to use, the six data types listed here will serve you well for most purposes. INT, TINYINT, and DECIMAL for numbers, VARCHAR and TEXT for different lengths of text, and for dates, DATETIME is the most versatile. If you don't include a time with the date, it defaults to midnight.

Contents