mysql数字类型

原创

mb648972af0d702 2023-06-23 00:16:36 ©著作权

文章标签 数据库人工智能数据类型数据浮点数 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者mb648972af0d702的原创作品，请联系作者获取转载授权，否则将追究法律责任

1. 浮点数（SINGLE，DOUBLE，FLOAT，REAL）在计算机中是纯数字，即由二进制来表示的数字。由于规定了长度，所有是离散形的，也就是说无法准确表示定义区间内的所有实数。如果想了解详细，则可以参考自己的《计算机原理》教材，或者搜索 IEEE 754。

2. DECIMAL、NUMBER，这个从计算机角度来看，它不是数字，是一个结构。是由字符串或者DCB编码来表示的数字。和浮点数不同，它无法直接在CPU的ALU上直接进行计算，而是需要程序来处理的。但由于它是用字符串来表示，所以不会出现精度损失。但计算速度慢。并且一旦出现 DECIMAL*DECIMAL则同样会产生计算误差。

对比下sqlserver
数值数据
数值数据可以用来做数值运算处理，当我们要存放纯数字的数据，或是要对存放的内容作数值运算时，可以将它定义成数值的数据类型。SQL Server 2000提供许多可以存放数值数据的数据类型，如Int，Decimal，Real等。这些数值数据类型的特性及数值范围说明如表6-1所示。
表6-1 数据类型的特性及取值范围
数据类型使用字节数据范围
整数数值 tinyint 1 Bytes 0 ～ 255
smallint 2 Bytes －32,768 ～ 32,767
int 4 Bytes －231 ～ (231－1)
bigint 8 Bytes －263 ～ (263－1)
小数数值 decimal (numeric) 最大至38 Bytes （－1038 +1）～（1038－1）
浮点数值 float 单精度：4 Bytes；
双精度：8 Bytes －1.79E + 308 ～ 1.79E + 308
real 4 Bytes －3.40E + 38 ～ 3.40E + 38
从表6-1中可以发现float浮点数据类型可分为单精度与双精度两种，一般来说，除非是在进行科学运算需要较精确的值，需要用到双精度，否则一般使用单精度就足够了。另外，使用浮点数据类型可能会产生部分位数数据遗失的问题，例如，单精度数据类型，当数值位数超过7位时，只会保留最前面的7位数字（数值123456789保存成单精度后会变成123456700）；双精度数据类型只会保留15位数字。
当我们要指定数值数据的数据类型时，可以先通过存放的数值数据是否包含小数来决定使用整数类型的数据类型，或是带有小数的数据类型，接着再根据数值范围选择一个最适当的数据类型。
例如，年龄数据是一个数值数据，它通常是以正整数来表示，而且它的数值范围通常不会超过255，为了节省空间我们可以将它定义成tinyint数据类型；平均成绩数据为0~100之间的数值，通常保留两位小数，可以将它定义为 5 Bytes的Decimal数据类型（1～9位数），或是单精度浮点数据类型float或real。
6-2-2　货币数据
货币数据是专门针对金额数据而定义的，它可以说是一种特殊的小数数值数据，固定为4位小数。在SQL Server中提供两种货币数据类型：money与smallmoney，这两种货币数据类型的特性说明如表6-2所示。
表6-2 两种货币数据类型的说明
数据类型使用字节数据范围
Money 4 Bytes 263～（263 – 1）
smallmoney 8 Bytes －214.748,3648～+214,748.364
6-2-3　日期数据
日期数据是用来表示日期与时间，当我们要在表中存放日期时间信息，如出生日期、数据传入系统的时间等，就可以将列定义为日期时间数据类型。在SQL Server中，根据占用的字节以及数据的精度，定义了两种日期时间数据类型——datetime与smalldatetime，它们的特性说明如表6-3所示。
表6-3 两种日期时间数据类型说明
数据类型使用字节数据范围精度
Datetime 4 Bytes 1753年1月1日
到
9999年12月31日三百分之一秒

续表
数据类型使用字节数据范围精度
smalldatetime 8 Bytes 1900年1月1日
到
2079年6月6日一分钟
其实这种日期时间数据在系统中是以数值数据的形式存在的，数值整数的部分是用来指定该日期距离基准日的天数。例如，系统的基准日为1900/1/1，数值30，表示从1900/1/1算起的第30天，也就是1900/1/30；小数部分则是用来表示时间，例如，数值0.5表示半天，也就是12:00。所以日期时间数值30.5就是指1900年1月30日的上午12:00。
6-2-4　字符串数据
前面介绍的数值数据类型（含货币数据）只能允许输入数字，而字符串数据内则可以包含文字、数字或其他的特殊符号，几乎所有可以在计算机上输入的信息都可以视为是字符串数据。在定义字符串数据类型时，必须指定一个数值，用来表示字符串数据的字符长度。在char，varchar与text等字符串类型中，不论是字母、数字或中文字，每一个字符都占用一个字节空间。

1. char数据类型
char数据类型是用来存放固定长度的字符串内容，其最大长度可达8,000个字符（8K）。当我们要保存长度固定的数据（如代号）时，可以将它定义为char数据类型。
当我们在char数据类型内存入长度小于指定大小的字符串时，它将会自动在字符串后面补空格填满整个长度，使数据长度固定。

2. varchar数据类型
varchar数据类型较char数据类型有弹性，它可以随着存放的数据长度大小自动调整其占用的数据空间，当存入的数据长度小于指定的大小时，它不会在数据后面补空格，而是以实际存入的数据长度保存。其最大长度可设置为8,000个字符。
当数据内容长度的变异性较大时，varchar数据类型将是较好的选择，它可以减少不必要的空间浪费。例如，在产品数据的产品简述上，有些产品可能需要使用200到300个字来描述，有些产品则只需要短短20到30个字就可以介绍完，这时若将它定义为char数据类型，每一个产品的产品简述都固定占用200到300个字符长度；如果定义为varchar，则是占用实际输入的简述内容的字符长度。

3. text数据类型
char与varchar数据类型最大只能定义到存放8000个字符（8K），如果要存放的数据长度超过这个限制时，可以使用text数据类型。text数据类型和varchar数据类型一样，都是一个可变长度的数据类型，它允许的最大长度限制为231－1(2,147,483,647)个字符。
6-2-5　Unicode数据
前面介绍的三种字符串类型是使用数据库的字符集来定义字符串的内容，当要将这些数据转移到其他使用不同字符集的数据库时，数据的转换可能会发生错误，产生乱码。为了使数据能够在不同数据库（字符集）间顺利地转移，可以使用Unicode标准字符形式来定义字符串数据，所有的字符集的字符都有对应于这个标准的定义。
Unicode字符串类型包含nchar，nvarchar与ntext三种数据类型，分别对应前面的char，varchar与text数据类型。不过，这种Unicode标准是以2个字节代表一个字符，因此它可以允许的数据长度将会是非Unicode字符串类型的一半，也就是最大长度4,000个字符。如果预计要保存的数据长度将会大于4,000个字符，就必须将数据类型定义为ntext。
6-2-6　Binary数据
Binary数据是以十六进制的方式来表示数据，它可以接受1～9的数字，与A～F间的字母。通常我们可以利用它来表示图片或影像数据，或者是存放特殊格式化的文件数据，如Word，Excel，PDF文件等。

4. bit数据类型
bit数据类型就好像我们在程序语言中常用的Boolean布尔数据类型一样，如果数据内容只有True或False，1或0两个值时，可以将它定义为bit数据类型。例如，在产品数据中，可以利用它来表示该货品是否为新上市；在人员基本数据中，可以利用它来表示人员性别为男生或女生等。
实际上bit数据类型是以一个bit的位空间来存放数据，它的内容只有可能是0或1两种，我们必须自行定义0代表什么、1代表什么，例如，在“新上市”列中，可以定义数值为1时表示True（该货品为新上市），数值为0表示False（货品非新上市）。

5. timestamp数据类型
timestamp数据类型是一个binary的递增数据类型，它会在数据行加入或更改时自动产生，产生的值在数据库中是唯一的，不会有重复，通常我们可以使用它来作为列的版本戳记。由于这个时间戳记会随着数据行的变动而更改，因此最好不要将它设置为表的索引键，而且在一个表中也应该只会有一个timestamp数据类型的列。
timestamp列的内容是根据@@DBTS函数所取得的目前数据库时间戳记数值而更新的，与数据行插入或更改的日期与时间没有直接的关系。如果用户想要自动记录表中数据修改的时间，应该要使用datetime或smalldatetime数据类型，并利用事件触发程序来写入日期时间数据。

6. uniqueidentifier数据类型
uniqueidentifier数据类型是一个包含16字节的十六进制数字，用来表示全域唯一标识项(GUID)。这个GUID在不同的机器上、时间上都不同，因此对于一个跨国际、具有多台数据库服务器的企业，使用GUID作为辨识码来唯一标示数据行，可以确保所有这些数据库数据的汇整不会发生数据重复的问题。
在将列定义为uniqueidentifier数据类型时，必须将列的默认值设置为NEWID函数，这样当数据列插入到表时，就会调用NEWID函数自动产生GUID值。

7 sql_variant数据类型
sql_variant数据类型可保存text，ntext，timestamp与sql_variant以外的所有

SQL Server支持的数据类型。当用户无法确定表的某个列内将会存放哪种类型的数据类型时，可以将它定义为sql_variant数据类型，允许它接受任何数据类型的数据，以避免在填入数据时产生数据类型不符的错误。

SQL Server提供binary，varbinary以及image等三种存放Binary数据的数据类型，其中binary数据类型为一固定长度的数据类型，它会以固定的长度处理数据，当数据长度不足时会自动填补到指定的固定长度。varchar与image数据类型则是可变长度的数据类型，会随着数据内容调整长度。一般来说，使用varchar数据类型应该已经足够，如果预期数据长度将会超过8KB，就要改用image数据类型。
表6-4
数据类型长度变动数据最大长度限制
binary 固定长度 8,000个字节
barbinary 可变长度 8,000个字节
image 可变长度 231 – 1 (2,147,483,647)个字节
6-2-7　其他特殊数据类型
前面介绍的几个数据类型，其定义内容非常明确，如要保存日期、时间数据就使用日期时间数据类型、要存放文字就使用字符串数据类型、要存放数值就使用数值类型的数据类型等。除了这些数据类型外，SQL Server还提供了一些无法分类的特殊数据类型，将常用到的数据类型说明如下

mysql中float数据类型的问题收藏

一、浮点数的概念及误差问题： 浮点数是用来表示实数的一种方法，它用 M(尾数) * B( 基数)的E(指数）次方来表示实数，相对于定点数来说，在长度一定的情况下，具有表示数据范围大的特点。但同时也存在误差问题，这就是著名的浮点数精度问题！ 浮点数有多种实现方法，计算机中浮点数的实现大都遵从 IEEE754 标准，IEEE754 规定了单精度浮点数和双精度浮点数两种规格，单精度浮点数用4字节（32bit）表示浮点数，格式是：1位符号位 8位表示指数 23位表示尾数 双精度浮点数8字节（64bit）表示实数，格式是：1位符号位 11位表示指数 52位表示尾数 同时，IEEE754标准还对尾数的格式做了规范：d.dddddd...，小数点左面只有1位且不能为零，计算机内部是二进制，因此，尾数小数点左面部分总是1。显然，这个1可以省去，以提高尾数的精度。由上可知，单精度浮点数的尾数是用24bit表示的，双精度浮点数的尾数是用53bit表示的，转换成十进制： 2^24 - 1 = 16777215 2^53 - 1 = 9007199254740991 由上可见，IEEE754单精度浮点数的有效数字二进制是24位，按十进制来说，是8位；双精度浮点数的有效数字二进制是53位，按十进制来说，是16 位。显然，如果一个实数的有效数字超过8位，用单精度浮点数来表示的话，就会产生误差！同样，如果一个实数的有效数字超过16位，用双精度浮点数来表示，也会产生误差！对于 1310720000000000000000.66 这个数，有效数字是24位，用单精度或双精度浮点数表示都会产生误差，只是程度不同： 单精度浮点数： 1310720040000000000000.00 双精度浮点数： 1310720000000000000000.00 双精度差了 0.66 ，单精度差了近4万亿！这个结果为什么与翟振兴例子中的差很多呢？原因是翟振兴的测试用表中对字段进行了限制，实际上显示的是mysql溢出后的值，而我这里给出的是计算机中实际的值，如果把测试表字段精度提高到24位或以上，得到的结果就相同了。 以上说明了因长度限制而造成的误差，但这还不是全部！采用IEEE754标准的计算机浮点数，在内部是用二进制表示的，但在将一个十进制数转换为二进制浮点数时，也会造成误差，原因是不是所有的数都能转换成有限长度的二进制数。对于翟振兴测试中用到的 131072.32 这个数，其有效数字是8位，按理应该能用单精度浮点数准确表示，为什么会出现偏差呢？看一下这个数据二进制尾数就明白了 10000000000000000001010001...... 显然，其尾数超过了24bit，根据舍入规则，尾数只取 100000000000000000010100，结果就造成翟振兴测试中遇到的“奇怪”现象！131072.68 用单精度浮点数表示变成 131072.69 ，原因与此类似。实际上有效数字小于8位的数，浮点数也不一定能精确表示，7.22这个数的尾数就无法用24bit二进制表示，当然在数据库中测试不会有问题（舍入以后还是7.22），但如果参与一些计算，误差积累后，就可能产生较大的偏差。二、mysql 和 oracle中的数值类型： 翟振兴发现的问题是不是只有 mysql 存在呢？显然不是，只要是符合IEEE754标准的浮点数实现，都存在相同的问题。 mysql中的数值类型（不包括整型）： IEEE754浮点数： float （单精度） ， double 或 real （双精度） 定点数： decimal 或 numeric oracle中的数值类型： oracle 浮点数 ： number （注意不指定精度） IEEE754浮点数： BINARY_FLOAT （单精度） ， BINARY_DOUBLE （双精度） FLOAT，FLOAT(n) （ansi要求的数据类型） 定点数： number(p,s) 如果在oracle中，用BINARY_FLOAT等来做测试，结果是一样的。 因此，在数据库中，对于涉及货币或其他精度敏感的数据，应使用定点数来存储，对mysql来说是 decimal，对oracle来说就是number(p,s)。双精度浮点数，对于比较大的数据同样存在问题！三、编程中也存在浮点数问题： 不光数据库中存在浮点数问题，编程中也同样存在，甚至可以说更值得引起注意！ 通过上面的介绍，浮点数的误差问题应该比较清楚了。如果在程序中做复杂的浮点数运算，误差还会进一步放大。因此，在程序设计中，如果用到浮点数，一定要意识到可能产生的误差问题。不仅如此，浮点数如果处理不好，还会导致程序BUG！看下面的语句： if (x != y) { z = 1 / (x -y);} 这个语句看起来没有问题，但如果是浮点数，就可能存在问题！再看下面的语句会输出什么结果： public class Test { public static void main(String[] args) throws Exception { System.out.print("7.22-7.0=" + (7.22f-7.0f)); } } 我们可能会想当然地认为输出结果应该是 0.22 ，实际结果却是 0.21999979 ! 因此，在编程中应尽量避免做浮点数的比较，否则可能会导致一些潜在的问题！ 除了这些，还应注意浮点数中的一些特殊值，如 NaN、+0、-0、+无穷、-无穷等，IEEE754虽然对此做了一些约定，但各具体实现、不同的硬件结构，也会有一些差异，如果不注意也会造成错误！四、总结： 从上面的分析，我们可以得出以下结论： 1、浮点数存在误差问题； 2、对货币等对精度敏感的数据，应该用定点数表示或存储； 3、编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较； 4、要注意浮点数中一些特殊值的处理。 June，浮点数问题，很容易被忽视，可能具有一定的普遍性，也许应该发给其他技术人员，以免再出现这方面的问题。 -----Original Message-----From: htang [mailto:htang@corp.netease.com]Sent: Tuesday, September 26, 2006 6:29 PMTo: 翟振兴Cc: LisaLan; 关宝军; 韦连友Subject: RE: RE: mysql中float的问题这个问题不是一个Bug，而是浮点数本身存在的局限。原因是计算机对浮点数的表示是 M * 2 的 N 次方，其中M是尾数，N是指数，在此转换过程中存在数据损失，因此浮点数（包括double类型）是不能精确表示所有实数的。出现的问题正是由误差和四舍五入造成的。 -----Original Message-----From: 翟振兴 [mailto:zxzhai@corp.netease.com]Sent: Tuesday, September 26, 2006 12:17 PMTo: htangCc: LisaLan; 关宝军; 韦连友Subject: Re: RE: mysql中float的问题Importance: High 老唐,您好！ 昨天测试发现，当float数据类型超过131072时候，插入的数据会发现不稳定情况，测试过程如下： mysql> desc test10;+------------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+---------------+------+-----+---------+-------+| floattest | float(12,2) | YES | | NULL | || doubletest | double(12,2) | YES | | NULL | || dectest | decimal(12,2) | YES | | NULL | |+------------+---------------+------+-----+---------+-------+ mysql> insert into test10 values(131071,131071,131071);Query OK, 1 row affected (0.00 sec) mysql> select * from test10;+-----------+------------+-----------+| floattest | doubletest | dectest |+-----------+------------+-----------+| 131071.00 | 131071.00 | 131071.00 |+-----------+------------+-----------+1 row in set (0.00 sec) mysql> insert into test10 values(131071.32,131071.32,131071.32);Query OK, 1 row affected (0.00 sec) mysql> select * from test10;+-----------+------------+-----------+| floattest | doubletest | dectest |+-----------+------------+-----------+| 131071.00 | 131071.00 | 131071.00 || 131071.32 | 131071.32 | 131071.32 |+-----------+------------+-----------+2 rows in set (0.00 sec) mysql> insert into test10 values(131071.68,131071.68,131071.68);Query OK, 1 row affected (0.00 sec) mysql> select * from test10;+-----------+------------+-----------+| floattest | doubletest | dectest |+-----------+------------+-----------+| 131071.00 | 131071.00 | 131071.00 || 131071.32 | 131071.32 | 131071.32 || 131071.68 | 131071.68 | 131071.68 |+-----------+------------+-----------+3 rows in set (0.01 sec) mysql> insert into test10 values(131072,131072,131072);Query OK, 1 row affected (0.00 sec) mysql> select * from test10;+-----------+------------+-----------+| floattest | doubletest | dectest |+-----------+------------+-----------+| 131071.00 | 131071.00 | 131071.00 || 131071.32 | 131071.32 | 131071.32 || 131071.68 | 131071.68 | 131071.68 || 131072.00 | 131072.00 | 131072.00 |+-----------+------------+-----------+4 rows in set (0.00 sec) mysql> insert into test10 values(131072.32,131072.32,131072.32);Query OK, 1 row affected (0.00 sec) mysql> select * from test10;+-----------+------------+-----------+| floattest | doubletest | dectest |+-----------+------------+-----------+| 131071.00 | 131071.00 | 131071.00 || 131071.32 | 131071.32 | 131071.32 || 131071.68 | 131071.68 | 131071.68 || 131072.00 | 131072.00 | 131072.00 || 131072.31 | 131072.32 | 131072.32 |+-----------+------------+-----------+5 rows in set (0.00 sec) mysql> insert into test10 values(131072.68,131072.68,131072.68);Query OK, 1 row affected (0.00 sec) mysql> select * from test10;+-----------+------------+-----------+| floattest | doubletest | dectest |+-----------+------------+-----------+| 131071.00 | 131071.00 | 131071.00 || 131071.32 | 131071.32 | 131071.32 || 131071.68 | 131071.68 | 131071.68 || 131072.00 | 131072.00 | 131072.00 || 131072.31 | 131072.32 | 131072.32 || 131072.69 | 131072.68 | 131072.68 |+-----------+------------+-----------+6 rows in set (0.00 sec) mysql> insert into test10 values(131072.66,131072.66,131072.66);Query OK, 1 row affected (0.00 sec) mysql> select * from test10;+-----------+------------+-----------+| floattest | doubletest | dectest |+-----------+------------+-----------+| 131071.00 | 131071.00 | 131071.00 || 131071.32 | 131071.32 | 131071.32 || 131071.68 | 131071.68 | 131071.68 || 131072.00 | 131072.00 | 131072.00 || 131072.31 | 131072.32 | 131072.32 || 131072.69 | 131072.68 | 131072.68 || 131072.66 | 131072.66 | 131072.66 |+-----------+------------+-----------+ mysql> insert into test10 values(1310720000000000000000.66,1310720000000000000000.66,1310720000000000000000.66);Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> select * from test10;+----------------+---------------+---------------+| floattest | doubletest | dectest |+----------------+---------------+---------------+| 131071.00 | 131071.00 | 131071.00 || 131071.32 | 131071.32 | 131071.32 || 131071.68 | 131071.68 | 131071.68 || 131072.00 | 131072.00 | 131072.00 || 131072.31 | 131072.32 | 131072.32 || 131072.69 | 131072.68 | 131072.68 || 131072.66 | 131072.66 | 131072.66 || 10000000000.00 | 9999999999.99 | 9999999999.99 |+----------------+---------------+---------------+ 以上测试说明：当insert的数据范围在＋－131072（65536×2）以内的时候，float数据精度是正确的，但是超出这个范围的数据就不稳定，没有发现有相关的参数设置建议：将float改成double或者decimal，两者的差别是double是浮点计算，decimal是定点计算，会得到更精确的数据。