Home MySQL中常用数据类型数据范围
Post
Cancel

MySQL中常用数据类型数据范围

Posted Jul 29, 2023
By 李杭
11 min read

[TOC]

MySQL数据类型

官方文档

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

下表来自 MySQL官方文档

Data TypeStorage Required
TINYINT1 byte
SMALLINT2 bytes
MEDIUMINT3 bytes
INT, INTEGER4 bytes
BIGINT8 bytes
FLOAT(*p*)4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT4 bytes
DOUBLE [PRECISION], REAL8 bytes
DECIMAL(*M*,*D*), NUMERIC(*M*,*D*)Varies; see following discussion
BIT(*M*)approximately (M+7)/8 bytes
类型大小范围（有符号）范围（无符号）用途
TINYINT1 Bytes(-128，127)(0，255)小整数值
SMALLINT2 Bytes(-32 768，32 767)(0，65 535)大整数值
MEDIUMINT3 Bytes(-8 388 608，8 388 607)(0，16 777 215)大整数值
INT或INTEGER4 Bytes(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
BIGINT8 Bytes(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值
FLOAT4 Bytes(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度 浮点数值
DOUBLE8 Bytes(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度 浮点数值
DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值
Data TypeStorage Required Before MySQL 5.6.4Storage Required as of MySQL 5.6.4
YEAR1 byte1 byte
DATE3 bytes3 bytes
TIME3 bytes3 bytes + fractional seconds storage
DATETIME8 bytes5 bytes + fractional seconds storage
TIMESTAMP4 bytes4 bytes + fractional seconds storage

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型大小 ( bytes)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/’838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME8‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’YYYY-MM-DD hh:mm:ss混合日期和时间值
TIMESTAMP4‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYY-MM-DD hh:mm:ss混合日期和时间值，时间戳

字符类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型大小用途
CHAR0-255 bytes定长字符串
VARCHAR0-65535 bytes变长字符串
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

数据类型后面的长度(M)

在MySQL中，可以在类型后面指定长度的有：char(M)、varchar(M)、tinyint(M)、 smallint(M)、 mediumint(M)、 int(M)、 bigint(M)、 double(M, D)、 decimal(M, D)、 float(M, D)

如： char(8)、 varchar(100)、 tinyint(2)、 int(4)、 double(8,2)、 decimal(6,2)

根据数据类型我们分类说明M的含义

  • 字符型

    针对字符型的数据类型，M表示：可保存的最大字符长度

    • char

      A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1

      翻译:固定长度的字符串，存储时总是以空格向右填充指定长度。M 表示列长度（以字符为单位）。M 的范围是 0 至 255。如果省略 M，则长度为 1

    • varchar

    A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used

    For example, utf8mb3 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8mb3 character set can be declared to be a maximum of 21,844 characters

    翻译：长度可变的字符串。M 表示列的最大长度（以字符为单位）,M 的范围是 0 到 65,535。VARCHAR 的有效最大长度取决于最大行大小（65,535 字节，所有列共享）和使用的字符集

    例如，utf8mb3 字符每个字符最多需要三个字节，因此使用 utf8mb3 字符集的 VARCHAR 列最多可声明为 21,844 个字符

    如 varchar(4)，可以保存 abc 或 abcd ，但是如果保存 abcde ，MySQL则会报错，超出字段数据长度上限

  • 数值类型

    • 整型

      MySQL官方文档中这样描述：原文地址

      For integer data types, M indicates the minimum display width. The maximum display width is 255. Display width is unrelated to the range of values a type can store

      翻译: 对于整数数据类型，M表示最小显示宽度。 最大显示宽度为255。 显示宽度与类型可以存储的值范围无关

      M：表示数据最小显示宽度,

      M不会影响列的数据范围，如某一列设置为int(4)，该列数据可存储的数据大小范围依然是 (-2 147 483 648，2 147 483 647)(有符号), 当有超过M长度的数值插入时，只要在数据范围内都是可以正常插入数据 , 如 插入数据 10001， MySQL是可以正常插入数据的

    • 小数型(浮点型)

      MySQL官方文档中这样描述：原文地址

      MySQL permits a nonstandard syntax: FLOAT(*M*,*D*) or REAL(*M*,*D*) or DOUBLE PRECISION(*M*,*D*). Here, (*M*,*D*) means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) is displayed as -999.9999. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

      翻译：MySQL 允许非标准语法：FLOAT(M,D) 或 REAL(M,D) 或 DOUBLE PRECISION(M,D)。这里，(M，D)表示总共最多可以存储M位数值，其中小数点后可以有D位。例如，定义为 FLOAT(7,4) 的列显示为 -999.9999。 MySQL 在存储值时执行舍入，因此如果将 999.00009 插入 FLOAT(7,4) 列，则近似结果为 999.0001。

      M：表示数据最大位数 (包括小数位)

      D：表示最大小数位数

MySQL
This post is licensed under CC BY 4.0 by the author.
Recently Updated
Contents