美文网首页程序员
MySQL数据类型

MySQL数据类型

作者: linjinhe | 来源:发表于2016-05-21 22:15 被阅读655次

    MySQL不同存储引擎可能会有不同。下面的内容以InnoDB为主。

    选择数据类型的步骤

    1. 确定合适的大类型:数字、字符串、时间、二进制
    2. 确定具体的类型:有无符号、取值范围、变长定长等。

    整数类型

    类型 字节数 范围
    TNIYINT 1 -128~127
    SMALLINT 2 -32767~32768
    MEDIUMINT 3 -8388608~8388607
    INT 4 -2147483648~2147483647
    BIGINT 8 -9223372036854775808~9223372036854775807
    • 每个整数类型都有对应的无符号(UNSIGNED)类型。
    • 建议使用TINYINT代替ENUM。
    • 避免使用整数的显示宽度。

    实数类型

    类型 字节 备注
    FLOAT 4 单精度浮点数
    DOUBLE 8 双精度浮点数
    DECIMAL 可变 高精度定点数
    • DECIMAL只是一种存储格式,MySQL以二进制的合适存储DECIMAL类型的列。在计算中,DECIMAL会转换成DOUBLE。
    • 不建议指定浮点数的精度。
    • 不建议使用DECIMAL。
    • 建议要存储的实数乘以相应的倍数,使用整数类型运算和存储。

    字符串类型

    VARCHAR vs CHAR

    类型 最大长度 备注
    CHAR(size) 255字节 定长。size指定的是字符数,不是字节数。
    VARCHAR(size) 65532字节 变长。size指定的是字符数,不是字节数。
    • 适用VARCHAR:

      • 字符串地最大长度比平均长度大很多;
      • 列更新很少,所以碎片不是问题;
      • 使用像UTF8这样复杂地字符集,每个字符都可能使用不同的字节数进行存储。
    • 适用CHAR:

      • 短字符串;
      • 所有值都接近一个长度;
      • 经常变更的列,这样不易产生碎片;
    • CHAR类型的列,原字符串末尾的空格会被“干掉”,再填充空格。MySQL检索CHAR不会使用末尾的空格。(列是定长的,MySQL没有存储写入的字符串有多长,只好一刀切,末尾的空格都忽略掉。)

    • VARCHAR末尾的空格不会被“干掉”,检索的时候会用到。(MySQL存储了写入的字符串的长度,这样可以知道字符串末尾有多少各空格是你写入的。)

    • VARCHAR(5)和VARCHAR(200),如果都只存了"abc",它们有什么不同呢?实际上,MySQL会分配固定大小地内存块来保存内部值,因此VARCHAR(200)的列尽管只存了和VARCHAR(5)一样的字符串,但是分配的内存可能会大得多。

    BINARY vs VARBINARY

    类型 最大长度 备注
    BINARY 255 定长
    VARBINARY 65535 变长
    • BINARY和VARBINARY与CHAR和VARCHAR非常类似。
    • BINARY和VARBINARY存储的是二进制字符串,与字符集无关。
    • BINARY的末尾会被填充\0,并且会加入检索。

    BLOB vs TEXT

    L表示数据的长度。
    L+x表示存储需要的空间。

    类型 存储
    TINYBLOB L+1 bytes, L < 2^8
    SMALLBLOB/BLOB L+2 bytes, L < 2^16
    MEDIUMBLOB L+3 bytes, L < 2^24
    LONGBLOB L+4 bytes, L < 2^32
    TINYTEXT L+1 bytes, L < 2^8
    SMALLTEXT/TEXT L+2 bytes, L < 2^16
    MEDIUMTEXT L+3 bytes, L < 2^24
    LONGTEXT L+4 bytes, L < 2^32
    • BLOB系列存储二进制字符串,与字符集无关。
    • TEXT系列存储非二进制字符串,与字符集相关。
    • 一般情况下,你可以认为BLOB是一个更大的VARBINARY;TEXT是一个更大的VARCHAR。
    • MySQL只能对BLOB和TEXT的前面max_sort_length各字符进行排序和索引。
    • BLOB和TEXT都不能有default value。
    • 当BLOB和TEXT的长度太大时,InnoDB会使用专门的“外部”存储区域来进行存储。

    日期和时间类型

    类型 大小
    TIMESTAMP 4字节
    DATETIME 8字节
    • MySQL能存储的最小时间粒度为秒。
    • TIMESTAMP是UTC时间戳,与时区相关。
    • DATETIME的存储格式是一个YYYYMMDDHHmmSS的整数,与时区无关,你存了什么,读出来就是什么。
    • DATETIME的存储范围大于TIMESTAMP。
    • TIMESTAMP的列可以自动更新。
    • 除非有特殊需求,否则建议使用TIMESTAMP。

    ENUM、SET、BIT

    • ENUM列允许在列中存储一组定义值中的单个值。
    • SET列允许在列中存储一组定义值中的一个或多个值。
    • BIT在InnoDB中其实是一个最小的整数类型。而MySQL在检索BIT的时候会将其当做字符串,而不是整数,这可能会导致一些奇怪的行为。
    • 不建议使用这三个类型:用整数代替。

    一些原则

    • 选择最小的满足需求的数据类型。

    一般情况下,应该尽量使用可以正确存储数据的最小数据类型。
    简单就好。

    比如,用MySQL的内建类型date, time, datetime来存储时间,而不是使用字符串;用INT UNSIGNED来存储IPv4地址。

    如何存储IPv6的地址?IPv6地址128bit,MySQL最大的整数类型BIGINT只有64bit。可以将其存储成定长(16字节)的二进制字符

    • 尽量避免使用NULL。

    相关文章

      网友评论

        本文标题:MySQL数据类型

        本文链接:https://www.haomeiwen.com/subject/orfbrttx.html