Learn more about Mysql's 4 common, important data types _mysql

In order to achieve this goal, it requires us to have a good understanding of the common data types in the database system.

Now let's take a look at the important data types of MySQL

1, numeric types can be divided into numeric types: integral type, floating-point decimal type.

The so-called "decimal" refers to decimal and numeric, and they are of the same type. Strictly speaking, it is not a numeric type, because they are actually stored as strings, each digit value (including a decimal point) occupies a byte of storage space, so this type consumes a lot of storage space, but its advantage is that its value does not lose the floating-point calculation precision, it is more suitable for some computational precision requirements high, such as price calculation. Floating depending on the type of precision can be float or double. Their advantage is the precision of decimals, float can represent very small values, can be the smallest value of about 1.17E-38 (0.000 ...). 0117, 370 after the decimal point, the double can express a smaller number, the smallest number can be about 2.22E-308 (0.000 ...). 0222, the decimal point and a decimal number followed by 307 0). float and double are 4-byte and 8-byte storage space respectively.

For an integral type, there are many different types of integers in MySQL, in the Design database table, we can have a byte tinyint or 8 byte bigint, so we should consider which type to use too much to obtain the smallest storage space, without losing any accuracy value.

Tinyint,smallint,mediumint,int and bigint 1 bytes, 2 bytes, 3 bytes, 4 bytes, and 8 bytes. For unsigned integers, the maximum integer that these types can represent is 255,65535,16777215,4294967295 18446744073709551615 respectively. If we need to save the age of the user, tinyint is enough; if it is an ID, we should use Mediumint instead of int,int or too big. Many datasheets do not reach the Mediumint range.

2, Date time entered the date and time type is relatively simple

such as Date,time,datetime,timestamp and year. If we only need to care about the date, but no minutes we should use date instead of datetime, but DateTime is one of the most commonly used, and everything is designed according to actual needs

3, character type do not assume that the character type is only char

The difference between char and varchar is that char is a fixed length. If you define a field char (10), then no matter how many bytes of data, this will require 10 bytes of space, and for the 18-bit ID number, you should use char (18),

VARCHAR is variable length, and if we have a field with a value of different lengths, then we should use varchar.

4. Enumeration and collection type enumeration (enum) types

You can define up to 65,535 different strings from which to make a selection.

This may need to depend on how many values are one or two bytes in the enumeration type. Collection (set) type, you can have up to 64 different members, you can select 0 or more members, the number of members of the collection decision. For example, in SQL Server, you can use the bit type to represent the gender (male/female), but in MySQL, the bit in different versions of the database to take the value of the difference, and the use of tintint sometimes wasted, you can use the enum (' Male ', ' female '), so you can save a lot of space