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:
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:
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:
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:
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:
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:
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.
blog comments powered by Disqus