MySQL Common data types

Source: Internet
Author: User

MySQL supports a variety of data types, and the big nephew can be divided into three categories: numeric type, Time type, string type, and conforming type;

Numeric type:

Type

Size

Range (signed)

Range (unsigned)

Use

TINYINT

1 bytes

(-128,127)

(0,255)

Small integer value

SMALLINT

2 bytes

(-32 768,32 767)

(0,65 535)

Large integer value

Mediumint

3 bytes

(-8 388 608,8 388 607)

(0,16 777 215)

Large integer value

int or integer

4 bytes

(-2 147 483 648,2 147 483 647)

(0,4 294 967 295)

Large integer value

BIGINT

8 bytes

(-9 233 372 036 854 775 808,9 223 372 036 854 775 807)

(0,18 446 744 073 709 551 615)

Maximum integer value

FLOAT

4 bytes

( -3.402 823 466 e+38,1.175 494 351 E-38), 0, (1.175 494 351 e-38,3.402 823 466 351 e+38)

0, (1.175 494 351 e-38,3.402 823 466 e+38)

Single precision
Floating point value

DOUBLE

8 bytes

(1.797 693 134 862 315 7 e+308,2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+3 08)

0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308)

Double precision
Floating point value

DECIMAL

For decimal (m,d), if m>d, is m+2 otherwise d+2

Values that depend on M and D

Values that depend on M and D

Decimal value

Date Time Type

Each time type has a valid value range and a value of "0" when specifying an illegal value MySQL uses the "0" value

Type

Size
(
bytes )

Range

Format

Use

DATE

3

1000-01-01/9999-12-31

Yyyy-mm-dd

Date value

Time

3

' -838:59:59 '/' 838:59:59 '

HH:MM:SS

Time Value or duration

Year

1

1901/2155

YYYY

Year value

Datetime

8

1000-01-01 00:00:00/9999-12-31 23:59:59

YYYY-MM-DD HH:MM:SS

Blend date and time values

TIMESTAMP

8

1970-01-01 00:00:00/2037, sometime

YYYYMMDD HHMMSS

Mixed date and time values, timestamp

String type

Type

Size

Use

CHAR

0-255 bytes

Fixed length string

VARCHAR

0-65535 bytes

Variable length string

Tinyblob

0-255 bytes

A binary string of no more than 255 characters

Tinytext

0-255 bytes

Short text string

Blob

0-65 535 bytes

Long text data in binary form

TEXT

0-65 535 bytes

Long Text data

Mediumblob

0-16 777 215 bytes

Medium-length text data in binary form

Mediumtext

0-16 777 215 bytes

Medium-Length text data

Longblob

0-4 294 967 295 bytes

Large text data in binary form

Longtext

0-4 294 967 295 bytes

Maximum text data

Composite type

MySQL also supports two composite data types, enum and set, which extend the SQL specification. Although these types are listed in the technology listing string type, they can be considered different data types.

An enum type allows only one value to be obtained from a collection; The two set types allow for any number of values in a collection.

Enum type

The ENUM type is somewhat similar to a single option because it allows only one value to be obtained in the collection. It's easy to understand when dealing with data from each other, such as human sexuality. The ENUM Type field can get a value from the collection or use a null value.

In addition, the input will cause MySQL to insert an empty string in this field. In addition, if the casing of the inserted value does not match the case of the value in the collection, MySQL automatically converts the casing of the inserted value to a value that is consistent with the case in the collection.

The ENUM type can be stored as a number inside the system, and it is indexed with numbers starting from 1. An ENUM type can contain up to 65,536 elements, one of which is retained by MySQL to store error messages,

This error value is represented by index 0 or an empty string.

MySQL considers that the value that appears in the ENUM type collection is a valid input, except that any other input will fail. This means that it is easy to find the location of the error record by searching for a row that contains an empty string or a corresponding numeric index of 0.

Set type

The SET type is similar but not the same as the ENUM type. A set type can take any number of values from a predefined collection. And the same as the ENUM type, any attempt to insert a non-predefined value in the SET Type field will cause the

MySQL inserts an empty string. If you insert a record that has a valid element and an illegal element, MySQL retains the legitimate element, removing the illegal element.

A SET type can contain up to 64 elements. The value in the SET element is stored as a separate "bit" sequence, which represents the element corresponding to it. A bit is a simple and efficient way to create a collection of ordered elements.

And it also removes duplicate elements, so it is not possible to include two identical elements in a SET type.

You want to find illegal records from the SET type field simply look for rows that contain an empty string or binary value of 0

MySQL Common data types

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.