MySQL文档阅读(一)-数字类型

这个系列参考自MySQL官方文档:MySQL5.7官方文档

MySQL支持很多系列的SQL数据类型:数字类型(numeric types)、日期和时间类型(date and time types)、字符串类型(字符和字节)、特殊类型和JSON数据类型。

数据类型描述遵循如下约定:

  • M表示整数类型最大的显示宽度值;对于浮点数和固定长度数据类型,M表示数字在MySQL中的总的存储长度;对于字符串类型,M表示字符串的最大长度。数据类型的最大值就是M的最大值。
  • D决定了浮点类型和固定(fixed-point)类型,表示十进制数据的位数。D的最大值是30,但要小于M-2。
  • fsp应用在TIME、DATETIME和TIMESTAMP类型上,这个数字表示秒的分数部分。fsp的值应该是0到6:0表示没有分数部分(如果没有提供fsp值,默认值为0)。
  • 中括号([])表示数据类型中的可选部分。

数据类型概览

  1. 数字类型
  • 整数(INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT) 在创建表的时候,让我们填写的那个数字就是M,表示可显示的最大宽度,而数字的实际范围则由数据类型决定。例如:TINYINT[(M)] [UNSIGNED] [ZEROFILL]中,M就是表示可显示宽度,TINYINT实际可表示的数值范围如下图所示。

MySQL支持的整数类型

  • Fixed-Point类型(DECIMAL、NUMERIC) DECIMALNUMERIC类型代表精确数字类型。当业务需求(例如涉及金钱的数据)要求在数据库中存储精确数值时,需要使用这些类型。在MySQL中NUMERIC也实现为DECIMAL,因此后续针对DECIMAL的叙述也适用于NUMERIC。 MySQL以二进制格式存储DECIMAL值。在DECIMAL的列定义中需要制定precision和scale值,例如:
salary DECIMAL(5, 2)

在这个例子中,precision是5,scale是2;其中,precision表示用于表示数字的位数、scale表示小数点所在的位置。在标准SQL语法中,要求DECIMAL(5,2)表示5位数字,以及2位小数,它的取值范围是[-999.99, 999.99]。按照SQL语法,DECIMAL(M)等同于DECIMAL(M,0);DECIMAL也等同于DECIMAL(M,0),M的默认值是10。如果scale等于0,则DECIMAL值不包括小数部分。

  • 浮点类型(FLOAT、DOUBLE) FLOATDOUBLE类型代表近似值。MySQL使用4个字节表示单精度值、使用8个字节表示双精度值。对于FLOAT类型,SQL标准规定一份可选的精度规范,MySQL也支持这种可选的精度规范,但是精度值仅仅用于决定存储空间大小。单精度的FLOAT类型表示4个字节;24-53的精度则需要双精度DOUBLE类型表示。 MySQL允许一个非标准的语法:FLOAT(M, D)、REAL(M, D),或者DOUBLE PRECISION(M, D),在这里,M表示数值的最大存储位数是M,而D则表示小数点后有多少位。例如,FLOAT(7, 4)可以表示-999.9999。MySQL在存储数值的时候会执行四舍五入,因此,对于FLOAT(7, 4)如果你插入的数字是999.00009,则实际存储的值是999.0001。 正因为浮点数表示近似值而不是精确值,因此在试图把它们当做精确值进行比较的时候会有问题。具体的比较结果由系统决定,如果需要更详细资料,参考:Section B.5.4.8, “Problems with Floating-Point Values”
  • Bit值类型(BIT) BIT数据类型用于存储bit值,BIT(M)可存储M-bit值,M的范围是1~64。 为了标识是bit值,需要使用b'value'格式表示。value是一个0和1表示的二进制值。例如:b'111'表示7,b'10000000'表示128。 如果你给一个BIT(M)的列赋值一个小于M位的值,MySQL会在值的左边填充0,例如,如果将b'101'赋值给BIT(6)的列,则对应存储的值是b'000101'。
  • 数字类型属性 MySQL支持通过在数据类型后面加括号的方式限制整数类型的显示宽度。例如,INT(4)定义了一个整数类型,但是显示宽度为4。如果应用中的数值小于指定的宽度,则数字的左边用空格填充。 显示宽度并不会限制该列能够存储的值;也会让宽于指定宽度的值正确显示出来。例如,某个列的数据类型设定为SMALLINT(3),则该列可存储的范围是-32768到32767,这些超出指定宽度的值也能正确显示。 在使用ZEROFILL属性时,数字左边空余的部分会由0填充,例如:对于一个定义为INT(4) ZEROFILL的列,给定一个值为5,将显示为0005。 所有的Integer类型都有一个可选的属性——UNSIGNED。当某个属性只需要存放非负数字或者需要更大的数字上限时。例如:如果一个属性定义为UNSIGNED INT,这个属性能表示的范围跟SIGNED相同,但是由[-2147483648,2147483647]平移到[0, 4294967295]。 浮点数和精确数值也可以设置成UNSIGNED,这种情况下,跟整数类型一样不允许存储负数;跟整数类型不一样的是,该属性的表示范围上限与SIGNED相同。 如果你给一个numeric列指定了ZEROFILL属性,则MySQL会自动为其设置UNSIGNED属性。 对于INTEGER和浮点数类型,还有一个额外的属性——AUTO_INCREMENT。当你将一个NULL值插入到一个加了AUTO_INCREMENT索引的列,该列将会被设置为下一个顺序值。一般来说是现有的value+1,而这个value值正是当前表中该属性列的最大值(AUTO_INCREMENT属性列的值从1开始计数)。 将0存入AUTO_INCREMENT列和NULL的情况具备相同的效果,除非MySQL开启了NO_AUTO_VALUE_ON_ZERO模式。 如果要达到“插入NULL值产生自增值”的效果,需要将对应的AUTO_INCREMENT列设置为NOT NULL。如果该列设置为NULL(可空),那么在插入NULL值的时候就会直接存储为NULL。 MySQL5.7并不支持负数的自动增长。

注意: 当该属性属于某个表达式或者UNION查询时,MySQL会忽略ZEROFILL属性。

  • 溢出处理 当MySQL存储的值超过了某个属性所能表示的范围,这时候实际存储的结果取决于当时MySQL中的SQL模式:
    • 如果开启了严格SQL模式,MySQL会拒绝溢出的值,会报错,插入数据失败;
    • 如果没有开启严格SQL模式,则MySQL会根据数据类型能表示的最大值将该溢出值截断,并存储该数据类型能表示的最大值;例如:当把一个溢出值赋值给一个Integer属性,MySQL实际上存储的是该Integer类型所能表示的边界值。如果你将256存入TINYINT或者TINYINT UNSIGNED列,MySQL会分别存储127或255。当把一个溢出值赋值给一个浮点数或者一个FIXED-POINT列时,MySQL会截断并存储该数据类型能表示的边界值。

    在numberic表达式求值过程中发生溢出,则会导致一个错误。例如,SIGNED BIGINT的最大值是9223372036854775807, 因此如下的表达式会产生错误:

mysql> SELECT 9223372036854775807 + 1; 
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

为了使得上述表达式正确执行,需要将值修改成UNSIGNED:

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
| 9223372036854775808                       |
+-------------------------------------------+

两个Integer相减,如果其中一个为UNSIGNED,则最后的结果为UNSGINED。当计算过程中发现结果为负数,则会报出错误:

mysql> SET sql_mode = ''; 
Query OK, 0 rows affected (0.00 sec)  
mysql> SELECT CAST(0 AS UNSIGNED) - 1; 
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

上述这种情况,当SQL模式——“NO_UNSIGNED_SUBTRACTION”开启时,才会显示正确的负数结果:

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION'; 
mysql> SELECT CAST(0 AS UNSIGNED) - 1; 
+-------------------------+ 
| CAST(0 AS UNSIGNED) - 1 | 
+-------------------------+ 
|                      -1 | 
+-------------------------+

如果上述操作的结果是用于更新UNSIGNED列,则该结果会被截断为该数值类型的最大值;或者当开启了“NO_UNSIGNED_SUBTRACTION”模式时,结果会存储为0。如果开启了严格模式,则会报出错误,并且对应的属性值不会改变。

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

PL/SQL 联合数组与嵌套表

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

1143
来自专栏木子昭的博客

<导图>Mysql常用查询语法

普通查询 查看整个表 格式: select * from 表名; 示例: select * from students; 查询指定字段 格式 ...

3347
来自专栏java初学

mysql入门 — (2)

3568
来自专栏抠抠空间

MySQL之表的数据类型

一 介绍 存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的 详细参考: http://www.runoob....

3678
来自专栏数据库

MySQL数据库应用总结(六)—MySQL数据库的数据类型和运算符(上)

SQL语法预览: 创建表字段数据类型:【createtable 表名(字段名称 数据类型); 】 插入字段值:【insert into表名 values(值1,...

2135
来自专栏醉生梦死

MySQL常用函数 原

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()));

1202
来自专栏Python

表的数据类型

一 介绍 存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的 详细参考: http://www.runoob....

2157
来自专栏栗霖积跬步之旅

第十章:创建计算字段

创建在数据库表中的数据一般不是应用程序所需要的格式。  我们需要从数据库中检索出转换、计算或格式化过的数据。  计算字段并不实际存在于数据库表中,计算字段是运行...

1735
来自专栏听雨堂

使用正则表达式求完整路径中的文件名

      以前都是比较恶心的算法,找字符串中的最后一个"\",再求出末尾的文件名。现在好啦,用.net中的正则表达式,可以非常漂亮的完成。    usin...

3168
来自专栏Laoqi's Linux运维专列

SQLAlchemy总结+

3583

扫码关注云+社区

领取腾讯云代金券