本文共 6922 字，大约阅读时间需要 23 分钟。

一、数字类型概述

• MySQL的数字类型包括整数 、定点数 、浮点数 和位值 ，如下图所示：

• 除BIT之外，其他的数类型可以带有正负号，也可以不带正负号。有一种特殊的属性，它可以让列自动生成一组有序的整数或浮点值。在你需要一组唯一标识编号的时候，可以使用它。

二、数字类型分类

• MySQL的数字数据类型分为以下3大类：
  • 精确值类型，它包括整数类型和DECIMAL。
    • 整数类型用来存放没有小数部分的数字。只要是能用整数来表示的数据(如精确到谤的重量值 、家庭人口数或者库存量)，都可以用一个整型列来保存。
    • DECIMAL类 型可以保存带有小数部分的精确值 ，如3.14159、-.00273或-4.78。这种类 型非常适合用来保存像币值之类的数据。整数和DECIMAL类型的值都会被精确地保存起来 ，不会进行四舍五入；并且关于它们的计算也是精确的。
  • 浮点类型，它细分为单精度(FLOAT)和双精度(DOUBLE)。这些类型和DECIMAL—样 ，也可以用来存放带有小数部分的数字，但它们存储的是可能发生四舍五入的近似值，如3.9E+4或-0.1E-100。如果对数值精确度的要求不太严格，或者因为数值很大，以至于DECIMAL类型无法表示，那么浮点类型则是个不错的选择。诸如粮食平均亩产、星球距离等类型的数据都很 适合使用浮点值来表示。
  • BIT类型，用于存储位域值。

• 带小数部分的值可以赋值给一个整型列，但会使用“四舍五入”规则近似成整数：如果小部分大于等于0.5，则舍弃小数部分，在整数部分上加1(对于负数，则是减1)。类似地，整值也可以赋给允许 有小数部分的类型列，只不过它们的小数部分等于零。

取值范围与存储空间

• 取值范围如下：

• 存储空间如下：

• M表示整型的最大显示宽度、浮点类型和DECIMAL类型的精度(有效位数)，以及BIT型的位数 。对于那些带有小数部分的数据类型，代表的是数学精确度(小数点后面的数字数)， 也称为“ 小数精度”（scale）。
• 类型DECIMAL的存储空间要求取决于小数点左右两侧的数字个数：
  • 对于每一侧，每9位数字需要4个字节，最后剩下的数字需要1〜4个字节。
  • 每一个值的存储空间都等于小数点左右两边字节存储占用总和，是小数点左右两侧数值所需存储空间的总和。
• 一个BIT(M)值大约需要(M+7)/8个字节的存储空间。

三、精确值数字类型

• 精确值数据类型包括整数类型和定点DECIMAL类型。

整数类型

• 整数类型包括TINYINT、SMALLINT、MEDIUMINT、INT和 BIGINT。其中，INTEGER是 INT的同义词。
• 可以将整型列定义为UNSIGNED，以禁止出现负数 ，这也将其取值范围上移到了从0开始的区间。DECIMAL类型

• 在定义整型列时，你可以为它指定一个可选的显示宽度M，该值必须是1〜255的整数。它决定着MySQL将用多少个字符来显示该列里的值。例如，MEDIUMINT(4)会指定一个显示宽度为4个字符的MEDIUMINT列。
• 假设声明如下的INT类型的字段：该字段指名，在year字段中数据一般只显示4位数字的宽度

CREATE TABLE student(    year INT(4));

• 注意：显示宽度和数据类型的取值范围和占用空间是无关的。
• 显示宽度只是指明MySQL最大可能显示的数字个数：
  • 数值的位数小于指定的宽度时会由空格填充；
  • 如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如，向上面的year字段中插入数值19999，当使用SELECT查询该列值的时候，MySQL显示的将是完整的带有5位数字的19999，而不是4位数字的值

• 如果不指定显示的宽度，则系统为每一种类型指定默认的宽度值。如下所示：

CREATE TABLE tmp1(    x TINYINT,    y SMALLINT,    z MEDIUMINT,    m INT,    n BIGINT);DESC tmp1;

• 从上图可以看出，系统为每一种类型使用默认宽度，这些显示宽度能够保证显示每一种数据类型可以取到取值范围内的所有值

DECIMAL类型

• DECIMAL是一种定点类型，即所有值都有一个固定的小数位数。这 一点的重要性在于DECIMAL值不会像浮点数那样存在四舍五入的问题——此特性使得DECIMAL非常适合于保存货币值 。
• NUMERIC和FIXED都是DECIMAL的同义词。
• DECIMAL列也可以被定义成UNSIGNED。与 整数类型不同的是，把一个DECIMAL类型定义为UNSIGNED不会扩大该类型的取值范围，而只会“砍掉 ”整个负数部分。
• decimal(M,N)：在DECIMAL列的定义里，可以包含一个最大有效位数M和一个小数位数D(即精度和小数位数 )。其中：
  • M的取值范围是1〜65，D的取值范围是0〜30，且不超过M。
  • M代表精度：表示总共的位数(不包括小数点)。N代表标度：小数的位数
  • 与浮点数类型一样，如果用户指定的数字超出了精度范围，会进行四舍五入处理。
• 这里的M和D都是可选的：
  • 如果省略了D，那么其默认值为0。
  • 如果省略了M，那么其默认值为10。
  • 换句话说，下列等式是成立的：

DECIMAL = DECIMAL(10) = DECIMAL(10,0)DECIMAL(n)=DECIMAL(n,0)

• DECIMAL类型的最大取值范围取决于M和D的值：
  • 如果在D保持不变的情况下调整M，则取值范围将随着M的变大而增加（如下图所示）。
  • 如果在M保持不变的情况下调整D，则取值范围将随着D的变大而减小（如下图所示）。

四、近似值数字类型

• MySQL提供了两种浮点类型：FLOAT和DOUBLE，用于存储近似值数。
• DOUBLE PRECISION是 DOUBLE的同义词 。在默认情况下，RAEL类型是DOUBLE的同义词；如果启用了SQL的REAL_AS_DEFAULT模式，则它会成为FLOAT的同义词 。
• 浮点类型也可以定义为UNSIGNED。这样，该类型的负数部分将被“砍掉”。
• 与DECIMAL类型相似，在浮点类型列的定义里，可以包含一个最大有效位数M和一个小数位数 D(即精度和小数位数)：
  • 其中，M的取值范围是1-255，D的取值范围是0~30，且不超过M。
  • 这里的M和D都是可选的。如果在定义列时省略了它们，那么这些值会按你的硬件所支持的最大精度来存储。
• 使用FLOAT(p)语法也是可以的。不过，这里的p在标准SQL里表示的是所要求的精度位数，这点与 MySQL的处理方式有所差异：
  • p的取值范围是0~53，并且只能用于确定此列存储的是单精度值 ，还是双精度值。
  • 如果p值落在0〜24的区间内 ，则此列会被视为单精度列，如果落在25~53的区间 内 ，则此列将被视为双精度列。
  • 也就是说 ，此列会根据是否带有用M和D值，来确定是FLOAT类型，还是DOUBLE类型。
• 注意事项：
  • 插入数据时，如果值越界了，会报错。
  • 插入数据时，如果值没有越界，但是小数越界了，会进行四舍五入处理。

五、BIT类型

• BIT数据类型可用于存放位域值。BIT列的定义里可以包含一个可选的最大宽度M，它表示的是按二进制位计算列的“宽度”。M的取值必须是一个1~64的整数。如果省略，M的默认值将为1。
• BIT不支持UNSIGNED。
• 在默认情况下，从BIT列检索出来的值不能打印显示。要显示一个位域值的可打印形式，需要加上零或是使用CAST()函数：

CREATE TABLE t(    b BIT(3));INSERT INTO t(b) VALUES(0),(b'11'),(b'101'),(b'111');SELECT b, b+0, CAST(b AS UNSIGNED) FROM t;

• 如果需要以二进制表示法来显示某些位域值或是它们以二进制形式计算后的结果，那么可以使用BIN()函数：

SELECT BIN(b),BIN(b & b'101'), BIN(b | b'101') FROM t;

• 对于八进制和十六进制的显示，可以分别使用函数OCT()和HEX()。

六、数字数据类型的属性

• UNSIGNED属性可以防止出现负值。让一个整型列具有UNSIGNED属性，并不会改变底层数据类型的取值范围的宽度，而只是把这个范围向右平移。
• 请看下面这个表声明：itiny和itiny_u都是范围跨度为256的TINYINT列，只是允许的值集不一样 。itiny的取值范围是-128~127，而 itiny_u的取值范围则左移到了0~255。

CREATE TABLE mytbl(    itiny   TINYINT,    itiny_u TINYINT UNSIGNED);

• 如果数据不可能出现负数值(如人口统计数字或观众人数 )，则应考虑给相应的整型列加上UNSIGNED属性。如果用一个带正负号的列类型来保存这类数据，则只能使用到该数据类型的一半取值范围 。在为该列加上UNSIGNED属性之后，可用范围则立刻会增加一倍。例如，想用某列来 保存序列编号 ，那么在加上UNSIGNED属性之后，可用编号的长度会翻 一倍。
• 你也可以为DECIMAL或浮点列加上UNSIGNED属性，但最终效果与整型列的情况稍有不同。具体表现在：浮点值的取值范围不会朝正数方向平移；相反，原取值范围的上端将保持不变，而下端会变成0。

SIGNED属性

• 所有支持UNSIGNED属性的数字类型，也都可以用SIGNED属性。
• 因为这些类型在默认情况下都是带符号的，所以即使为允许使用负数的列加上SIGNED属 性，也没有什么效果。

ZEROFILL属性

• ZEROFILL属性适用于除BIT以外的所有数字类型。它会在列里的显示值前面填充若干0，使其宽度最终达到显示宽度。
• 当你想要让列值按指定的显示宽度进行显示时，就可以使用ZEROFILL。确切来讲，显示宽度其实是“一个给定的最小数字个数 ”，因为那些长度大于显示宽度的数值，会全部显示出来，而不会被截断。
• 演示案例：请注意，最后那个值是完整显示的，因为它比列的显示宽 度宽。

CREATE TABLE mytbl(my_zerofill INT(5) ZEROFILL);INSERT INTO mytbl VALUES(1),(100),(10000),(1000000);SELECT my_zerofill FROM mytbl;

• 如果给某个列指定了ZEROFILL属性，那么它将自动转换成一个UNSIGNED列。

AUTO_INCREMENT

• 另外一个属性是AUTO_INCREMENT，它可用于整数或浮点数类型(不过，用于浮点数类型的情况比较少见 )。当想要生成一组唯一标识符或序列值时，可以指定AUTO_INCREMENT属性。
• 当把NULL插到AUTO_INCREMENT列时，MySQL会自动生成下一个序列值，并把它存储 到该列中。通常情况下，在没有做出另外设定的时候，AUTO_INCREMENT的值将从1开 始，每新增一行，序列值也加1。如果在表里删除了某几行，那么这个序列可能也会受到影响 。也就是说， 序列值可能会被再次使用，但是这种情况是否会发生，还要看具体的存储引擎。
• 每个表最多只能有一个 AUTO_INCREMENT列：
  • 该类型的列应该具备NOT NULL约束，并且必须被索引。一般情况下，最好能把AUTO_INCREMENT列声明为一个 PRIMARY KEY或UNIQUE索引。
  • 此外，因为序列值不可能是负数 ，所以通常还应该为该列加上UNSIGNED属性。
  • 例如，可以用下列任意一条语句来声明一个AUTO_INCREMENT列：

CREATE TABLE ai(i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);CREATE TABLE ai(i INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE);CREATE TABLE ai(i INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(i));CREATE TABLE ai(i INT UNSIGNED NOT NULL AUTO_INCREMENT, UNIQUE(i));

• 其中，前面两种形式将索引信息指定为列定义的一部分。紧接着的两种形式将索引指定为 CREATETABLE语句的一个独立子句。如果这个索引只包含AUTO_INCREMENT列，那么 使用独立子句的方式则是可选的。如果想要创建一个涉及多个列且包含AUTO_INCREMENT列的索引，那么必须使用独立子句。更多相关示例会在后面"数据类型之处理序列中介绍"。

• AUTO_INCREMENT列可以显式地声明为NOT NULL。即使你省略了NOT NULL，MySQL也会自动把它加上。

• 以上介绍 的各种属性都是数字类型列所特有的。在这些属性后面，你可以继续指定NULL或 NOT NULL属性。如果没有为数字类型列指定NULL或NOT NULL，那么它将默认允许使用NULL值(通常情况是这样。不过，如上所述，AUTO_INCREMENT列默认使用的是NOT NULL)。
• 你还可以利用DEFAULT属性来指定默认值。下面这个表包含了3个INT列，它们的默认值分别为-1、1和NULL：

CREATE TABLE t(    i1 INT DEFAULT -1,    i2 INT DEFAULT 1,    i3 INT DEFAULT NULL);

• 如果列定义没有包含DEFAULT子句，那么MySQL将为它选择一个默认值。

七、选择数字数据类型

• 在为数字列选择数据类型时，需要考虑数据的取值范围 ，并选择一个能够覆盖该范围的最小类 型。选择较大的类型，会浪费存储空间，会导致表毫无必要地变得很大，也会导致在处理这样的表时，无法像处理选择了更小类型的表那样有效率。
  • 如果数据的取值范围很小(如人的年龄 或兄弟姐妹人数 )，则选择TINYINT类型是最佳的。
  • MEDIUMINT类型能够表示上百万的值，它可用于很多类型的信息，只是在存储空间方面会有些额外的开销。
  • BIGINT类型的取值范围最大，但它占用的存储空间是最小整数类型(INT)的两倍，所以应该只在真的需要时才使用它。
  • 对于浮点值，DOUBLE类型所占用的存储空间是FLOAT类型的两倍。因此，只有在需要非常高的精确度或者数据的取值范围非常大的情况下，才需要使用DOUBLE来代替FLOAT；否则 ，会浪费掉一半的存储开销。
• 每个数字列的取值范围都由其类型决定。如果想要把某个超出了列的取值范围的值插入这个列里，那么最终的结果将取决于是否已启用严格的SQL模式。
  • 如果已启用，则取值超出范围时将导致一个错误 。
  • 如果未启用，则会进行截断：MySQL会先把这个值替换为该列的取值范围的相应端点值 ，然后将处理后的结果值插入列中，同时生成一条警告消息。
• 值的截断处理依据的是数据类型的取值范围 ，而不是它的显示宽度。例如，SMALLINT(3) 列的显示宽度只有3个字符，但它的取值 范围 却为-32768~32767。因为数值12345比列的显示宽度更宽 ，但仍在该 列的取值范围内 ，所以不需要对它进行截断即可插入它，而且在检索时得到的结 果也是12345。对 于数 值 99999，由于它超出了该 列的取值范围，所以它在插入时会被截断为32767。以后的检索结果也会是32767。
• 对于定点或浮点列，如果存储值的小数位数多于该列定义所规定的值 ，那么会出现取舍情况 。例如，把 1.23456插到一个FLOAT(8, 1)列里，其结果将是1.2。如果把这个值插到一个FL0AT(8, 4)列里，其结果将是1.2346。也就是说，必须根据自己对精确度的要求来选定一个小数点后面的位数 。如果需要精确到千分之一，则不能只把小数点后面的位数声明为2位。
• FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL如不指定精度，默认为(10,0)。

演示案例

• SQL如下：

CREATE TABLE tmp2(    x FLOAT(5,1),    Y DOUBLE(5,1),    Z DECIMAL(5,1));INSERT INTO tmp2 VALUES(5.12,5.12,5.12);SHOW WARNINGS;

• 可以看到，在执行INSERT的时候MySQL给出了一个警告，接着查看警告，显示FLOAT和DOUBLE在四舍五入时没有警告，而DECIMAL类型给出了警告。
• 查看结果：

SELECT * FROM tmp2;

转载地址：https://dongshao.blog.csdn.net/article/details/104055727 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！