From the course: MySQL Essential Training (2019)

What are data types?

- [Instructor] Data types are how your database system stores and represents different types of data internally. The data types that are available on one database management system are pretty much guaranteed to be different than the data types available on another database management system. MySQL supports three fundamental types of data. Numeric types are used for representing numerical values. String types are used for representing both textual and non-textual strings. Date and time types are used for representing dates, times, and other temporal values. And MySQL also supports a few specialty types including Boolean, enumerations, and sets. Integer types come in various sizes. They have a fixed number of digits and they have no fractional part. Fixed point types are used to represent precise values. They have a fixed decimal point so their scale is known and they're commonly used for financial and other applications where accuracy is more important than the ability to represent very large or very small values. Floating point types are used to represent real numbers. That is, numbers with a fractional part that may be very large or very small. Floating point numbers sacrifice precision for scale. They are not completely accurate representations of value. Floating point numbers are used for applications where accuracy is not as important as the ability to represent very large or very small values. Fixed length character strings are used for strings with a known length like postal codes and form labels. Fixed length strings are padded with spaces for storage and trailing spaces are stripped when the strings are retrieved. Variable length strings are used where you cannot predict the exact length of strings, but you still want to conserve storage space. Trailing spaces are not stripped from variable length strings. Binary strings are available in both fixed and variable length varieties. They are not treated as text and have no character set and collation is according to binary value. Fixed length binary strings are padded with zeros. Large object storages provide in both binary and text varieties. BLOB stands for binary large object. It's typically used for storing images, audio and other binary media. TEXT is like BLOB, but is treated as non-binary, has a character set, and collation. Date and time types are provided for storing temporal values. They're stored in standard SQL format to support efficient sorting and searching. Time zone support is also available. Be aware that the timestamp type stores dates as a 32-bit epoch and is limited to dates before 19th of January, the year 2038. The date/time type stores dates as a four-digit year and supports dates up to year 9999. Enumerations and sets are specialty types that operate from lists of strings. The enumeration type provides a single value from a list of possible values. The set type provides a set of several values from a list. Enumerations are stored as a small integer representing a position in a list. Sets are stored as bitmaps where each bit represents a position in a list. MySQL imposes hard limits on the number of columns per table and number of bytes per row. A table my have up to 4,096 columns and a row may be up to 65,535 bytes. This means that the maximum size of all the columns in a table must not exceed 65,535 bytes, including metadata. This includes the length bytes of variable length strings. The maximum allowable size of variable byte size character sets, like UTF-8, and any other overhead permitted by the various data types used in your column definitions. BLOB and TEXT columns count as nine to 12 bytes each as they store their data separately. MySQL provides a rich set of data types. In the rest of this chapter, I'll cover some of the details of the major data types available in MySQL.

Contents