What are MySQL Data Types and How to Choose Them?

All databases come with a variety of database data types you can employ to assist your use case.
MySQL is no exception and MySQL data types are a thing too.

This mysql tutorial will tell you that the data you see in an application most likely comes from an SQL database or a NoSQL database. This MySQL tutorial will remind you that data in a relational database is inserted, modified, updated or deleted by using SQL queries and those SQL queries act on columns that hold data of a specific data type inside of them.

MySQL provides support for MySQL data types in several categories: the database management system provides support for numeric MySQL data types, date and time MySQL data types, string data types, spatial MySQL data types, as well as the JSON MySQL data type. As such, data types are a big aprt of your mysql database design strategy.

All of those MySQL data types are there for a reason and all of them have subcategories allowing you to make a well-informed decision.

Data types can be defined when creating a table, or modified once the table is already created.
Numeric data types only accept the storage of numbers, date and time data types are optimized for date and time values, and string data types can be used to store text, or in other words, string values.
Spatial data types are intended for geometric data, and the JSON data type is intended to store JSON values.

For the best results, evaluate all of the possible options and choose a data type that matches the type of data you intend to store.

Selecting specific data types is also important because otherwise, you may have issues with functions interacting with your data and you may get inconsistent results as a consequence.

Thus, to properly choose a data type, first think about your use case, choose MySQL data types applicable to you, and only then define the table structure. In other words, before choosing a data type, think about what data will you be working with. That will be a good starting point.

For an example, maybe you're building a search engine that lets people search and buy used cars.
Your table structure would likely include an id column, a car manufacturer, the year the car was made, the mileage related to the car, and whether it's been in any accidents.

With that in mind, you have a lot of information to assist in building your table structure and choosing data types of interest.

The id column would necessitate an integer data type because the only thing we're going to store in it are numbers, same with the year and the mileage. The car manufacturer would necessitate the existence of a string-based field, for performance and storage reasons it most likely being either CHAR or a VARCHAR.

When defining MySQL data types, also be wary of their length. The id field would do with a length of 4 or 5 characters if your application isn't very big, the year field would most likely necessitate only 4 characters, mileage could necessitate up to 7, and the car manufacturer could do with up to 20 or 30.

The length of MySQL data types has a direct impact on the amount of space those data types occupy on the disk.

The last thing to keep in mind is that MySQL data types or their length are not set in stone and they can be easily changed even if your data consists of billions of rows.

One can change MySQL data types by using SQL clients or by simply running an SQL query that alters the table structure.
The outcome will be the same, it's just that SQL clients will generate and run SQL queries for you instead of letting you do it yourself.

Hopefully this video on MySQL data types was educational and useful, comment below if you have any questions, and I'll see you in the next one. Bye for now.

Music:
Kokua by Luke Bergs: soundcloud.com/bergscloud
Creative Commons — Attribution-ShareAlike 3.0 Unported — CC BY-SA 3.0
Free Download / Stream: www.audiolibrary.com.co/luke-bergs/kokua
Music promoted by Audio Library:    • Upbeat Acoustic Pop by Luke Bergs (No Copy...  

Mellow - Mehul Choudhary: soundcloud.com/mehulchoudhary
Creative Commons — Attribution 3.0 Unported — CC BY 3.0
Free Download / Stream: www.audiolibrary.com.co/mehul-choudhary/mellow
Music promoted by Audio Library:    • Electronic Energetic Bass by Mehul Choudha...  

#mysql #database #sql #development #developer #webdevelopment