MySQL Data and Table Types

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.

Numeric

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.

String

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

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:

• GEOMETRYCOLLECTION
• MULTIPOINT 
• MULTILINESTRING 
• MULTIPOLYGON
  

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.