Mysql常用数据类型分为三大类:
一、数字类型:
数字类型又包括整型,浮点型,定点型,位值类型。
(1)整型包括以下类型:TINYINT, SMALLINT,MEDIUMINT,INT,and BIGINT
Type | Storage | Minimum Value | Maximum Value |
| (Bytes) | (Signed/Unsigned) | (Signed/Unsigned) |
TINYINT | 1 | -128 | 127 |
|
| 0 | 255 |
SMALLINT | 2 | -32768 | 32767 |
|
| 0 | 65535 |
MEDIUMINT | 3 | -8388608 | 8388607 |
|
| 0 | 16777215 |
INT | 4 | -2147483648 | 2147483647 |
|
| 0 | 4294967295 |
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
|
| 0 | 18446744073709551615 |
(2)浮点型包括以下类型:FLOAT, DOUBLE
MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
(3)定点类型:DECIMAL ,NUMERIC
DECIMAL 和 NUMERIC 类型存储精度计算类型. 它们被用来进行精准的计算,譬如说货币的计算。
(4)位值类型:BIT
二、日期和时间类型:DATE,TIME,DATETIME,TIMESTAMP,YEAR
Data Type | Storage Required Before MySQL 5.6.4 | Storage Required as of MySQL 5.6.4 |
1 byte | 1 byte | |
3 bytes | 3 bytes | |
3 bytes | 3 bytes + fractional seconds storage | |
8 bytes | 5 bytes + fractional seconds storage | |
4 bytes | 4 bytes + fractional seconds storage |
存储大小见上图所示,MySQL 5.6.4以前的大小和MySQL 5.6.4有所不同。
三.字符串类型:包括 CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET
In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.
Data Type | Storage Required |
CHAR(M) | M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set. See Section 14.8.1.2, “The Physical Row Structure of an InnoDB Table” for information about CHAR data type storage requirements for InnoDB tables. |
BINARY(M) | M bytes, 0 <= M <= 255 |
VARCHAR(M), VARBINARY(M) | L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes |
L + 1 bytes, where L < 28 | |
L + 2 bytes, where L < 216 | |
L + 3 bytes, where L < 224 | |
L + 4 bytes, where L < 232 | |
ENUM('value1','value2',...) | 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
SET('value1','value2',...) | 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) |
Variable-length string types are stored using a length prefix plus data. The length prefix requires from one to four bytes depending on the data type, and the value of the prefix is L (the byte length of the string). For example, storage for a MEDIUMTEXT value requires L bytes to store the value plus three bytes to store the length of the value
四.mysql还有空间类型,json类型等数据类型,这些在特定场合下用到的就不介绍了。
引自:mysql官网