Value range of bigint, int, mediumint, smallint, and tinyint in mysql _ MySQL

Mysql database design, which is important for data performance optimization and field type consideration. some information is collected and shared, this section describes the syntax of mysql bigint, int, mediumint, smallint, and tinyint, as follows: 1. bigint from-2 ^ 63 (-9 mysql database design, where, for data performance optimization, field type considerations are very important. I collected some information and shared it out. This section describes the syntax for mysql bigint, int, mediumint, smallint, and tinyint, as follows:

1. bigint

The unsigned range of integer data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807) is 0

18446744073709551615. One digit is 8 bytes.

2. int

A normal integer. The signed range is-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647) integer data (all numbers). The Unsigned range is 0 to 4294967295. The size of one digit is 4 bytes.

The SQL-92 synonym for int is integer.

3. mediumint

A medium-size integer with a signed range of-8388608 to 8388607, and a Unsigned range of 0 to 16777215. The size of one digit is 3 bytes.

4. smallint

A small integer. The signed range is-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767) integer data. the unsigned range is 0 to 65535. The size of one digit is 2 bytes. MySQL provides more than enough functions, and because MySQL is an open source software, it can greatly reduce the overall cost of ownership.

5. tinyint

The signed range is-128-127, and the unsigned range is integer data ranging from 0 to 255. The size of one bit is 1 byte.

Note that all arithmetic operations are completed with signed BIGINT or DOUBLE values. Therefore, you should not use signed big integers greater than 9223372036854775807 (63 bits), except for bitwise functions! Note that when the two parameters are INTEGER values,-, +, and * use BIGINT operations! This means that if you take two big integers (or from the function that returns integers) and the result is greater than 9223372036854775807, you can get unexpected results. A floating-point number cannot be unsigned. for a single-precision floating-point number, its precision can be <= 24. for a double-precision floating-point number, it is between 25 and 53, these types, such as FLOAT and DOUBLE, are immediately described below. FLOAT (X) has the same FLOAT and DOUBLE ranges, but the display size and decimal places are undefined. In MySQL3.23, this is a real floating point value. In earlier MySQL versions, FLOAT (precision) always has two decimal places. This syntax is provided for ODBC compatibility.

Values of various data types in MySQL

TINYINT

-128-127

TINYINT UNSIGNED

0-255

SMALLINT

-32768-32767

SMALLINT UNSIGNED

0-65535

MEDIUMINT

-8388608-8388607

MEDIUMINT UNSIGNED

0-16777215

INT or INTEGER

-2147483648-2147483647

Int unsigned or INTEGER UNSIGNED

0-4294967295

BIGINT

-9223372036854775808-9223372036854775807

BIGINT UNSIGNED

0-18446744073709551615

FLOAT

-3.402823466E + 38--1.175494351E-38,0, 1.175494351E-38-3.402823466E + 38

DOUBLE, double precision, or REAL

-1.7976931348623157E + 308--2.225074255072014e-308,0, 2.225071095072014e-308-1.7976931348623157E + 308

DECIMAL [(M, [D])] or NUMERIC (M, D)

It is determined by M (the length of the entire number, including the decimal point, the number of digits on the left of the decimal point, the number of digits on the right of the decimal point, but not the negative number) and D (the number of digits on the right of the decimal point, M is 10 by default, D is 0 by default

DATE

1000-01-01-9999-12-31

DATETIME

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

TIMESTAMP

2037 00:00:00-one day in December (I don't know which day, huh, huh)

TIME

-838: 59: 59' to 838: 59: 59

YEAR [(2 | 4)]

The default format is 4 bits. the value range of 4 bits is 1901-1970, 2069, and the value range of 2 bits is 70-69)

CHAR (M) [BINARY] or NCHAR (M) [BINARY]

M ranges from 1 to 255. if BINARY is not available, it is case-insensitive. NCHAR indicates that the default character set is used. fill in space in the database, but the space at the end of the removal will be automatically removed.

[NATIONAL] VARCHAR (M) [BINARY]

M ranges from 1 to 255. spaces at the end of the database will be removed automatically.

TINYBLOB or TINYTEXT

255 (2 ^ 8-1) characters

BLOB or TEXT

65535 (2 ^ 16-1) characters

MEDIUMBLOB or MEDIUMTEXT

16777215 (2 ^ 24-1) characters

LONGBLOB or LONGTEXT

4294967295 (2 ^ 32-1) characters

ENUM ('value1', 'value2 ',...)

There can be a total of 65535 different values

SET ('value1', 'value2 ',...)

Up to 64 members