常用数据类型分类
- 数据值类型
- 字符串类型
- 二进制类型
- 日期类型
数据值类型
类型列表
| 类型 | 大小 | 说明 |
|---|---|---|
| BIT[(M)] | 默认bit | 位值类型。M表示每个值的位数，取值范围为1~64。如果省略M，则默认为1。 |
| TINYINT[(M)] | 1byte | 取值范围-2⁷~2⁷-1，无符号取值范围2⁸-1 |
| BOOL | 1byte | TINYINT(1)的同义词。值为零被认为是假，非零值被认为是true |
| SMALLINT[(M)] | 2byte | 取值范围-2¹⁵~2¹⁵-1，无符号取值范围2¹⁶-1 |
| MEDIUMINT[(M)] | 3byte | 取值范围-2²³~2²³-1，无符号取值范围2²⁴-1 |
| INT[(M)] | 4byte | 取值范围-2³¹~2³¹-1，无符号取值范围2³²-1 |
| INTEGER[(M)] | 4byte | INT[(M)]的同义词 |
| BIGINT[(M)] | 8byte | 取值范围-2⁶³~2⁶³-1，无符号取值范围2⁶⁴-1 |
| FLOAT[(M,D)] | 4byte | 单精度浮点型，M是总位数，D是小数点后面的位数，大约可以精确到小数点后7位 |
| DOUBLE[(M,D)] | 8byte | 双精度浮点型，M是总位数，D是小数点后面的位数，大约可以精确到小数点后15位 |
| DECIMAL[(M[,D])] | 动态 | 不存在精度损失，M是总位数(最大65)，D是小数点后的位数(最大30)。默认M=10,D=0。M不计算小数点和负号。D=0时值没有小数点和小数部分。 |
数据类型取值范围
| 类型 | 大小 | 有符号最小值 | 有符号最大值 | 无符号最小值 | 无符号最大值 |
|---|---|---|---|---|---|
| TINYINT | 1Byte | -128 | 127 | 0 | 255 |
| SMALLINT | 2Byte | -32,768 | 32,767 | 0 | 65,535 |
| MEDIUMINT | 3Byte | -8,388,608 | 8,388,607 | 0 | 16,777,215 |
| INT | 4Byte | -2,147,483,648 | 2,147,483,647 | 0 | 4,294,967,295 |
| BIGINT | 8Byte | -2⁶³ | 2⁶³-1 | 0 | 2⁶⁴-1 |
字符串类型
类型列表
字符串类型
| 类型 | 说明 |
|---|---|
| CHAR[(M)] | 固定长度字符串，M表示长度(字符数)，范围0~255，省略M则长度为1 |
| VARCHAR(M) | 可变长度字符串，M表示最大字符长度，范围0~65535，实际存储取决于字符集 |
| TINYTEXT | 最大长度255(2⁸-1)个字符，实际存储取决于字符集 |
| TEXT[(M)] | 最大长度65535(2¹⁶-1)个字符，实际存储取决于字符集 |
| MEDIUMTEXT | 最大长度16,777,215(2²⁴-1)个字符，实际存储取决于字符集 |
| LONGTEXT | 最大长度4,294,967,295(4GB/2³²-1)个字符，实际存储取决于字符集 |
二进制类型
| 类型 | 说明 |
|---|---|
| BINARY[(M)] | 固定长度二进制数据，类似CHAR但存储字节，M范围0~255，省略M则长度为1 |
| VARBINARY(M) | 可变长度二进制数据，类似VARCHAR但存储字节 |
| TINYBLOB | 最大长度255(2⁸-1)字节 |
| BLOB[(M)] | 最大长度65535(2¹⁶-1)字节 |
| MEDIUMBLOB | 最大长度16,777,215(2²⁴-1)字节 |
| LONGBLOB | 最大长度4,294,967,295(4GB/2³²-1)字节 |
特殊类型
ENUM(‘value1’,‘value2’,…)
- 枚举类型，从预定义值列表中选择一个值
- 最多65,535个不同元素
- 单个元素最大长度：M≤255或(M×w)≤1020（M为字符长度，w为字符最大字节数）
- 内部存储为整数
SET(‘value1’,‘value2’,…)
- 集合类型，从预定义值列表中选择零个或多个值
- 最多64个元素
- 单个元素最大长度：M≤255或(M×w)≤1020
- 内部存储为整数
关于排序
- 字符串类型的列以字符为单位，并且可以单独指定字符集和排序规则，⽐如字符集是 utf8mb4 ， 排序规则是 utf8_0900_ai_ci
- ⼆进制的列以字节为单位，可以指定_bin结尾的排序规则，⽐如排序规则是 utf8mb4_bin ，这 时以⽐较和排序基于数字字符代码值
CHAR与VARCHAR的区别
| 特性 | CHAR | VARCHAR |
|---|---|---|
| 存储方式 | 固定长度 | 可变长度 |
| 空间占用 | 总是占用M个字符的空间（不足补空格） | 只占用实际字符长度+长度标识(1-2字节) |
| M的含义 | 严格分配的字符数（0-255） | 最大允许的字符数（0-65535） |
| 省略M时 | 默认为1 | 必须指定M值 |
| 存储效率 | 定长数据效率高 | 变长数据效率高 |
| 适用场景 | 存储长度固定的数据（如MD5、UUID） | 存储长度变化大的数据（如用户名、地址） |
| 最大长度 | 255字符 | 65535字符（实际受行大小限制约65532） |
| 尾部空格处理 | 存入时会补足空格，取出时自动去除 | 保留原始空格 |
| 索引效率 | 定长特性使索引效率更高 | 相对CHAR略低 |
| 内存分配 | 预分配固定内存 | 动态分配内存 |
使用建议
-
优先使用VARCHAR：
- 大多数情况下VARCHAR更节省空间
- 特别是当数据长度变化较大时
-
使用CHAR的情况：
1 2 3 4 5 6-- 适合CHAR的场景 CREATE TABLE users ( id INT, gender CHAR(1), -- 固定1字符(M/F) password_hash CHAR(32) -- 固定32字符的MD5 );
VARCHAR与TEXT的区别
| 特性 | VARCHAR | TEXT |
|---|---|---|
| 基本类型 | 可变长度字符串 | 文本大对象 |
| 最大长度 | 65,535字符（受行大小限制） | 65,535字符（TEXT）~4GB（LONGTEXT） |
| 存储方式 | 行内存储（通常） | 行外存储（溢出页） |
| 默认值 | 支持设置默认值 | 不支持默认值（MySQL 8.0前） |
| 索引限制 | 可建完整索引 | 必须指定前缀长度（如TEXT(100)） |
| 内存处理 | 全内容加载到内存 | 按需加载 |
| 排序限制 | 可用作完整排序键 | 只能按前缀长度排序 |
| 临时表处理 | 始终使用内存临时表 | 可能使用磁盘临时表 |
| 字符集影响 | 影响最大可用长度 | 影响存储空间但长度限制固定 |
| 典型用途 | 姓名、地址等常规字符串 | 文章内容、日志等大文本 |
详细对比
-
存储机制
- VARCHAR：通常存储在行内（对于短字符串）
- TEXT：内容超过768字节时会使用溢出页存储
-
性能影响
1 2 3 4 5-- VARCHAR示例（高效） SELECT * FROM users WHERE username = 'admin'; -- 用户名适合VARCHAR -- TEXT示例（需注意） SELECT * FROM articles WHERE content LIKE '%MySQL%'; -- 大文本搜索效率低
日期类型
类型列表
| 类型 | 大小 | 说明 | 0值/默认值 |
|---|---|---|---|
| TIMESTAMP[(fsp)] | 4 bytes | • 时间戳类型 • 支持范围： 1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.499999• 显示格式： YYYY-MM-DD hh:mm:ss[.fraction] |
0000-00-00 00:00:00 |
| DATETIME[(fsp)] | 8 bytes | • 日期和时间组合 • 支持范围： 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999• 显示格式： YYYY-MM-DD hh:mm:ss[.fraction] |
0000-00-00 00:00:00 |
| DATE | 3 bytes | • 纯日期类型 • 支持范围： 1000-01-01 ~ 9999-12-31• 显示格式： YYYY-MM-DD |
0000-00-00 |
| TIME[(fsp)] | 3 bytes | • 纯时间类型 • 支持范围： -838:59:59.000000 ~ 838:59:59.000000• 显示格式： hh:mm:ss[.fraction] |
00:00:00 |
| YEAR[(4)] | 1 byte | • 4位年份格式 • 支持范围： 1901 ~ 2155• 显示格式： YYYY |
0 |
关键说明
- fsp参数：表示小数秒精度（0-6），如
TIMESTAMP(3)支持毫秒精度 - TIMESTAMP特性：
- 自动转换为UTC存储，检索时转回当前时区
- 受2038年问题限制（最大到2038-01-19）
- DATETIME优势：
- 更大时间范围（1000-9999年）
- 不受时区影响
- 特殊范围：
- TIME类型支持
-838:59:59到838:59:59，可用于处理时间间隔
- TIME类型支持
- 零值处理：
- 所有日期时间类型的零值在SQL模式严格时会报错
其他
MySQL 时间函数与参数说明
fsp参数
- 可选设置，用于指定小数秒精度
- 取值范围：0到6
- 值为0表示没有小数部分
- 如果省略，默认精度为0
日期时间函数同义词
| 函数 | 等效函数 | 功能描述 |
|---|---|---|
| CURRENT_DATE | CURDATE() | 获取当前日期 |
| CURRENT_DATE() | CURDATE() | 获取当前日期 |
| CURRENT_TIME | CURTIME() | 获取当前时间 |
| CURRENT_TIME([fsp]) | CURTIME([fsp]) | 获取当前时间（可指定小数秒精度） |
| CURRENT_TIMESTAMP | NOW() | 获取当前日期和时间 |
| CURRENT_TIMESTAMP([fsp]) | NOW([fsp]) | 获取当前日期和时间（可指定小数秒精度） |