[Daily update-mysql Basics] 5. Common data types-integers and strings

1. Data type

We've been talking about data types when we're learning PHP, so the data type is the format of the data. Each type of data stored in the computer will be different, the storage capacity is also different, so choosing the right data type can save our storage costs, but also facilitate our program operation and development efficiency.

1.1 Integer type

Represents: Int

In PHP, to store an integer type requires 4 bytes, in MySQL, this int can be specified manually, it calculates the number of bytes used based on the length of your choice. The standard integer type is also 4 bytes.

When defining a table, if you select int, you need to set a length that automatically sets the capacity of the field according to length and cannot store data that exceeds the capacity.

So when considering the field, we need to know the maximum amount of data to be stored, and choose the length of the storage according to the maximum number. Once selected, the space is fixed!

Integers contain both positive and negative integers. But you can also store only positive integers in MySQL. That is, there is no negative number, the smallest is 0, using the unsigned property, you can make a field without negative numbers.

1.2 Decimal types

Expression: Float/double/decimal

In MySQL, floating-point and fixed-point types can use the type name followed by m/d to represent the length of the value, m for the total length of the value, and D for the number of digits after the decimal point. M and d are also known as precision and scale. For example: Float (5,2): The precision is 5, 2 digits after the decimal point, then the maximum value is: 999.99. Assuming that the data we deposited is (100.009) then this value does not meet our storage requirements (5,2) so it will round up the values and become (100.01) to store them.

Note: In MySQL, float and double are displayed by default with actual precision when not specifying precision, while decimal does not specify precision, the default integer part is 10, and the decimal part is 0

In PHP there is no single or double precision difference, and in MySQL, there is,

Float occupies 4 bytes and is a single precision

Double occupies 8 bytes, which is a dual precision

It is important to note that computers are not very good at processing decimals, they are saving approximate values, so it is best not to take decimals for accurate calculation and accurate comparison.

Decimals also have positive and negative decimals, and can also be unsigned to remove symbols.

1.3 String Type

Expression: Char/varchar/text

1.3.1 Char

A fixed-length string type. Disclaimer: char (length value)

Once the length is defined, regardless of whether the stored characters are so long enough to store so many bytes, in use, take into account the length of the stored data will not change, if need change, then the char type is not suitable for you.

1.3.2 varchar

Variable-length strings

A varchar type is a more used type, which can vary the number of bytes stored based on the length of the data's content. At the time of the Declaration, it is also necessary to pass in a length such as varchar (255), 255 means the longest can store 255, but if there is not so much, the storage will not occupy more storage space.

NOTE: varchar will add one to two extra bytes to store the true length of the data, so the storage: ABCD, the space will occupy: 4+1, where the 1 is used to store the true length of the data.

If the length of the data is longer than 255 characters, then the space that stores the true length becomes 2.

A varchar type can store 65,535 characters.

How should char and varchar be chosen?

Char Type: Faster calculation

VARCHAR type: more space-saving.

Decide for yourself according to your needs.

1.3.3 Text

Text is used to store a larger type of string.

It is also variable in length. So we recommend that you save the string with a length greater than 255 as the text type.

The corresponding text type has 4:

Tinytext text Mediumtext Longtext

In order to store the length of different, according to their own needs to choose, in general, an article, using the text type is sufficient.

[Daily update-mysql Basics] 5. Common data types-integers and strings