|
|
mysql database design, which, for data performance optimization, field type considerations are very important, collected some information, collated and shared out, this is about mysql integer bigint, int, mediumint, smallint and tinyint syntax is introduced, as follows:
1. bigint
Integer data (all numbers) from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807), unsigned in the range 0 to
18446744073709551615. 8 bytes per bit.
2, int
A normal size integer. Signed integer data (all numbers) in the range -2^31 (-2,147,483,648) to 2^31 - 1 (2,147,483,647), unsigned in the range 0 to 4294967295. one bit is 4 bytes.
The SQL-92 synonym for int is integer.
3、mediumint
A medium-sized integer, signed range is -8388608 to 8388607, unsigned range is 0 to 16777215. One bit size is 3 bytes.
4, smallint
A small integer. The signed range is -2^15 (-32,768) to 2^15 - 1 (32,767) integers, the unsigned range is 0 to 65535. one bit is 2 bytes in size. the functionality provided by MySQL is more than adequate, and since MySQL is open source software, the total cost of ownership can be greatly reduced.
5. tinyint
Signed range is -128 - 127, unsigned range is integer data from 0 to 255, one bit size is 1 byte. A bit size of 1 byte.
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 -, +, and * will use BIGINT arithmetic when both arguments are INTEGER values! This means that if you multiply 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, which cannot be unsigned, can have a precision of <=24 for a single-precision floating-point number, and between 25 and 53 for a double-precision floating-point number, these types such as the FLOAT and DOUBLE types are described immediately below.FLOAT(X) has the same range as the corresponding FLOAT and DOUBLE, but the display size and the 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 has 2 decimal places. This syntax is provided for ODBC compatibility.
Value ranges for each data type in MySQL
TINYINT
-128 - 127
TINYINT UNSIGNED
0 - 255
SMALLINT
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 whole 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 does not include the minus sign) and D (the number of digits to the right of the decimal point) to determine the number of digits, M defaults to 10, 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 - 2037 (I don't know exactly what day it is, lol)
TIME
-838:59:59' to 838:59:59
YEAR[(2|4)]
Default is 4-digit format, 4-digit format range is 1901 - 2155,0000, 2-digit format range is 70-69(1970-2069).
CHAR(M) [BINARY] or NCHAR(M) [BINARY]
M ranges from 1 - 255, case insensitive if there is no BINARY entry, NCHAR uses the default character set. In the database with a space complement, but in the retrieval of the end of the space will be automatically removed.
[NATIONAL] VARCHAR(M) [BINARY]
The range of M is 1 - 255. The space at the end will be removed automatically in the database.
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',...)
can have up to 64 members
|
Previous: PHP line break PHP_EOLNext article: linux update yum update command
|
发表于 2020-5-17 21:45:54
rebroadcast
