Jinyun's Notes

没什么天赋,爱好也不多,但愿坚持做些喜欢的事情

0%

MySQL 数据类型

201911171605.jpg

👋 使用 MySQL 这么长时间了,经常使用的情况时,除了自增主键和日期时间之外 ,能 Varchar 的基本上都给 Varchar 了。团队内部还出现过 VARCHAR(2)、VARCHAT(4)、…、VARCHAT(2^N) 的情况,现在看来确实有点搞笑。

数据类型

  • 数值类型
    • 整数:tinyintsmallintmediumintintbigint
    • 小数:floatdoublerealdecimal
  • 日期和时间类型:datetimedatetimetimestampyear
  • 字符串类型
    • 字符串:charvarchat
    • 大文本:tinytexttextmediumtextlongtext
    • 其他:enumset
  • 二进制类型:tinyblobblobmediumbloblongblob
  • 空间数据类型:geometrygeometrycollectionpointlinestring
  • JSON 数据类型:

各个类型详情

Data Type Storage Requirements

整型

类型存储空间(Bytes)有符号最小值与最大值无符号最小值与最大值
tinyint1-128~127-2^7~2^7-10~2550~2^8-1
smallint2-32768~32767-2^15~2^15-10~655350~2^16-1
mediumint3-8388608~8388607-2^23~2^23-10~167772150~2^24-1
int4-2147483648~2147483647-2^31~^31-10~42949672950~2^32-1
bigint8-9223372036854775808~9223372036854775807-2^63~2^63-10~1.8446744e+190~2^64-1

浮点型

类型存储空间(Bytes)有符号最小值与最大值无符号最小值与最大值
float(M, D)4-3.402823466E+38~-1.175494351E-381.175494351E-38~3.402823466E+38
double(M, D)8-1.7976931348623157E+308~-2.2250738585072014E-30802.2250738585072014E-308~1.7976931348623157E+308

定点型

DECIMAL 范围

这里我复制一下官方文档的解释:

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.99
  • decimal(M, D) 范围:1<= M <= 650 <= D <= 30
  • 如果省略 D,默认值是 0。如果省略 M,默认值是 10
  • 存储空间变长(M+2 bytes)

日期与时间型

类型存储空间(Bytes)格式范围
year1YYYY1901~2155
date3YYYY-MM-DD1000-01-01~9999-12-31
time3hh:mm:ss-838:59:59~838:59:59
datetime8yyyy-mm-dd hh:mm:ss1000-01-01 00:00:00~9999-12-31 23:59:59'
timestamp4YYYYMMDDhhmmssUTC: 1970-01-01 00:00:01~2038-01-19 03:14:07

包含两位数年份值的日期是模糊的,因为世纪是未知的。MySQL 使用这些规则解释两位数的年值:

  • 00~69 范围内的年份值被转换为 2000~2069
  • 70~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 字符,varchartext 的上限 65535 字符
  • char 在存储的时候会截断尾部的空格,varchartext 不会
  • 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 存定长,速度快,存在空间浪费的可能,会处理尾部空格,上限 255
  • varchar 存变长,速度慢,不存在空间浪费,不处理尾部空格,上限 65535,但是有存储长度实际 65532 最大可用
  • text,存变长大数据,速度慢,不存在空间浪费,不处理尾部空格,上限 65535,会用额外空间存放数据长度,因此可以全部使用 65535
本笔记是笔者在学习和工作中的一些整理,如对您有用,请鼓励我继续写作