MYSQL width indicator and MYSQL width indicator

MYSQL width indicator and MYSQL width indicator

1. In the old version of MySQL 3.22, the single table size of MySQL is 4 GB. At that time, the MySQL storage engine was also the ISAM storage engine. However, when the MyISAM storage engine appears, that is, starting from MySQL 3.23, the maximum limit for a single MySQL table has been extended to 64 Pb (as shown in the official document ). That is to say, from the current technical environment, the single table size limit of the MyISAM storage engine of the MySQL database is no longer determined by the MySQL database itself, it is determined by the file system on the OS of the host. One of the most popular storage engines in MySQL, Innodb, has two data storage policies: Shared tablespace storage and exclusive tablespace storage. When using the shared tablespace storage method, all data in Innodb is stored in a single tablespace, which can be composed of multiple files. A table can exist across multiple files, its size limit is not a limitation of the file size, but its own limitation. From the official Innodb documentation, we can see that the maximum tablespace limit is 64 TB. That is to say, the single table limit of Innodb is about 64 TB, of course, this size includes all indexes of the table and other related data. When an exclusive tablespace is used to store Innodb tables, the data in each table is stored in a separate file. In this case, the single table is limited, the size of the file system is limited.

2. int internal storage int Is 4 bytes. The number in the brackets is the display width. If zerofill is not set, it is useless. If zerofill is set, 0 is used to complete the case. It is only used for display and has nothing to do with storage. The numbers in int-type parentheses have no significance for mysql. It doesn't matter if you don't write them. If you write them, it doesn't matter. If you want to increase the number, you can use bigint.

######################################## ######################################## ######################################## ############################

Utf8, a Chinese character requires three bytes

As an extension to the ANSI/ISO SQL92 standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT listed in the preceding table. Another extension is that MySQL allows you to specify the display width of an integer value, followed by parentheses (for example, INT (4 )). This optional width is used to fill the display left when its width is less than the specified width of the column, but it does not limit the range of values that can be stored in the column, it does not limit the number of digits that will be displayed, and its width exceeds the specified width of the column. When used together with the optional extension property ZEROFILL, the default space fill is replaced by zero. For example, for a column declared as INT (5) ZEROFILL, a value of 4 is retrieved as 00004. Note: If you store a larger value in an integer column that exceeds the display width, you may encounter problems when MySQL generates temporary tables for some complex joins, in these cases, MySQL believes that the data is indeed suitable for the original column width.

**************************************** **************************************** **************************************** **************************************** *************************

Http://caixia.blog.51cto.com/2266345/759569

MySQL supports all standard SQL numeric data types. These types include strict NUMERIC data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), and approximate NUMERIC data types (FLOAT, REAL, and double precision ). The keyword INT Is the synonym of INTEGER, And the keyword DEC is the synonym of DECIMAL.

BIT Data Type stores BIT field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.

As an extension of the SQL standard, MySQL also supports integer TINYINT, MEDIUMINT, and BIGINT. The following table shows the storage and range of each integer type.
TINYINT: a positive integer with a very small size of 1 byte, with the symbol:-128 ~ 127, without symbols: 0 ~ 255
SMALLINT: 2-byte small integer, with the symbol:-32768 ~ 32767, without symbols: 0 ~ 65535
MEDIUMINT: A 3-byte integer of medium size, with the symbol-8388608 ~ 8388607, without symbols: 0 ~ 16777215
INT: 4-byte standard integer, with the symbol:-2147483648 ~ 2147483647, without symbols: 0 ~ 4294967295
BIGINT: 8-byte big integer, with the symbol:-9223372036854775808 ~ 9233372036854775807, without symbols: 0 ~ 18446744073709551615
FLOAT: 4-byte Single-precision floating point number. Minimum non-zero value: +-1.175494351E-38; maximum non-zero value: +-3.402823466E + 38
DOUBLE: 8-byte DOUBLE-precision floating point number, minimum non-zero value: +-2.225074255072014e-308, maximum non-zero value: +-1.7976931348623157E + 308
DECIMAL: A floating point number expressed as a string in M + 2 bytes. Its value range is variable, determined by the values of M and D.

MySQL also supports specifying the display width of an integer (for example, INT (4) in parentheses after a keyword of this type )). The optional display width rule is used to fill up the width from the left when the display width is less than the specified column width value.

The display width does not limit the range of values that can be saved in the column, nor the display of values that exceed the specified width of the column.

When combined with the optional extension attribute ZEROFILL, the spaces supplemented by default are replaced by zero. For example, for a column declared as INT (5) ZEROFILL, value 4 is retrieved as 00004. Note that if you save a value that exceeds the display width in an integer column, MySQL may encounter problems when generating a temporary table for a complex join, because MySQL believes that the data is suitable for the width of the original column.

When declaring an integer data column, we can specify a display width M (1 ~ 255), such as INT (5), specifying the display width to 5 characters. If the display width is not specified for it, MySQL will specify a default value for it. The display width is only used for display, and the value range and occupied space cannot be limited. For example, INT (3) occupies 4 bytes of storage space, and the maximum allowed value is not 999, the maximum value allowed by the INT type.

To save storage space and improve database processing efficiency, we should select the most suitable data column type based on the value range of application data.
If you store a number that exceeds the value range of a data column in this column, mysql truncates this value, for example:
We store 99999 in the smallint (3) data column, because the value range of smallint (3) is-32768--32767, so
Will be truncated to 32767 storage. The display width 3 does not affect the storage of arrays. Only display affected

Http://www.yesky.com/imagesnew/software/mysql/manual_Reference.html