Explanation of The Various Data Types in MySQL Based on User Requirements

Leave a comment

Not all data is the same! Through working with variables in PHP and JavaScript we know that variables can contain any kind of data. MySQL is no different in this respect. Various data types in MySQL affect the disk space used by the database, that it also affects performance.

If the data type used for the field is not optimized, it can lead to slow applications that cause the database to become a bottleneck. Now that you have a good introduction to MySQL, let’s take a closer look at data types in MySQL and take a look at the basics of how it works. You can start by looking at the schema tables in the information_schema database.

mysql> show columns from information_schema.schemata;

https://gist.github.com/82594314e5ef1730e9ad1e1495535d00

Here I want to focus on the second column called type. It just tells you what types of data will be stored here. The result is any varchar data type. You can also see that each field has a specific number associated with varchar. This tells you how many characters you can store. Let’s quickly define some data types.

Varcar

It’s true that very flexible for strings data type, especially if you’re not sure how long it will be. Names and addresses are often used for varchar data types, that can be up to 255 characters in 1 field.

Char

If you know how many characters you need, you can use the char data type. It is a fixed width data type. So if you only want to use 3 characters, you don’t need to allocate a 56 character char datatype. It’s best to use char only if the data being saved is of the same length. Accuracy is important here.

Text

Text data types are strings, just like varchar and char. It is a variable length data type that can be up to 4 gigabytes per field, and please use this data type carefully.

Character Sets and Bytes

This brings us to an interesting paradox. When defining a database field, you specify the length of characters that the field will contain. However, you cannot specify the number of bytes, so this is where the character set comes on.

  • latin1 – It is MySQL’s default character set and uses 1 byte of data per character to store in the database. note. If you need more than 1 byte of data as suggested by latin1, the data may appear as question marks in the database.
  • utf8 – On the other hand, I used up to 4 bytes per character. If you have an application that uses foreign languages or special character types, you might consider changing the character set to utf8.

MySQL text sizes

There are four ways to define a text box. These are TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. The type you choose depends on how much you want to store. In general, it’s a good idea to use the smallest possible data type to store your data while at the same time making your application work as expected. This is because overhead costs increase as capacity increases. The MySQL documentation has a great section on storage requirements.

Dates and Times

Date, time, and timestamp are actually as the most general sense that classified into strings data type, but they have their own data types based on user requirements.

  • DATE – represented in the form of YYYY-MM-DD for example 2020-10-27
  • TIME – represented in the form of HH:MM:SS for example 03:09:29
  • YEAR – represented in the form of YY or YYYY for example 20 or 2020
  • TIMESTAMP – It has a limited range from 1970-01-01 to 2038-01-19, but it is a very useful data type. But what makes this data type great is the ability to auto-populate when you create or update a record.
  • DATETIME – The similar data type of TIMESTAMP, even though it’s zero dynamic. You must calculate the value that you want to enter manually by insert yourself. That’s a drawback benefit however, is that the range is greater.

MySQL numbering

There are the several ways to store and numbering of MySQL depending on what do you want to store in your database. Just like text data, you must to choose the type of data which best suits for your storage.

DECIMAL – is data type that allows you to store very specific numeric values. I’m assuming you want to keep the cost of a product and you know all products are under 100. You can specify DECIMAL (4.2). This tells MySQL to store four digits as two digits after the decimal point. This is allowed from -99.99 to 99.99. Record 4 digits of the total and 2 digits after the decimal point.

INTEGER – are commonly used for unique identifiers or similar presentation types. The concept of an integer leads to the concept of an unsigned value. In most cases, negative values are not needed when using integers. In this case, an unsigned integer.

The other curious characteristic of this data type will automatic assignment by numbers. You probably know that identifiers grow automatically. AUTOINCREMENT is often used with the type UNSIGNED. This way you have a non-negative integer that is automatically updated for use in the table. This is specific to MySQL and not standard SQL.

ENUM – is a cool data type that has been confusing in the past. Don’t get confused anymore! ENUM simply means an enumerated list and is ideal for storing yes/no values. Instead of storing actual text to represent anything in the application, it stores numbers.

But in reality you are concatenating that number with a specific text value. You can see the actual text when you query the database. Think about options like free, paid, premium, or admin. With ENUM, you can easily accept these 4 options and do it with little memory overhead. You can only choose one possible value. Think of it as a selection tag in HTML.

SET – this is the purpose of the SET data type. SET field can store up to 64 values. Some or all of the available parameters may not be saved.

MySQL Data Types Summary

It’s not the most exciting thing to know, but MySQL data types are one of the main topics you need to know. In this issue, we discussed the different types of data and their uses. Let’s check the table here.

https://gist.github.com/dcd69b5d4b5d1c4e58228cadec5933bb

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *