MySQL database design, among them, for data performance optimization, field type consideration is very important, collected some information, sorted out and shared, this article is about the syntax introduction of mysql integer bigint, int, mediumint, smallint and tinyint, as follows:
1、bigint
From -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807) integer data (all numbers), the unsigned range is 0 to
18446744073709551615。 One bit is 8 bytes.
2、int
A normal-sized integer. The signed range is -2^31 (-2,147,483,648) to 2^31 - 1 (2,147,483,647) integer data (all numbers), and the unsigned range is 0 to 4294967295. A bit is 4 bytes in size.
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 bit is 3 bytes in size.
4、smallint
A small integer. The signed range is -2^15 (-32,768) to 2^15 - 1 (32,767) integer data, and the unsigned range is 0 to 65535. A bit is 2 bytes in size. MySQL offers more than enough features, and because MySQL is open source software, it can significantly reduce the total cost of ownership.
5、tinyint
The signed range is -128 - 127, and the unsigned range is integer data from 0 to 255. A bit is 1 byte in size.
Note that all arithmetic operations are done with signed BIGINT or DOUBLE values, so you should not use signed large integers larger than 9223372036854775807 (63 bits), except for bit functions! Note that when the 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 an integer), you can get unexpected results if the result is greater than 9223372036854775807. 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 described below. FLOAT(X) has the same range as FLOAT and DOUBLE, but the display size and number of decimal places are undefined. In MySQL 3.23, this is a true floating-point value. In earlier versions of MySQL, FLOAT(precision) always had 2 decimal places. This syntax is provided for ODBC compatibility.
The value range of 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 not the negative sign) and D (the number of digits to the right of the decimal point), M defaults to 10 and D defaults to 0
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 (I don't know exactly what day, hehe)
TIME
-838:59:59' to 838:59:59
YEAR[(2|4)]
The default is 4-bit format, the 4-bit format is 1901 - 2155,0000, and the 2-bit format is 70-69 (1970-2069)
CHAR(M) [BINARY] or NCHAR(M) [BINARY]
M ranges from 1 - 255, and if there is no BINARY item, it is case-sensitive, and NCHAR means using the default character set. Fill in with spaces in the database, but the spaces at the end will be automatically removed when you take them out.
[NATIONAL] VARCHAR(M) [BINARY]
M ranges from 1 to 255. The spaces at the end of the database will be 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 can be a total of 65,535 different values
SET('value1','value2',...)
There are up to 64 members
|
Posted on 2020-5-17 21:45:54
|
|
|
