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

MySQL data type

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) usage TINYINT1 byte (- 128127) (0255) small integer value SMALLINT2 byte (- 32 768 minus 32 767) (0meme 65 535) large integer value MEDIUMINT3 byte (- 8 388 608) (0mei 16 777 215) large integer value INT or INTEGER4 byte (- 2 147 483 648 4147 483 647) (0min 4 294 967 295) large integer value BIGINT8 byte (- 9 233 372 036 854 775 808) 9 223 372 036 854 775 807) (0Magna 18 446 744 073 709 551 551 615) maximum integer FLOAT4 bytes (- 3.402 823 466 Elaine 38 FLOAT4 bytes), 0, (1.175 494,351 Emai 38 3.402 823 466 351 Elux 38) 0, (1.175 494,351 E38 Ensemble 3.402 823 466 Ecards 38) single precision

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

Floating-point values DECIMAL to DECIMAL (M > D), if M > D, if M > D, otherwise, it depends on the decimal values of M and D, 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

(byte) 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 00Rank 9999-12-31 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-255Byte fixed length string VARCHAR0-65535 byte variable length string TINYBLOB0-255Bytes binary string TINYTEXT0-255byte short text string BLOB0-65535 bytes long text data TEXT0-65535 bytes long text data MEDIUMBLOB0-1677215 bytes binary medium length text data MEDIUMTEXT0-1677215 bytes medium length text data LONGBLOB0-4 294 967 295 byte maximum text data LONGTEXT0-4 294 967 295 byte maximum text data in binary form

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.

The BINARY and VARBINARY classes 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. They just differ in the maximum length of the allowable value.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to four BLOB types with the same maximum length and storage requirements.

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.