MySQL数据类型

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。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏吾爱乐享

软件测试之学习mysql的查询功能select及高级查询(重中之重)

1252
来自专栏数据和云

深入剖析:认识Oracle 中的 NULL 值

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 经常看到很多人提出和NULL有关的问题。N...

3035
来自专栏与神兽党一起成长

[MySQL]查询学生选课的情况(二)

上一篇文章使用了自定义的函数查找出来选择Math,或者选择Chinese或选择Japanese课程的学生。

1650
来自专栏java初学

mysql入门 — (2)

3578
来自专栏王磊的博客

MongoDB Query 的几个方法

Query.All("name", "a", "b");//通过多个元素来匹配数组 Query.And(Query.EQ("name", "a"), Query...

3068
来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------单行函数

SQL> select lower('Hello World') 转小写,upper('Hello World') 转大写,initcap('hello wor...

770
来自专栏乐沙弥的世界

PL/SQL 联合数组与嵌套表

      通常情况下,在PL/SQL中,处理单行单列的数据可以使用标量变量,而处理单行多列的数据则使用PL/SQL记录是不错的选择。单列多行数据 则由联合数组...

1143
来自专栏LanceToBigData

MySQL(四)之MySQL数据类型

一、数据类型概述  MySQL的数据类型有大概可以分为5种,分别是 整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型。现在可以来看看你对...

24010
来自专栏闻道于事

Hibernate框架HQL语句

这篇随笔将会记录hql的常用的查询语句,为日后查看提供便利。 在这里通过定义了三个类,Special、Classroom、Student来做测试,Special...

3055
来自专栏祥子的故事

sql | 基础总结 | 思维导图

3976

扫码关注云+社区

领取腾讯云代金券