Shulou(Shulou.com)06/01 Report--

This article will explain in detail the data types of which fields are in the mysql data table, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The data types of fields in the data table are: TINYINT, SMALLINT, MEDIUMINT, INT, INTEGE, DATETIME, DATE, TIMESTAMP, TIME, YEAR, CHAR, VARCHAR, TEXT, TINYBLOB, BLOB, and so on.

Defining the type of data field in MySQL is very important for optimizing your database.

MySQL supports many types, which can be roughly divided into three types: numeric, date / time, and string (character) types.

Numerical type

MySQL supports all standard SQL numeric data types.

These types include strict numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC) and approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).

The keyword INT is synonymous with INTEGER, and the keyword DEC is synonymous with DECIMAL.

The BIT data type holds bit field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.

As an extension of the SQL standard, MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the storage and range of each integer type required.

Type size range (signed) range (unsigned) use TINYINT1 byte (- 128127) (0255) small integer value SMALLINT2 bytes (- 32 768 minus 32 767) (0mei 65 535) large integer value MEDIUMINT3 bytes (- 8 388 608 minus 8 388 607) (0mei 16 777 215) large integer value INT or INTEGER4 bytes (- 2147 483 648 minus 2147 483 647) (0r4 294 967 295) large integer value BIGINT8 bytes 18 446 744 073 709 551 615) maximal integer value FLOAT4 bytes (- 3.402 823 466 Equ 38), 0, (1.175 494,351 Elam 38), 0, (1.175 494,351 Ecolor 38) single precision

Floating-point number DOUBLE8 bytes (- 1.797 693 134 862 315 7 Ecolors 308), 0, (2.225 073 858 507 201 4 Elyle 308), 0, (2.225 073 858 507 201 4 Elyle 308) 0, (2.225 073,858 507 201 4 Elam 308 1.797 693 134 862 315 7) double precision

Floating-point values DECIMAL to DECIMAL (M > D), if M > D, it is M > D, otherwise it is M and D dependent on M and D values, depending on M and D decimal values.

Date and time type

The date and time types that represent time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

Each time type has a range of valid values and a "zero" value, which is used when specifying values that cannot be represented by illegal MySQL.

The TIMESTAMP type has a proprietary automatic update feature, which will be described later.

Type size

(bytes) range format use DATE31000-01-01 23:59:59YYYY-MM-DD HH:MM:SS 9999-12-31YYYY-MM-DD date value TIME3'-838:59:59'/'838:59:59'HH:MM:SS time value or duration YEAR11901/2155YYYY year value DATETIME81000-01-01 00Gang 00hand 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS mixed date and time value TIMESTAMP4

1970-01-01 00VOG 0000GOUBER 2038

The end time is the 2147483647 second, Beijing time 2038-1-19 11:14:07 Beijing time, 03:14:07 GMT on January 19, 2038

YYYYMMDD HHMMSS mixed date and time values, timestamp

String type

String types refer to CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM and SET. This section describes how these types work and how to use them in queries.

Type size use CHAR0-65535 bytes fixed-length string VARCHAR0-65535 bytes variable length string TINYBLOB0-255binary string TINYTEXT0-255bytes short text string BLOB0-65535 bytes long text data TEXT0-65535 bytes long text data MEDIUMBLOB0-16 777 215 bytes binary medium length text data MEDIUMTEXT0-16 777 215 bytes medium length text data LONGBLOB0-4 294 967 295 bytes Extremely large text data in binary form LONGTEXT0-4 294 967 295 bytes maximum text data

Note: n in parentheses in char (n) and varchar (n) represents the number of characters, not the number of bytes. For example, CHAR (30) can store 30 characters.

CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They are also different in terms of maximum length and whether trailing spaces are retained. No case conversion occurs during storage or retrieval.

BINARY and VARBINARY are similar to CHAR and VARCHAR, except that they contain binary strings rather than non-binary strings. That is, they contain byte strings instead of character strings. This means that they do not have a character set, and sort and compare numeric values based on column value bytes.

BLOB is a large binary object that can hold a variable amount of data. There are four types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The difference between them is that they can accommodate different storage ranges.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. The maximum length of the corresponding four BLOB types is different, which can be selected according to the actual situation.

The data types of which fields in the mysql data table are shared here. I hope the above content can be helpful to you and you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.