The values of bigint, int, mediumint, smallint, and tinyint in MySQL

MySQL database design, which, for Data performance optimization, field type considerations are important, collected some information, organized to share, this is about the MySQL integer bigint, int, mediumint, smallint and tinyint syntax introduction, as follows:
1, bigint

Integer data (all numbers) from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807), the unsigned range is 0 to

18446744073709551615. A one is 8 bytes.

2, int

A normal size integer. The signed range is the integer data (all numbers) of -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647), and the unsigned range is 0 to 4294967295. A size of 4 bytes.
The SQL-92 synonym for int is integer.

3, Mediumint
A medium-sized integer with a signed range of 8388608 to 8388607, and an unsigned range of 0 to 16777215. A size of 3 bytes.

4, smallint

A small integer. The signed range is the integer data of -2^15 (-32,768) to 2^15-1 (32,767), and the unsigned range is 0 to 65535. A size of 2 bytes. MySQL provides more than enough functionality, and because MySQL is open source software, it can significantly reduce TCO.

5, tinyint

The signed range is-128-127, and the unsigned range is the integer data from 0 to 255. One size is 1 bytes.


Note that all arithmetic operations are done with a signed bigint or double value, so you should not use a signed large integer greater than 9223372036854775807 (63-bit), except for the bit function! Note that when two parameters are integer values,-, +, and * will use the bigint operation! This means that if you multiply by 2 large integers (or from a function that returns integers), you can get unexpected results if the result is greater than 9223372036854775807. A floating-point number, which cannot be unsigned, is a single-precision floating point, whose precision can be <=24, to a double-precision floating point, between 25 and 53, these types such as float and double type are described below immediately. Float (X) has the same range as the corresponding float and double, but the display dimensions and scale are undefined. In MySQL3.23, this is a real floating-point value. In earlier versions of MySQL, FLOAT (precision) always had 2 decimal places. This syntax is provided for ODBC compatibility.

Range of values for each data type 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 or double PRECISION or REAL
-1.7976931348623157E+308- -2.2250738585072014e-308,0,2.2250738585072014e-308-1.7976931348623157e+308
decimal[(M,[d]) or NUMERIC (m,d)
Determined by M (the length of the entire number, including the decimal point, the number of digits to the left of the decimal point, the number of digits to the right of the decimal point, but excluding the minus sign) and D (the number of digits to the right of the decimal), m defaults to 0 for the 10,d default
DATE
1000-01-01-9999-12-31
Datetime
1000-01-01 00:00:00-9999-12-31 23:59:59
TIMESTAMP
1970-01-01 00:00:00-One day in 2037 (specifically, I don't know, hehe)
Time
-838:59:59 ' to 838:59:59
year[(2|4)]
Default is 4-bit format, 4-bit format value range is 1901-2155,0000,2 bit format value range is 70-69 (1970-2069)
CHAR (m) [binary] or NCHAR (m) [binary]
The range of M is 1-255, and if there are no binary entries, it is not case-nchar indicates that the default character set is used. A space is filled in the database, but the space at the end of the removal is automatically removed.
[National] VARCHAR (M) [BINARY]
The range of M is 1-255. The space at the end of the database is automatically removed.
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 are a total of 65,535 different values
SET (' value1 ', ' value2 ',...)
A maximum of 64 members

The values of bigint, int, mediumint, smallint, and tinyint in MySQL