MySQL 数据类型
目录
使用 MySQL 这么长时间了，经常使用的情况时，除了自增主键和日期时间之外 ，能 varchar 的基本上都给 varchar 了。团队内部还出现过 VARCHAR(2)、VARCHAT(4)、...、VARCHAT(2^N) 的情况，现在看来确实有点搞笑。
- MySQL 数据类型
- MySQL 用户管理
- MySQL 关键字查询
- MySQL 字符串函数
- MySQL 字符串连接函数
- MySQL 忘记 root 密码后重置
- MySQL 最值查询
- MySQL 日期时间函数
- MySQL 联合查询
- MySQL 查询当前年、季、月、周的第一天和最后一天
- MySQL 支持 emoji 图标的设置
- MySQL 存储引擎
- MySQL 隔离级别及其脏读不可重复读幻读
数据类型
- 数值类型
- 整数：
tinyint、smallint、mediumint、int、bigint - 小数：
float、double、real、decimal
- 整数：
- 日期和时间类型：
date、time、datetime、timestamp、year - 字符串类型
- 字符串：
char、varchat - 大文本：
tinytext、text、mediumtext、longtext - 其他：
enum、set
- 字符串：
- 二进制类型：
tinyblob、blob、mediumblob、longblob - 空间数据类型：
geometry、geometrycollection、point、linestring - JSON 数据类型：
主要类型
Data Type Storage Requirements
整数型
| 类型 | 存储空间(Bytes) | 有符号最小值与最大值 | 无符号最小值与最大值 |
|---|---|---|---|
tinyint | 1 | -128~127 或 -2^7~2^7-1 | 0~255 或 0~2^8-1 |
smallint | 2 | -32768~32767 或 -2^15~2^15-1 | 0~65535 或 0~2^16-1 |
mediumint | 3 | -8388608~8388607 或 -2^23~2^23-1 | 0~16777215 或 0~2^24-1 |
int | 4 | -2147483648~2147483647 或 -2^31~^31-1 | 0~4294967295 或 0~2^32-1 |
bigint | 8 | -9223372036854775808~9223372036854775807 或 -2^63~2^63-1 | 0~1.8446744e+19 或 0~2^64-1 |
浮点型
| 类型 | 存储空间(Bytes) | 有符号最小值与最大值 | 无符号最小值与最大值 |
|---|---|---|---|
float(M, D) | 4 | -3.402823466E+38~-1.175494351E-38 | 1.175494351E-38~3.402823466E+38 |
double(M, D) | 8 | -1.7976931348623157E+308~-2.2250738585072014E-308 | 0 和 2.2250738585072014E-308~1.7976931348623157E+308 |
定点型
这里我复制一下官方文档的解释：
The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:
- M is the maximum number of digits (the precision). It has a range of 1 to 65.
- D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.
If D is omitted, the default is 0. If M is omitted, the default is 10.
- 高精度的数据类型，常用来存储交易相关的数据
decimal(M, D)解释：M代表总精度（小数点前后数字总个数），D代表刻度（小数点后的位数）。比如：decimal(5, 2)表示存储范围为-999.99~999.99decimal(M, D)范围：1<= M <= 65、0 <= D <= 30- 如果省略
D，默认值是0。如果省略M，默认值是10 - 存储空间变长（
M+2bytes）
日期时间型
| 类型 | 存储空间(Bytes) | 格式 | 范围 |
|---|---|---|---|
year | 1 | YYYY | 1901~2155 |
date | 3 | YYYY-MM-DD | 1000-01-01~9999-12-31 |
time | 3 | hh:mm:ss | -838:59:59~838:59:59 |
datetime | 8 | yyyy-mm-dd hh:mm:ss | 1000-01-01 00:00:00~9999-12-31 23:59:59' |
timestamp | 4 | YYYYMMDDhhmmss | UTC: 1970-01-01 00:00:01~2038-01-19 03:14:07 |
包含两位数年份值的日期是模糊的，因为世纪是未知的。MySQL 使用这些规则解释两位数的年值：
00~69范围内的年份值被转换为2000~206970~99范围内的年份值被转换为1970~1999
字符串型
| 类型 | 说明 |
|---|---|
char(M) | 固定长度的字符串（可以包含字母，数字和特殊字符）。 M 参数指定以 字符 为单位的列长度（范围：0~255）。默认值为 1 |
varchat(M) | 长度可变的字符串（可以包含字母，数字和特殊字符）。 M 参数指定以 字符 为单位的最大列长度（范围：0~65525） |
binary(M) | 等于 char(M)，但存储二进制 字节 字符串。 M 参数指定以 字节 为单位的列长度。 默认为 1 |
varbinary | 等于 varchat(M)，但存储二进制 字节 字符串。 M 参数指定最大列长度。 |
tinyblob | 用户 BLOB（二进制大对象）。最大长度 255 个 字节 |
tinytext | 包含最大长度为 255（255B）个 字符 的字符串 |
blob(M) | 对于 BLOB（二进制大对象）。最多可容纳 65535 个 字节 的数据 |
text(M) | 容纳最大长度为 65535（64KB）个 字符 的字符串 |
mediumblob | 对于 BLOB（二进制大对象）。最多容纳 16777215 个 字节 的数据 |
mediumtext | 容纳最大长度为 16777215（16MB）个 字符 的字符串 |
longblob | 对于 BLOB（二进制大对象）。最多可容纳 4294967295 个 字节 的数据 |
longtext | 包含最大长度为 4294967295（4GB）个 字符 的字符串 |
enum('v1', 'v2', ...) | 从可能值列表中选择的只能有一个值的字符串对象。你可以在 ENUM 列表中最多列出 65535 个值。如果插入的值不在列表中，则将插入空白值。值按输入顺序排序 |
set(v1, v2, v3, ...) | 从可能值列表中选择的可以具有 0 个或多个值的字符串对象。你可以在 SET 列表中最多列出 64 个值 |
关于 char(M)、varchar(M) 和 text 的问题：
char(M)和varchar(M)中M代表 字符个数，并不是字节个数。当使用 UTF-8 时意味着可以插入M个字符，但实际会占用M*3个字节char(M)不管实际值都会占用M个字符的空间，页varchar(M)只会占用实际字符应该占用的空间+1，并且实际空间为+1 <= M- 超过
char(M)和varchar(M)的M设置后，字符串会被截断 char的上限为255字符，varchar和text的上限65535字符char在存储的时候会截断尾部的空格，varchar和text不会varchar会使用1~3个字节来存储长度，而text不会
| 值 | char(4) | 存储需要 | varchat(4) | 存储需要 |
|---|---|---|---|---|
'' | ' ' | 4 bytes | '' | 1 bytes |
'ab' | 'ab ' | 4 bytes | 'ab' | 3 bytes |
'abcd' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
'abcdefgh' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
如上所述：
char存定长，速度快，存在空间浪费的可能，会处理尾部空格，上限255varchar存变长，速度慢，不存在空间浪费，不处理尾部空格，上限65535，但是有存储长度实际65532最大可用text，存变长大数据，速度慢，不存在空间浪费，不处理尾部空格，上限65535，会用额外空间存放数据长度，因此可以全部使用65535