MySQL全面瓦解3:数据类型介绍与分析

发布于 2022-8-1 19:15
浏览
0收藏

概述
MySQL支持很多数据类型,以便我们能在复杂的业务场景中支持各种各样的数据格式,存储适当的数据内容。我们在设计数据库时,正确的使用数据库类型对整个数据库的整洁和高效,会有很大的帮助。

目前常用的数据类型大致上可以分为4大类:整数类型、浮点数类型、字符串(字符)类型、日期/时间类型。详细如下

MySQL全面瓦解3:数据类型介绍与分析-开源基础软件社区

数值类型
MySQL支持所有标准SQL数值数据类型,包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),还有近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION),所以MySQL中数据类型是丰富且完整的。

而作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。在下面的表格中,我们总结了5个整数类型的存储大小和取值范围。

MySQL全面瓦解3:数据类型介绍与分析-开源基础软件社区

我们创建表赋予的类型默认是有符号类型的,无符号的需要在类型需要后面跟上unsigned 标记,上面的数据范围是根据字节对应二进制换算,比如一个字节转化为十进制最大为255(11111111),最小为0(00000000),具体可以去查下二进制表示相关资料。

测试一下:

mysql> create table tinyintdemo(
  tiny TINYINT,
  tinyunsi TINYINT UNSIGNED
 );
 Query OK, 0 rows affected
 
 mysql> insert into tinyintdemo values(-128,-128);
 1264 - Out of range value for column 'tinyunsi' at row 1
 mysql> insert into tinyintdemo values(-128,0);
 Query OK, 1 row affected
 
 mysql> insert into tinyintdemo values(255,255);
 1264 - Out of range value for column 'tiny' at row 1
 mysql> insert into tinyintdemo values(127,255);
 Query OK, 1 row affected

 上面我们故意输入不正确的值,无符号下我们输入-128,默认有符号情况下我们输入255,都会提示 Out of range,超出范围。

mysql> select *  from  tinyintdemo;
+------+----------+
| tiny | tinyunsi |
+------+----------+
| -128 |        0 |
|  127 |      255 |
+------+----------+
2 rows in set

另外需注意的点:我们经常在创建表的时候定义字段为int(n),比如int(11) , 或者bitint(19) , 其实这不是描述字段长度的意思,在int(n)中,int占据的字节是固定4个,所以他的范围也固定是在 (-2 147 483 648,2 147 483 647) 之间,

并不会因为n的长度是多少而改变,而n的目的是为了表示显示宽度用的,在显示宽度不足的时候补充0。

再测试一下:

 mysql> CREATE TABLE lengthdemo (
        `nor` int,
        `norlen` int(8),
        `norfill` int(8) zerofill
 );
 Query OK, 0 rows affected
 
 mysql> insert into lengthdemo values(12345,12345,12345);
 Query OK, 1 row affected
 
 mysql> select * from lengthdemo;
  
 +-------+--------+---------+
 | nor   | norlen | norfill |
 +-------+--------+---------+
 | 12345 |  12345 |00012345 |
 +-------+--------+---------+
 1 row in set
 
 mysql> show create table lengthdemo;
 +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table      | Create Table                                                                                                                                                                   |
 +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | lengthdemo | CREATE TABLE `lengthdemo` (
   `nor` int(11) DEFAULT NULL,
   `norlen` int(8) DEFAULT NULL,
   `norfill` int(8) unsigned zerofill DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set

从这个执行结果中,我们可以总结如下:

1、n的值不管填写多少,int类型只占据4个字节。

2、如果设置了n的显示宽度之后,再加上unsigned zerofill,显示宽度不足的用0补足,超过则直接显示完整数值。

3、加 zerofill属性的字段,会默认为 unsigned 无符号类型。

浮点类型 
float数值类型表示单精度浮点数值,double数值类型表示双精度浮点数值,float和double都是浮点型,而decimal是定点型。又称为精度和标度。

MySQL全面瓦解3:数据类型介绍与分析-开源基础软件社区

浮点型和定点型可以用类型名称后加(M,D)来表示,M表示该值的总共长度,D表示小数点后面的长度,M和D又称为精度和标度。

float和double在不指定精度时,默认会按照实际的精度来显示,而DECIMAL在不指定精度时,默认只取整数,小数丢弃。

同样的,我们来测试下三个类型的使用:

mysql> insert into accuracy values (1,1,1),(2.124,2.124,2.124),(3.125,3.125,3.125),(4.115,4.115,4.115),(5.136,5.136,5.136);
  Query OK, 5 rows affected, 4 warnings (0.07 sec)
  Records: 5  Duplicates: 0  Warnings: 4
 
 mysql> select * from accuracy;
 +------+------+------+
 | a    | b    | c    |
 +------+------+------+
 | 1.00 | 1.00 | 1.00 |
 | 2.12 | 2.12 | 2.12 |
 | 3.12 | 3.12 | 3.13 |
 | 4.12 | 4.12 | 4.12 |
 | 5.14 | 5.14 | 5.14 |
 +------+------+------+
 5 rows in set (0.00 sec)

 从这个执行结果,总结如下:

1、c是decimal类型,采用的是四舍五入

2、a和b分别为float 和double,采用的是四舍六入五成双

说明下四舍六入五成双:5以下舍弃,5以上进位,遇到5的时候,如果5后面还有不为0的任何数字,进位,如果没有,需要检查5前面的数字,奇数进位,偶数丢弃,观察结果中 3.125和4.115,可以得到规律。

 

我们再将浮点类型的(M,D)精度和标度都去掉,结果如下: 

mysql> create table accuracy2(a float,b double,c decimal);
 Query OK, 0 rows affected
 
 mysql> insert into accuracy2 values (1,1,1),(1.23,1.23,1.23);
 Query OK, 2 rows affected
 Records: 2  Duplicates: 0  Warnings: 1
 
 mysql> select * from accuracy2;
 +------+------+---+
 | a    | b    | c |
 +------+------+---+
 |    1 |    1 | 1 |
 | 1.23 | 1.23 | 1 |
 +------+------+---+
 2 rows in set

总结如下:

如果浮点数float、double如果不写精度和标度,则会按照实际的结果显示,而decimal会将小数四舍五入,并且插入时发出警告信息。

float,double等非标准类型,在DB中保存的是近似值,而decimal则以字符串的形式保存准确的数值,比如银行、金融系统之类的对统计精度要求比较高的,建议采用decimal。 

日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

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

MySQL全面瓦解3:数据类型介绍与分析-开源基础软件社区

字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

MySQL全面瓦解3:数据类型介绍与分析-开源基础软件社区

注意点:

1、CHAR(N) 和 VARCHAR(N) 中N代表字符的个数,但并不代表字节个数,比如 CHAR(10) 代表可以存储 10 个字符。

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

如果存放的数据为固定长度的建议使用char类型,如:手机号码、身份证等固定长度的信息。

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

4、BLOB是一个二进制大对象,可以容纳可变数量的数据,包含4 种类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB,区别是可容纳存储范围不同。

5、TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际业务场景的选择。  

MySQL、Java类型对照

MySQL全面瓦解3:数据类型介绍与分析-开源基础软件社区

MySQL全面瓦解3:数据类型介绍与分析-开源基础软件社区

MySQL全面瓦解3:数据类型介绍与分析-开源基础软件社区

使用建议
不建议选择较大的数值范围,尽量选择合适的数据范围,越小的数据类型会更快,占用磁盘,内存和CPU缓存也更小。

越简单的类型执行的成本越低,比如整型比字符类型操作代价要小得多,很多固定范围的文本内容字段可以用整型表示。

尽量制定列为NOT NULL,有NULL的列值会使得索引、索引统计和值比较更加复杂。

浮点类型的建议统一选择decimal,精度会好很多。

记录时间的建议使用时间戳格式,存储方便,索引高效

 

 

文章转载自公众号:架构与思维

已于2022-8-1 19:15:11修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐