Let’s do a quick overview of some basic data types in MySQL.

Strings

CHAR(size) and VARCHAR(size) could be thought of as the most commonly used data types for strings. CHAR and VARCHAR both take an argument, which will be become the length of the string that is stored in that field. CHAR creates a fixed length field with a maximum of 255 characters and VARCHAR could have a variable length with a 65,535 (so more than enough for most occasions).

If the occasion arises to insert a LOT of text into a field, there are other options, such as MEDIUMTEXT or LONGTEXT (that could store up to 4,294,967,295 characters).

A useful string datatype for enforcing rules is ENUM. ENUM could be followed by up to 65,535 argument values and the field can only have one of the fields at the value. For instance we could define a a field with ENUM(‘small’, ‘medium’, ‘large’), that field can only have a value between the three that has been given.

Numbers

INT and FLOAT are the most commonly used types. Similar to other programming languages, INT is for integers, or whole numbers, and floats are used if you would like to accommodate decimal points. BOL is used for Boolean values where zero is considered false and nonzero is true.

Date and Time

Let’s look at one more category of data type before we finish this topic. It’s date and time. There is the urge to quickly and conveniently use substitute data types for date and time, such as a string for a date, or an integer for years. But these judgement calls have to made in a thoughtful manner. Databases could quickly be filled up with content from users, systems and equipment. Cleaning-up data within a database or migrating data to a different database structure could be quite costly, especially once valuable (yet messy) data starts accumulating.

MySQL has a DATE data type that uses the format “YYYY-MM-DD”, for instance “2021-01-22.” There is also a more detailed DATETIME type that uses the format “YYYY-MM-DD hh:mm:ss”, and for just the time, there is the format TIME that uses the format “hh:mm:ss.”

For a quick example, let’s think of a database to store data from contact sensors. Storing and processing data from sensors or devices is a massive area of work, especially with the advent IOT (Internet of Things) and inexpensive sensors. It’s quite easy to purchase Wifi connected contact sensors that you could attach to your door, desk drawer, or fridge and get notifications and collect data on when it was opened or closed.

The above could be an example for a simple table to collect data for this sensor. You could see that it structures the date and time data, and only allows “close” and “open” for the “action” field. Try creating this table and explore what data it would accept or not accept.

You can watch a video of a brief walk through of this topic here.