Mysql数据类型的归纳与选择

猫坚果NutCat
290 阅读5分钟

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第2天，点击查看活动详情。

Mysql数据类型的归纳与选择

工作中有很多设计表的时候，这时我就会根据项目需求思考下字段需要使用什么数据类型。从开发的角度来说，我们需要为一个字段选择其合适的数据类型，但有时候也会迷糊应该设置什么数据类型，应该给多大的长度。如果使用错误的数据类型可能会严重影响应用程序的功能和性能，因此在建表过程中，字段的数据类型尤为重要，更改包含数据的列的数据类型并不是一件轻松的事情，有可能造成精度丢失甚至数据丢失。今天我就归纳一下Mysql的数据类型，并聊一聊该如何选择。

MySQL的数据类型大概可以分为以下4种，分别时数值类型、日期和时间类型、字符串类型和二进制类型：

  1. 数值类型

    数值类型又可以分为整数类型和浮点数类型：

    • 整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT；
    • 浮点数类型包括 FLOAT 和 DOUBLE；
    • 另外还有一个DECIMAL，DECIMAL数据类型用于在数据库中存储精确的数值。

  2. 日期和时间类型

    有 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP等。

  3. 字符串类型

    包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。

  4. 二进制类型

    包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

数值类型

整数类型

不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此应根据实际需要选择最合适的类型，这样有利于提高查询的效率和节省存储空间。

类型名称说明大小范围（有符号/无符号）
TINYINT很小的整数值1个字节(−27, 27−1) / (0, 28-1)
SMALLINT小的整数2个宇节(−215, 215−1) / (0, 216-1)
MEDIUMINT中等大小的整数3个字节(−231, 231−1) / (0, 232-1)
INT (INTEGHR)普通大小的整数4个字节(−263, 263−1) / (0, 264-1)
BIGINT大整数8个字节(−2127, 2127−1) / (0, 2128-1)

  • 整数类型的字段可以添加 AUTO_INCREMENT 自增约束条件；

  • 整数类型有可选的UNSIGNED属性，标识不允许负值，因为去掉了符号位，这大致可以让上限提升一倍，这就是上表有有符号/无符号两种范围的原因；

  • 在一些建表语句中，我们会看见例如int(5)的整数类型(M)这种写法，这种写法中，M表示select查询结果的显示宽度，并不会影响实际取值范围；

    • 这个语句没啥用，还容易误导人......

  • 最常用的是INT类型；

  • 举个例子：

    CREATE TABLE students(
	id INT(10) PRIMARY KEY,
	age INT(6)
);

  • 整数类型分为5种，但我们可以看到，BIGINT即使是有符号，其最大值9223372036854775807也已经是个天文数字了，9223372036854775807byte = 8589934592GB = 8388608TB = 8192PB。整数类型也是我们设计表中最常使用到的类型之一。

    • 一个0/1枚举字段，我们用TINYINT即可。如果你用了BIGINT，每个数据，就浪费了7bytes， 100W 的数据量，这个列就会浪费 700W bytes，大约6.7M

浮点数类型

可以用浮点数类型(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数，如超过D位则四舍五入。

  • M是表示有效数字数的精度。 M范围为1〜65。
  • D是表示小数点后的位数。 D的范围是0~30。MySQL要求D <= M。
类型名称说明存储需求
FLOAT单精度浮点数4 个字节
DOUBLE双精度浮点数8 个字节

  • FLOAT 类型的取值范围如下：

    • 有符号的取值范围：-3.402823466E+38～-1.175494351E-38。
    • 无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38。

  • DOUBLE 类型的取值范围如下：

    • 有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。
    • 无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。

  • 两者在精度上区别就很大，FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定）；

  • 浮点数能够表示的范围很大，但是浮点数的缺点是会引起精度问题；

  • 举个例子：

    CREATE TABLE point(
	x FLOAT(3,1),
	y DOUBLE(5,3)
);

DECIMAL（定点类型）

既然浮点数会引起精度问题，那么有没有方法能避免这个问题呢。

如果我们的场景对精度要求很高，那此时就需要使用定点类型DECIMAL了。

float、double类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据，而decimal无论写入数据中的数据是多少，都不会存在精度丢失问题。它常用于货币、金融等对小数点后的数字比较敏感的系统中，比如价格，工资，账户余额，在银行、互联网金融中很常使用。

  • 使用方式和浮点数类型一样，都是column_name DECIMAL(M,D);

  • 与INT数据类型一样，DECIMAL类型也具有UNSIGNED和ZEROFILL属性。 如果使用UNSIGNED属性，则DECIMAL UNSIGNED的列将不接受负值。

    如果使用ZEROFILL，MySQL将把显示值填充到0以显示由列定义指定的宽度;

  • 举个例子：

    CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(255),
    cost DECIMAL(19,4) NOT NULL
);

float/double/decimal的区别

  1. float/double存在着精度丢失问题，但decimal则不会；
  2. float/double在db中存储的是近似值，而decimal则是以字符串形式进行保存的；
  3. decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度来处理而decimal在不指定M、D时默认为decimal(10, 0)。

日期和时间类型

类型名称日期格式日期范围存储需求
YEARYYYY1901 ~ 21551 个字节
TIMEHH:MM:SS-838:59:59 ~ 838:59:593 个字节
DATEYYYY-MM-DD1000-01-01 ~ 9999-12-33 个字节
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:598 个字节
TIMESTAMPYYYY-MM-DD HH:MM:SS1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC4 个字节

YEAR

  • 较少使用的类型；
  • 以 4 位字符串或者 4 位数字格式表示的 YEAR，输入格式为 'YYYY' 或者 YYYY，比如输入 '2010' 或 2010，插入数据库的值均为 2010；
  • 2位数存入YEAR值，会根据其值被mysql转换为对应的年份，不推荐存入2位数的，很容易出事故。

TIME

  • TIME 类型用于只需要时间信息的值；
  • 我们可以看到TIME的取值范围是-838:59:59 ~ 838:59:59，这说明TIME 类型不仅可以用于表示一天的时间，还可能是某个事件过去的时间或两个事件之间的时间间隔，但不推荐这么用；
  • 可以使用HH:MM:SS, HH:MM, D HH:MM:SS, HHMMSS等格式存储：
    • D 表示日
    • 如果是个没有意义的时间，会变为00:00:00
    • 如果使用HHMM这种只有两个时间的类型，最右边的两位会被识别为秒，如1112，并不是11：12：00而是00：11：12

DATE

DATE 类型用于仅需要日期值时，没有时间部分，格式YYYY-MM-DD

DATETIME

DATETIME 类型用于需要同时包含日期和时间信息的值。

  • 以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期，取值范围为 '1000-01-01 00：00：00'～'9999-12-3 23：59：59'。例如，输入 '2014-12-31 05：05：05' 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。
  • 不推荐YYYY写成YY，虽然也会被识别但容易出错

TIMESTAMP

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符。

TIMESTAMP 与 DATETIME 的区别：

  • DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；
  • 而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的;
  • 由于大小的区别，datetime与timestamp能存储的时间范围也不同，datetime的存储范围为1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp存储的时间范围为19700101080001——20380119111407;
  • datetime默认值为空，当插入的值为null时，该列的值就是null；timestamp默认值不为空，当插入的值为null的时候，mysql会取当前时间。

在实际工作中，一张表往往我们会有两个默认字段，一个记录创建时间而另一个记录最新一次的更新时间，这种时候可以使用timestamp类型来实现：

create_time timestamp default current_timestamp comment "创建时间",
update_time timestamp default current_timestamp on update current_timestamp comment "修改时间",

字符串类型

这里我们最常用到的有CHAR、VARCHAR，特定场景发挥很大作用的还有TEXT、ENUM，我们以这几个类型展开聊聊。

类型名称说明存储需求
CHAR(M)固定长度非二进制字符串M 字节，1<=M<=255
VARCHAR(M)变长非二进制字符串L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT非常小的非二进制字符串L+1字节，在此，L<2^8
TEXT小的非二进制字符串L+2字节，在此，L<2^16
MEDIUMTEXT中等大小的非二进制字符串L+3字节，在此，L<2^24
LONGTEXT大的非二进制字符串L+4字节，在此，L<2^32
ENUM枚举类型，只能有一个枚举字符串值1或2个字节，取决于枚举值的数目 (最大值为65535)
SET一个设置，字符串对象可以有零个或 多个SET成员1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

CHAR 和 VARCHAR 类型

先看看定义：

  • char：固定长度的字符串，当保存时，在右侧填充空格以达到指定的长度。char(M):M 表示列的长度，范围是 0～255 个字符。
  • varchar: 长度可变的字符串。varchar(M):M 表示列的长度，范围是 0～65535 个字符。

两者的区别：

  • char长度固定、varchar的长度是可变；

    • MySQL处理char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会。
    插入值CHAR(4)存储需求VARCHAR(4)存储需求
    ' '' '4字节''1字节
    'ab''ab '4字节'ab'3字节
    'abc''abc '4字节'abc'4字节
    'abcd''abcd'4字节'abcd'5字节
    'abcdef''abcd'4字节'abcd'5字节

  • char类型每次修改的数据长度相同，效率更高；varchar类型每次修改的数据长度不同，效率更低；

  • char类型存储的时候是初始预计字符串再加上一个记录字符串长度的字节，占用空间较大；varchar类型存储的时候是实际字符串再加上一个记录字符串长度的字节，占用空间较小。

使用场景：

  • 通过 char(m) 声明的字段，数据库就一定会占用这么长字节的位置，即便实际字符串只有寥寥数位；好处是会方便 mysql 进行数据检索，不会产生碎片空间，所以推荐对可预见长度的字符类型使用 char 来定义，比如 身份证、电话、姓名、银行卡、物料编码 等等。
  • 使用 varchar(n) 声明的字段，默认情况下，不会有空间浪费的情况。比如定义字段可用 10 位，实际数据仅使用 1 位，mysql 内部也只用 1 位，不会去占用另外的 9 位；好处是对空间的利用率较高，但若数据不断变化调整，mysql 内部就需要将数据转移到新的空间，因为原本并没有占用预留的空间。如此往复，空间的碎片率就会非常高。
  • 总结来说：定长字符类型建议用 char；不定长并且不怎么变化的数据类型，建议用 varchar；如果数据改动频繁，长度又是参差不齐，要么空间换时间用 char，要么时间换空间用 varchar。

其他类型

  • TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

  • ENUM 类型是一个字符串对象，值为表创建时列规定中枚举的一列值。其语法格式如下：

    <字段名> ENUM( '值1', '值1', …, '值n' )

关于varchar(m)

二进制类型

类型名称说明存储需求
BIT(M)位字段类型大约 (M+7)/8 字节
BINARY(M)固定长度二进制字符串M 字节
VARBINARY (M)可变长度二进制字符串M+1 字节
TINYBLOB (M)非常小的BLOBL+1 字节，在此，L<2^8
BLOB (M)小 BLOBL+2 字节，在此，L<2^16
MEDIUMBLOB (M)中等大小的BLOBL+3 字节，在此，L<2^24
LONGBLOB (M)非常大的BLOBL+4 字节，在此，L<2^32

这里主要说一下blob类型。

varchar、text和blob：

  • text和blob，都是为了存储大对象使用的；
  • 当varchar(M)的M大于某些数值时，varchar会自动转为text，如M>255时转为tinytext，M>500时转为text，M>20000时转为mediumtext；
  • 单行64K即65535字节的空间，varchar只能用63352/65533个字节，但是text可以65535个字节全部用起来；
  • text可以指定text(M)，但是M无论等于多少都没有影响；
  • text不允许有默认值，varchar允许有默认值；
  • varchar比text效率高，所以能用varchar就优先用varchar，不要用text；
  • text存储的是字符串而blob存储的是二进制字符串，blob更多用于存储例如图片、音视频这种文件的二进制数据的。

数据类型的属性

MySQL关键字含义
NULL数据列可包含NULL值
NOT NULL数据列不允许包含NULL值
DEFAULT默认值
PRIMARY KEY主键
AUTO_INCREMENT自动递增，适用于整数类型
UNSIGNED无符号
CHARACTER SET name指定一个字符集
avatar
文章
阅读
粉丝