Today’s MySQL database tutorial will cover MySQL data and table types. Both of these topics are essential to anyone jumping into the world of the MySQL and give you a nice little foundation to build on as you hone your skills.
Before we jump into the specifics, we should look at why we should go over the topics of MySQL data and table types first. In terms of MySQL data types, knowing how to differentiate between them will help you implement them effectively into your future designs of database tables. As for MySQL table types, knowing how to manipulate all of their variations will come in handy when you want to get the most out of your application’s performance.
So, now that you have a bit of an introduction to the importance of MySQL data and table types, it is time to break down each topic even further. Let us take the logical route of explaining MySQL data types first.
MySQL Data Types
When using MySQL, you will notice that each database table is characterized by numerous columns. Each of these columns accommodates certain data types that differ from one another in four main ways. First, you must consider the type of value that data type represents. Second, are the data type’s values able to be indexed? Third, are the values fixed in their length, or do they have variable lengths, and how much space do they occupy? Lastly, you must consider how MySQL performs a comparison of the values contained within each of the data types. Using all of these criteria divides data types into their own little families, and we will list examples of the different MySQL data types you will find right now.
If you have used Microsoft’s SQL Server product in the past, you will probably notice that the numeric data types found within MySQL are similar. These numeric data types in MySQL include the conventional integer, fixed-point, floating-point, and bit.
Integer numeric data types and fixed-point numeric data types are considered to be exact values. Exact values are important when you want to store data where exact precision is necessary, such as monetary values. Here are some examples of numeric data types that are exact values:
TINYINT – an integer that is tiny or very small in size
SMALLINT – an integer that is small in size
MEDIUMINT – an integer that is medium in size
INT – a integer that is standard in size
BIGINT – an integer that is big or large in size
DECIMAL – a number of fixed-point status
On the flip side, we have floating-point numeric data types. Unlike their exactly value brethren, these represent approximate numeric values. Examples of floating-point numeric data types include the following:
FLOAT – a floating-point number that is single-precision in nature
DOUBLE – a floating-point number that is double-precision in nature
Last, but not least, we have the BIT numeric data type, which helps us store bit field values.
You could say that string data types in MySQL are less restrictive and more flexible than numeric data types. Why? Not only can they store simplistic data such as plain text, but they can also store binary data items in the form of images, files, and the like. It is also worth noting that with string data types in MySQL, you have the ability to use the LIKE clause or regular expression to perform searches or comparisons.
Here is a breakdown of the different string data types in MySQL:
CHAR – a character string that is non-binary and has a fixed length
VARCHAR – a character string that is non-binary and has a variable length
BINARY – a binary string that has a fixed lengthy
VARBINARY – a binary string that has a variable length
TINYBLOB – a binary large object that is tiny or very small in size
BLOB – a binary large object that is small in size
MEDIUMBLOB – a binary large object that is medium in size
LONGBLOB – a binary large object that is large in size
TINYTEXT – a non-binary string that is tiny or very small in size
TEXT – a non-binary string that is small in size
MEDIUMTEXT – a non-binary string that is medium in size
LONGTEXT – a non-binary string that is large in size
ENUM – a string object with a value chosen from a list of pre-defined values that are enumerated when the table is created
SET – a string object that has zero or more values that are chosen from a list of pre-defined values that are specified when the table is created
Spatial data types in MySQL deal with values that are geometrical or geographical in nature. The following spatial data types in MySQL hold single geometry values:
GEOMETRY – any type of spatial value
POINT – a point represented by X and Y coordinates
LINESTRING – one or more POINT values that make up a curve
POLYGON – a polygon
The following spatial data types hold collections of values of the data types above:
Date and Time
These MySQL data types deal with different variations for storing date and time data. Here they are:
DATE – a value for the date in the 'YYYY-MM-DD' format
DATETIME – a value for the date and time in the 'YYYY-MM-DD HH:MM:SS' format
TIMESTAMP - a value for the timestamp including the date and time in the 'YYYY-MM-DD HH:MM:SS' format
TIME – a value for the time in the 'HH:MM:SS' format
YEAR – a value for the year in the YYYY or YY format
MySQL Table Types
Now that you have had a quick overview on MySQL data types, it is time to move on to MySQL table types. They are also referred to as storage engines. Here are some of the table types available in MySQL:
InnoDB – this storage engine is transaction-safe and is good for general purposes and offers such benefits as high reliability and high performance, among others. InnoDB data files are portable, allowing you to move them among systems
MyISAM – based on the now defunct ISAM storage engine, MyISAM has several useful extensions. It is not transaction-safe. When you create a table, MyISAM is the default table type. MyISAM data files are portable, similar to InnoDB.
MERGE – this storage engine is sometimes referred to as MRG_MyISAM. It is basically a collection of MyISAM tables that are identical in terms of their column and index information and can be used as one. This is useful in the way that it essentially eliminates size limitations associated with MyISAM tables.
These are just some of the table types or storage engines in MySQL. This concludes this brief overview on MySQL data and table types. Keep checking back with us for more in the future.