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

The following mainly brings you what the data types of MySQL mainly include. I hope these words can bring you practical use. This is also the main purpose of this article that I edit the data types of MySQL. All right, don't talk too much nonsense, let's just read the following.

1bit is a bit binary.

1Byte=8bit, which is an eight-bit binary. The maximum number of values that can be represented in decimal is 2 ^ 8.

1. Numerical type

Integer type

Data type

Storage size

Tinyint

1Byte means 2 ^ 8 digits

Smallint

2Byte

Mediumint

3Byte

Int

4Byte

Bigint

8Byte

Floating point number

Data type

Float (m _ m _ d)

Single precision floating point number

M: the length of a floating-point value, excluding symbols. D: the number of digits after the decimal point

Double (m _ m _ d)

Double precision floating point number

II. Character type

Character type

Upper and lowercase

Fixed length / variable length

Take up space

Actual occupied space

Storage range

Char (n)

No zoning

Fixed length

N

N

0mura-2 ^ 8-1Bytes 256byte

Varchar (n)

Do not distinguish

Variable length

N

Actual character length space

0mi-2 ^ 16-1Bytes 65535 bytes

Binary (n)

Distinction

Fixed length

N

N

0-2 ^ 8-1bytes 256byt

Varbinary (n)

Distinction

Variable length

N

Actual character length space

0mi-2 ^ 16-1Bytes 65535 bytes

Text

Do not distinguish

Variable length

0-2 ^ 16-1bytes 65535 bytes

Blob

Distinction

Variable length

N

Actual character length space

0-2 ^ 16-1bytes 65535 bytes

Char requires a byte mark that is not empty, so a maximum of 254bytes are available.

When the varchar exceeds 255bytes, you need 2 bytes of mark field length, no more than 255bytes, you need 1 byte mark length, and the non-null tag takes up one byte.

String retrieval of char type is faster than that of varchar type.

Varchar can be specified n.Text cannot be specified.

Text is the actual number of characters + 2 bytes.

The text type cannot have a default value.

Varchar can create the index directly, and text can specify the first number of characters to create the index. Varchar queries are faster than text, and text's index doesn't seem to work when both indexes are created.

Enumerated ENUM: lists all possible values for a field. The storage range is 0-65535bytes. For example, ENUM ('Flying Magna M').

III. Date and time type

Date and time type

Storage range

Take up space

Date type DATE

"1000-01-01 → 9999-12-31"

3bytes

Time-based TIME

"- 838 59" → "838 14 59"

3bytes

Date-time DATETIME

"1000-01-01 00:00:00" → "9999-12-31 23:59:59"

8bytes

Timestamp TIMESTAMP

Is the number of seconds from 00:00:00 to the current on 1970-01-01

4bytes

Annual YEAR (2)

00-99

1bytes

Annual YEAR (4)

1901-2155

1bytes

Field modifiers

When declaring fields, you can add additional modifiers to these fields. The modifiers are different for different data types.

Data type

Modifier

Integer type

Unsigned: unsigned, meaning non-negative, defined at the front

Null

Not null

Default value: defining default values

Auto_incremnet: auto-growing, defined fields must be integer, non-empty, non-negative, unique key, or primary key

Floating point number

Unsigned, null, not null, default value

Character type

Char 、 varchar 、 text

Null, not null, default value

Character set character set (show character set can view supported character sets, show variables like'% charac%' can view supported default character sets)

Collation 'collation': set collation

Show collation can view all the collations supported by mysql.

Character type binary, varbinary, blob

Null, not null, default value

Date type

Null, not null, default value

Enum (enumerated)

Null, not null, default value

For the above data types about MySQL which mainly include, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.