The MySQL database field data type detailed explanation (1/2)

There are three types of columns in MySQL: Numeric classes, string classes, and date/time classes.

There are only three types of columns from a large class to see the column type and the numeric type. However, each column type can also be subdivided.

The various column types are described in detail below.

Data column types for numeric classes

Field type:
Tinyint:1 byte very small positive integer with symbol: -128~127, unsigned: 0~255
Smallint:2 byte small integer with symbol: -32768~32767, without symbol: 0~65535
Mediumint:3 byte medium sized integer with symbol: -8388608~8388607, unsigned: 0~16777215
Int:4 byte standard integer with symbol: -2147483648~2147483647, without symbol: 0~4294967295
Bigint:8 byte large integer with symbol: -9223372036854775808~9233372036854775807, without symbol: 0~18446744073709551615
Float:4-byte single-precision floating-point number, min 0 value: +-1.175494351e-38, max non 0 value: +-3.402823466e+38
Double:8 byte double-precision floating-point number, min non 0 value: +-2.2250738585072014e-308, max non 0 value: +-1.7976931348623157e+308
Decimal:m+2 a floating-point number, expressed as a string, that has a variable range of values, determined by the value of M and D.

Note: The notation means that you can use the minus sign, which can be changed by setting the unsigned.
When declaring an integer data column, we can assign it a display width of M (1~255), such as int (5), specify a display width of 5 characters, and if you don't specify a display width for it, MySQL assigns it a default value. The display width is used only for display and does not limit the range of values and space occupied, such as INT (3) or 4 bytes of storage space.
So, this length is just the length of the display, used to display the data. has no other effect.
Assigning a zerofill to a field is a complement, so the length of the int (3) shown above works, such as 1 for 001, and it automatically helps us set the unsigned property, which is not negative.
Primary key: The primary key to the field is to mark the uniqueness of the field, which means that the contents of this field cannot be duplicated, if two fields or more are set the primary key indicates that the data combined by these fields is unique, and the uniqueness of all the fields.

Date, Time Type data column type

DATE 1000-01-01~9999-12-31 3 bytes (MySQL3.23 version formerly 4 bytes) 0000-00-00 time-838:59:59~838:59:59 3 bytes 00:00:00 DATETIME 1000-01-01 0 0:00:00~9999-12-31 23:59:59 8 bytes 0000-00-00 00:00:00 TIMESTAMP A time of 19,700,101,000,000-2037 years 4 bytes 00000000000000 year (4) : 1901~2155 Year (2): 1970~2069 1 bytes 0000

MySQL always put the dates and dates in front of the year, in the order of month and day.

Date, time, datatime data column type

The date, time, and datatime types hold a combination of day, value, date, and time values, respectively. They are in the form of "Ccyy-mm-dd", "Hh:mm:ss", "Ccyy-mm-dd hh:mm:ss", respectively.

The time value in the datatime is different, and the time value in the datatime represents a few minutes, while the time value represents the amount spent. When inserting a value into a Time data column, you need to write the complete writing of the times, such as "00:12:30" in 12 minutes and 30 seconds.

Timestamp data column type

The format of the timestamp data column is CCYYMMDDHHMMSS, with a value range starting from 19700101000000, i.e. January 1, 1970, up to 2037 years. It is characterized by the ability to record the creation or modification time of a row of data:

If you insert a null value into the timestamp column, the data column automatically takes the value of the current date and time.

When a data row is created and modified, if the timestamp data column is not explicitly assigned, it is automatically evaluated to the current date and time. If there is more than one timestamp column in the row, only the first is automatically evaluated.

Setting a fixed date and time value on the timestamp will invalidate timestamp's automatic value-taking function.

Timestamp the default column width is 14, you can specify the width of the columns to change the display effect. However, regardless of the column width you specify, MySQL stores the timestamp value in 4 bytes and is always evaluated with 14-bit precision.

If you need to record the creation time and the most recent modification time, you can use two timestamps to record, a record creation time, and a record modification time. But there are two things that need to be remembered. The first is to put the record modification time timestamp data column at the front, so that will automatically take the value; second, when you create a new record, you use the now () function to initialize the creation time timestamp data column so that the timestamp data column does not change.

Year

Year is a single-byte data column type, and year (4) has a value range of 1901~2155,year (2) that is 1970~2069, but displays only the last two digits. MySQL automatically converts a two-digit year into a four-digit year, where 97 and 14 points are converted to 1997 and 2014. The conversion rule is this:

The year value 00~69 will be converted into 2000~2069;

The year value 70~99 will be converted to 1970~1999.

00 are converted to 0000 instead of 2000. Because the value 00 is 0, and 0 is a valid value for year. Blob and text

A blob is a binary string, and the text is a non binary string. Both can store large volumes of information.

Home 1 2 last page