Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Data types are how your database system stores and represents different types of data internally. The data types that are available in one database management system are pretty much guaranteed 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 text and non-text strings. Date and time types are used for representing dates, times and other temporal values.
MySQL also supports 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 skill is known. 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.
Number 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 patterned 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're 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 objects storage is provided in both binary and TEXT varieties. BLOB stands for binary large object.
It is typically used for storing images on video and other binary media. TEXT is like a BLOB but it is treated as non binary and 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. Support for automatic time and date stamps is supported. Timezone support is also provided. 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. And sets are stored as bitmaps, where each bit represents a position, in the list. As we consider the storage requirements for the various data types in the rest of this chapter. It's important to understand that MySQL imposes a hard limit, on the size of any one row in a table.
The maximum size of all the columns in a table must not exceed 65,535 bytes. Keep in mind that this limit is imposed as the table is defined, not as it is populated. So the maximum size of every column definition in a table cannot exceed this limit. This includes length bytes of variable length strings. The maximum allowable size of variable byte size character sets like UTF8. And any other overhead permitted by the various data types used in your column definitions.
MySQL provides a rich set of data types. In the rest of this chapter, I'll cover the details of the major data types available in MySQL.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 101483 Viewers
61 Video lessons · 88250 Viewers
71 Video lessons · 72117 Viewers
56 Video lessons · 103881 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.