MySQL data type

Source: Internet
Author: User

MySQL supports multiple types and can be broadly divided into three categories: numeric, date/time, and string (character) types.

1). Data 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 +308)

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

2). Date Type

the date and time type that represents the time value is DATETIME, DATE, TIMESTAMP, Time, and year.

each time type has a valid value range and a value of "0", and a value of "0" is used when specifying an illegal MySQL value that cannot be represented.

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

4

1970-01-01 00:00:00/2038

End time is 2147483647 second, Beijing time 2038-1-19 11:14:07, GMT January 19, 2038 03:14:07

YYYYMMDD HHMMSS

Mixed date and time values, timestamp

3). String Type

String type refers to CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set. This section describes how these types work and how they are used in queries.

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

CHAR and VARCHAR types are similar, but they are saved and retrieved in different ways. They are also different in terms of their maximum length and whether trailing spaces are retained. No case conversions are made during the storage or retrieval process.

Binary and VARBINARY are similar to CHAR and VARCHAR, but differ in that they contain binary strings rather than binary strings. That is, they contain a byte string instead of a character string. This means that they do not have a character set, and sort and compare numeric values based on column-valued bytes.

A BLOB is a binary large object that can hold a variable amount of data. There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. The difference is that they can accommodate different storage ranges.

There are 4 types of text: Tinytext, text, Mediumtext, and Longtext. The corresponding 4 types of blobs, the maximum length can be stored in different, can be selected according to the actual situation.

MySQL data type

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.