There are 5 types of mysql field types: 1. Integer type, mainly used to store integer numbers, including INT, BIGINT, TINYINT, etc.; 2. Floating point number and fixed point number types, used to store decimals, floating point numbers are FLOAT and DOUBLE, fixed-point numbers include DECIMAL; 3. String type, used to store string data, etc.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
Every field in the database should have an appropriate data type that limits or allows the data stored in that column. For example, if the field stores numbers, the corresponding data type should be numeric.
If you use the wrong data type, it may seriously affect the function and performance of the application. Therefore, when designing the table, you should pay special attention to the data type used in the data column. Changing a column containing data is not a trivial matter, and doing so may result in data loss. Therefore, the correct data type and length must be set for each column when creating the table.
The data types of MySQL fields can be roughly divided into 5 types, namely integer type, floating point type and fixed point type, date and time type, string type, binary type, etc.
Note: Integer types and floating point types can be collectively referred to as numeric data types.
1. Integer type
The integer type is also called numerical data. The numerical data type is mainly used to store integer numbers.
MySQL provides a variety of numeric data types. Different data types provide different value ranges. The larger the value range that can be stored, the greater the storage space required.
The main integer types provided by MySQL are TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, and AUTO_INCREMENT auto-increment constraints can be added to their attribute fields. The following table lists the numeric types in MySQL.
| Type name | Description | Storage requirements |
|---|---|---|
| TINYINT | Small integer | 1 byte |
| SMALLINT | Small integer | 2 bytes |
| MEDIUMINT | Medium size integer | 3 bytes |
| INT (INTEGHR) | Normal size integer | 4 bytes |
| BIGINT | Large integer | 8 bytes |
As you can see from the above table, different types of integers require different numbers of bytes to store. The one that occupies the smallest number of bytes is the TINYINT type, and the one that occupies the largest number of bytes is the TINYINT type. BIGINT type, the more bytes the type occupies, the larger the numerical range it can represent.
The value range of each data type can be found based on the number of bytes occupied. For example, TINYINT requires 1 byte (8bit) to store, then the maximum value of TINYINT unsigned number is 28-1, which is 255; the maximum value of TINYINT signed number is 27-1, which is 127.
2. Floating-point and fixed-point types
MySQL uses floating-point and fixed-point numbers to represent decimals.
There are two floating-point types, single-precision floating-point numbers (FLOAT) and double-precision floating-point numbers (DOUBLE); there is only one fixed-point type, which is DECIMAL.
Both floating-point types and fixed-point types can be represented by (M, D), where M is called precision, indicating the total number of digits; D is called scale, indicating the number of decimal digits.
The value range of floating point number type is M (1~255) and D (1~30, and cannot be greater than M-2), which represent the display width and number of decimal places respectively. M and D are optional in FLOAT and DOUBLE, and the FLOAT and DOUBLE types will be saved to the maximum precision supported by the hardware. The default D value for DECIMAL is 0 and M value is 10.
The following table lists the decimal types and storage requirements in MySQL.
| Type name | Description | Storage requirements |
|---|---|---|
| FLOAT | Single precision floating point number | 4 bytes |
| DOUBLE | Double precision floating point number | 8 bytes |
| DECIMAL (M, D), DEC | Compressed "strict" fixed-point number | M 2 bytes |
DECIMAL type is different from FLOAT and DOUBLE. DOUBLE is actually stored in the form of a string. The maximum possible value range of DECIMAL is the same as DOUBLE, but the effective value range is determined by M and D. If M is changed and D is fixed, the value range will become larger as M becomes larger.
As can be seen from the above table, the storage space of DECIMAL is not fixed, but is determined by the precision value M, occupying M 2 bytes.
The value range of the FLOAT type is as follows:
The signed value range: -3.402823466E 38～-1.175494351E-38.
Unsigned value range: 0 and -1.175494351E-38～-3.402823466E 38.
The value range of DOUBLE type is as follows:
Signed value range: -1.7976931348623157E 308～-2.2250738585072014E-308.
Unsigned value range: 0 and -2.2250738585072014E-308～-1.7976931348623157E 308.
Tip: Regardless of whether it is a fixed-point or floating-point type, if the user-specified precision exceeds the precision range, it will be rounded for processing.
FLOAT and DOUBLE will default to the actual precision (determined by the computer hardware and operating system) when the precision is not specified. DECIMAL will default to (10, 0) if the precision is not specified.
The advantage of floating-point numbers over fixed-point numbers is that they can represent a larger range when the length is certain; the disadvantage is that it can cause accuracy problems.
Finally, let me emphasize: In MySQL, fixed-point numbers are stored in the form of strings. When the accuracy requirements are relatively high (such as currency, scientific data), it is better to use the DECIMAL type, and the other two float types are better. Point numbers are also prone to problems when performing subtraction and comparison operations, so you need to pay attention when using floating point numbers and try to avoid floating point comparisons.
3. Date/time type
There are many data types representing dates in MySQL: YEAR, TIME, DATE, DTAETIME, TIMESTAMP . When only recording year information, you can only use the YEAR type.
Each type has a legal value range. When an illegal value is specified, the system will insert a "zero" value into the database.
The following table lists the date and time types in MySQL.
| Type name | Date format | Date range | Storage requirements |
|---|---|---|---|
| YEAR | YYYY | 1901 ~ 2155 | 1 byte |
| TIME | HH :MM:SS | -838:59:59 ~ 838:59:59 | 3 bytes |
| DATE | YYYY-MM-DD | 1000-01-01 ~ 9999-12-3 | 3 bytes |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 8 bytes |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1980-01-01 00:00:01 UTC ~ 2040-01- 19 03:14:07 UTC | 4 bytes |
YEAR type
The YEAR type is a single-byte type used to represent the year and requires only 1 byte for storage. YEAR can be specified in various formats, as follows:
YEAR expressed in a 4-digit string or 4-digit number format, ranging from '1901' to '2155'. The input format is 'YYYY' or YYYY. For example, if you enter '2010' or 2010, the values inserted into the database will be 2010.
YEAR expressed as a 2-digit string in the range '00' to '99'. Values in the ranges of '00' to '69' and '70' to '99' are converted to YEAR values in the ranges of 2000 to 2069 and 1970 to 1999 respectively. '0' has the same effect as '00'. Values inserted outside the range will be converted to 2000.
YEAR expressed as a 2-digit number, ranging from 1 to 99. Values in the ranges of 1 to 99 and 70 to 99 are converted to YEAR in the range of 2001 to 2069 and 1970 to 1999 respectively. value. Note that here a value of 0 will be converted to 0000, not 2000.
Tip: The two-digit integer range is slightly different from the two-digit string range. For example, to insert the year 3000, a reader might use the numeric format 0 to represent YEAR, but in fact, the value inserted into the database is 0000, not 3000 as expected. Only '0' or '00' in string format can be correctly interpreted as 3000, illegal YEAR values will be converted to 0000.
TIME type
TheTIME type is used for values that require only time information and requires 3 bytes for storage. The format is HH:MM:SS. HH represents hours, MM represents minutes, and SS represents seconds.
The value range of the TIME type is -838:59:59～838:59:59. The reason why the hour part is so large is that the TIME type can not only be used to represent the time of a day (must be less than 24 hours), It could also be the time since an event or the time between two events (which can be greater than 24 hours, or even negative).
TIME values can be specified using various formats, as shown below.
'D HH:MM:SS' format string. You can also use these "non-strict" syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH' or 'SS'. D here represents the day, which can take a value between 0 and 34. When inserting into the database, D is converted to hours and saved in the format "D*24 HH".
'HHMMSS' format, a string without separators, or a numeric value in HHMMSS format, assuming a meaningful time. For example, '101112' is understood as '10:11:12', but '106112' is not legal (it has a meaningless minutes part) and will become 00:00:00 when stored.
Tip: When assigning abbreviated values to the TIME column, please note: If there is no colon, MySQL assumes that the rightmost two digits represent seconds when interpreting the value. (MySQL interprets TIME values as past time rather than current time). For example, a reader might think '1112' and 1112 mean 11:12:00 (that is, 12 minutes after 11 o'clock), but MySQL interprets them as 00:11:12 (that is, 11 minutes and 12 seconds). Likewise '12' and 12 are interpreted as 00:00:12. On the contrary, if a colon is used in the TIME value, it is definitely regarded as the time of the day, that is, '11:12' means 11:12:00, not 00:11:12.
DATE type
The DATE type is used when only a date value is required, without the time part, and requires 3 bytes for storage. The date format is 'YYYY-MM-DD', where YYYY represents the year, MM represents the month, and DD represents the day.
When assigning values to DATE type fields, you can use string type or numeric type data to insert, as long as it conforms to the date format of DATE. As shown below:
The date expressed in the format of 'YYYY-MM-DD' or 'YYYYMMDD' characters, the value range is '1000-01-01'～'9999- 12-3'. For example, enter '2015-12-31' or '20151231', and the date inserted into the database will be 2015-12-31.
Represent the date in 'YY-MM-DD' or 'YYMMDD' string format, where YY represents the two-digit year value. MySQL interprets the rules for two-digit year values: year values in the range of '00~69' are converted to '2000~2069', and year values in the range of '70~99' are converted to '1970~1999'. For example, if you enter '15-12-31', the date inserted into the database is 2015-12-31; if you enter '991231', the date inserted into the database is 1999-12-31.
The date expressed in YYMMDD numeric format is similar to the previous one. Year values in the range of 00~69 are converted to 2000~2069, and year values in the range of 80~99 are converted to 1980~1999. For example, if you enter 151231, the date inserted into the database is 2015-12-31, and if you enter 991231, the date inserted into the database is 1999-12-31.
Use CURRENT_DATE or NOW() to insert the current system date.
Tip: MySQL allows "relaxed" syntax: any punctuation mark can be used as a separator between date parts. For example, '98-11-31', '98.11.31', '98/11/31' and '98@11@31' are equivalent and these values will be inserted into the database correctly.
DATETIME type
The DATETIME type is used for values that need to contain both date and time information and requires 8 bytes for storage. The date format is 'YYYY-MM-DD HH:MM:SS', where YYYY represents the year, MM represents the month, DD represents the day, HH represents the hour, MM represents the minute, and SS represents the second.
When assigning values to fields of DATETIME type, you can use string type or numeric type data to insert, as long as it conforms to the date format of DATETIME, as shown below.
The date expressed in 'YYYY-MM-DD HH:MM:SS' or 'YYYYMMDDHHMMSS' string format, the value range is '1000-01-01 00:00: 00'～'9999-12-3 23:59:59'. For example, if you enter '2014-12-31 05:05:05' or '20141231050505', the DATETIME value inserted into the database will be 2014-12-31 05:05:05.
A date represented in 'YY-MM-DD HH:MM:SS' or 'YYMMDDHHMMSS' string format, where YY represents a two-digit year value. Same as before, the year value in the range of '00~79' is converted to '2000~2079', and the year value in the range of '80~99' is converted to '1980~1999'. For example, if you enter '14-12-31 05:05:05', the DATETIME inserted into the database is 2014-12-31 05:05:05; if you enter 141231050505, the DATETIME inserted into the database is 2014-12-31 05:05:05 .
Date and time expressed in YYYYMMDDHHMMSS or YYMMDDHHMMSS numeric format. For example, if you enter 20141231050505, the DATETIME inserted into the database is 2014-12-31 05:05:05; if you enter 140505050505, the DATETIME inserted into the database is 2014-12-31 05:05:05.
Tip: MySQL allows "relaxed" syntax: any punctuation mark can be used as a separator between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11 30 35', '98/12/31 11*30*45' and '98@12@31 11^30^45' are Equivalently, these values can be correctly inserted into the database.
TIMESTAMP type
The display format of TIMESTAMP is the same as DATETIME, the display width is fixed at 19 characters, and the date format is YYYY-MM-DD HH:MM :SS, requires 4 bytes for storage. However, the value range of the TIMESTAMP column is smaller than the value range of DATETIME, which is '1970-01-01 00:00:01'UTC～'2038-01-19 03:14:07'UTC. When inserting data, make sure it is within the legal value range.
Tip: Coordinated Universal Time (English: Coordinated Universal Time, French: Temps Universel Coordonné) is also known as Universal Unified Time, World Standard Time, and International Coordinated Time. The English (CUT) and French (TUC) abbreviations are different, and as a compromise, the abbreviation is UTC.
TIMESTAMP and DATETIME, in addition to different storage bytes and supported ranges, the biggest difference is:
DATETIME When storing date data, It is stored according to the actual input format, that is, whatever is entered is stored, regardless of the time zone;
The TIMESTAMP value is stored in UTC (Universal Standard Time) format, and is stored correctly Convert to the current time zone, and then convert back to the current time zone when retrieving. That is, when querying, the displayed time value is different depending on the current time zone.
Tip: If you assign a DATE value to a DATETIME or TIMESTAMP object, the time portion of the resulting value is set to '00:00:00', so the DATE value does not contain time information. If a DATE object is assigned a DATETIME or TIMESTAMP value, the time portion of the resulting value is removed, so the DATE value contains no time information.
4. String type
The string type is used to store string data, and can also store binary data of pictures and sounds. Strings can be compared with case-sensitive or insensitive strings, and regular expression matching searches can also be performed.
The string types in MySQL include CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET, etc.
The following table lists the string data types in MySQL. The M in brackets indicates that the length can be specified.
| Type name | Description | Storage requirements |
|---|---|---|
| CHAR(M) | Fixed length non-binary string | M bytes, 1<=M<=255 |
| VARCHAR(M) | Variable length non-binary String | L 1 byte, where L<=M and 1<=M<=255 |
| TINYTEXT | very small Non-binary string | L 1 byte, where L<2^8 |
| Small non-binary string | L 2 bytes, where L<2 | ^#16 |
| Medium size non-binary string | L 3 bytes, where L<2^24 | |
| Large non-binary string | L 4 bytes, here, L<2^32 | |
| enumeration type, there can only be one enumeration string value | 1 or 2 bytes, depending on the number of enumeration values (maximum value is 65535) | |
| A setting, a string object can have zero or Multiple SET members | 1, 2, 3, 4 or 8 bytes, depending on the number of set members (up to 64 members) |
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| BIT(M) | 位字段类型 | 大约 (M+7)/8 字节 |
| BINARY(M) | 固定长度二进制字符串 | M 字节 |
| VARBINARY (M) | 可变长度二进制字符串 | M+1 字节 |
| TINYBLOB (M) | 非常小的BLOB | L+1 字节，在此，L<2^8 |
| BLOB (M) | 小 BLOB | L+2 字节，在此，L<2^16 |
| MEDIUMBLOB (M) | 中等大小的BLOB | L+3 字节，在此，L<2^24 |
| LONGBLOB (M) | 非常大的BLOB | L+4 字节，在此，L<2^32 |
BIT 类型
位字段类型。M 表示每个值的位数，范围为 1～64。如果 M 被省略，默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位，在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b'101'，其效果与分配 b'000101' 相同。
BIT 数据类型用来保存位字段值，例如以二进制的形式保存数据 13，13 的二进制形式为 1101，在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。
提示：默认情况下，MySQL 不可以插入超出该列允许范围的值，因而插入数据时要确保插入的值在指定的范围内。
BINARY 和 VARBINARY 类型
BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。使用的语法格式如下：
列名称 BINARY(M) 或者 VARBINARY(M)
BINARY 类型的长度是固定的，指定长度后，不足最大长度的，将在它们右边填充 “\0” 补齐，以达到指定长度。例如，指定列数据类型为 BINARY(3)，当插入 a 时，存储的内容实际为 “\a0\0”，当插入 ab 时，实际存储的内容为“ab\0”，无论存储的内容是否达到指定的长度，存储空间均为指定的值 M。
VARBINARY 类型的长度是可变的，指定好长度之后，长度可以在 0 到最大值之间。例如，指定列数据类型为 VARBINARY(20)，如果插入的值长度只有 10，则实际存储空间为 10 加 1，实际占用的空间为字符串的实际长度加 1。
BLOB 类型
BLOB 是一个二进制的对象，用来存储可变数量的数据。BLOB 类型分为 4 种：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB
【相关推荐：mysql视频教程】
The above is the detailed content of What are the mysql field types?. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
