数据库字段类型选择

标签：字符 存储 字节 数据库 数据类型 选择 类型 长度

原文链接：https://www.xygalaxy.com/navLink/blog/article/fc9d50688efb432ca5f4ce309bc8ac98

推荐个导航网站（强烈推荐）：https://www.xygalaxy.com/

前言

数据库设计一直都是比较复杂的，好的数据库设计能让项目开发起来更加的顺利，无需反复修改，修改数据库表对于开发人员来说真是折磨。
修改数据库表字段，也是一个比较常见的问题，其中修改字段类型可能更为常见，所以这里写一份字段类型参考对照表，如何选择字段类型，减少后期对于数据表的维护，减少数据库内存的开销，合理的选择数据库字段类型。其中也会列出相关的一些MySql和Oracle的区别。

数据库类型分类

• 整型
• 浮点数与定点数
• 文本
• 日期与时间

整型

无符号和有符号的区别

• 无符号：无符号（unsigned）表示设置的的数据为0或者正数；

• 有符号：有符号则可以是负数

现在的数据库软件工具默认都是有符号的，如何设置为无符号?

MySql（关键字：UNSIGNED）

CREATE TABLE `t1` (
  `id`   int UNSIGNED NOT NULL AUTO_INCREMENT ,
  `val`  int UNSIGNED NOT NULL DEFAULT 0 ,
   PRIMARY KEY (`id`)
);

Oracle（没有UNSIGNED，得使用检查约束）

create table t2 (
   id number primary key, 
   constraint t2_uint_id check (id between 0 and 4294967295)
);

整型数据类型参照表

MySql：提供了以下几种整型数据类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，依据使用场景进行选择即可

类型名称	有符号型取值范围	无符号型取值范围	存储大小	理解说明
TINYINT	-128~127	0~255	1个字节	只有几个类型时一般选择该类型，例如数据字典、枚举数据都不会太多，可以选择这个类型，例如：性别、状态、是否等
SMALLINT	-32768~32767	0~65535	2个宇节	可用于比较小的一些数据统计，但注意范围，例如公司电脑设备数量，但如果是大公司可能这个数量就会超过，就尽量选择更大的整型
MEDIUMINT	-8388608~8388607	0~16777215	3个宇节	可用于更大的数据统计，例如火车站单日人流量统计等
INT	-2147483648~2147483647	0~4294967295	4个宇节	可用于大返回比较广，一般能用前面几种的整型的这个都能用，但是为了能不浪费内存，还是能选择比较小的类型还是选择小一点的好
BIGINT	-9223372036854775808~9223372036854775807	0~18446744073709551615	8个宇节	只有处理特别大的数据量统计才会用到，例如双十一成交量，全球人数统计等

Oracle：没有对应的整型类型，它只有数值类型：NUMBER，NUMBER数据类型可以存储正数，负数，0，定点数和精度为38为的浮点数，具体是使用场景依据自己的需求制定即可

number(p, s)

说明

• p：表示精度，表示有效的位数，在1~38之间。有效数：从左边第一个不为0的数算起，小数点和负号不计入有效数。

• s：为范围，表示小数点右边数字的位数，它在-84 ~ +127之间。

• 规则：p大于等于s，精确到小数点右边S位也就是小数部分，超出的四舍五入。如果精确后的有效位数小于等于p，则正确，否则报错。

使用

具体使用	理解说明
number(1)	一位整数，可用于基本的枚举等，例如：性别、状态、是否等，不超过2位数
number(10)	10位数的整数，用于比较大的数据统计等，相当与MySql中的INT
number(3,3)	3位整数位，3位小数位，小数位不够补0，小数位超过4位则四舍五入，例如：125.200，152.123、155.1267->155.1268
number(3,4)	报错：不满足规则p大于等于s
number(38,15)	可用于股票交易计算等大精度的数据，银行账户余额等

浮点数与定点数

浮点数与定点数类型参照表

MySql：提供的浮点型与定点数有以下几种类型：FLOAT、DOUBLE、DECIMAL

类型名称	描述	存储大小	理解说明
FLOAT	单精度浮点数	4个字节	浮点数缺点精度不精确，会有误差，对精度要求高的不适用,适用场景如：温度、计算化学、身高体重等，对于数据的大小范围在：±131072（65536×2），都可以选择该类型，限制位数用法：float(m,d)，其中m最大为8位精度（4个字节），，例如：float(5,2)，浮点数最大长度为5，小数部分为2位，根据有无符号范围大小为：无符号（-99999.9至99999.9），有符号（0.0-99999.9）
DOUBLE	双精度浮点数	8个宇节	浮点数缺点精度不精确，会有误差，对精度要求高的不适用，用法同FLOAT一样，使用场景根据所需大小选择，大小范围：double(m,d) 其中m最大16位精度(8字节)
DECIMAL	16进制定点数	3个宇节	高精度，选用场景：银行金额、有关金融类的都可以选中该字段，decimal(m,d)，其中m<65 是总个数，d<30且 d<m 是小数位。

Oracle：参考Number即可，Oracle 没有浮点数这个概念。

文本

文本数类型参照表

MySql：提供的文本类型：CHAR、VARCHAR、TEXT、MEDIUMTEXT、LONGTEXT 、TINYTEXT 、BLOB、TINYBLOB、MEDIUMBLOB、LONGBLOB
说明
文本类型都是用于存储字符串类的数据，具体的不同点主要包含：编码格式、长度、限制
最大长度会依据不同的编码格式而变化，这里只做默认说明

• CHAR

1. 定长字符串，CHAR字段上索引效率极高，但是不适用于字符长度不确定的数据
2. CHAR(10)，插入一个长度为10个字节的字符串，不够会在前面自动补充空格，无论长度是否够10个字节的空间，都会占用10个字节的空间
3. 长度超过限制无法插入
4. 最大长度：CHAR(255)
5. 使用场景：已确定字段长度

• VARCHAR

1. 变长字符串，存储效率低于CHAR,但是平常用的比较多
2. VARCHAR(10),插入一个小于10个字节的字符串，字符串长度为2个字节则只占用两个字节
3. 长度超过限制无法插入
4. 最大长度：VARCHAR(65535)
5. 使用场景：大多数场景都用这个，只要长度够存储都可以用这个

• TEXT

1. 存储数据类型：可变长度的非Unicode字符数据数据
2. 最大长度：2^16-1个字符
3. 使用场景：

• MEDIUMTEXT

1. 存储数据类型：可变长度的非Unicode字符数据数据
2. 最大长度：16777215 (2^24-1) 个字符
3. 使用场景：

• LONGTEXT

1. 存储数据类型：可变长度的非Unicode字符数据数据
2. 最大长度：2147483647 (2^32-1) 个字符
3. 使用场景：

• TINYTEXT

1. 存储数据类型：可变长度的非Unicode字符数据数据
2. 最大长度： 255 (2^8-1) 个字符
3. 使用场景：

• BLOB

1. 存储数据类型：二进制数据
2. 最大长度： 65k
3. 使用场景：

• TINYBLOB

1. 存储数据类型：二进制数据
2. 最大长度：255k
3. 使用场景：

• MEDIUMBLOB

1. 存储数据类型：二进制数据
2. 最大长度：16M
3. 使用场景：

• LONGBLOB

1. 存储数据类型：二进制数据
2. 最大长度：4G
3. 使用场景：

补充

• NCHAR、NVARCHAR、NTEXT

1. 前面几种类型前加N。它表示存储的是Unicode数据类型的字符

说明

• unicode字符集是为了解决字符集不兼容的问题而产生的，所有字符都用两个字节表示，即英文字符也用两个字节表示。

• 以上的最大长度都是指字节长度，而非字符个数，如char(1)就连一个汉字都不能存放。

Oracle：数据类型和Mysql有些区别，提供的文本类型：char、nchar、varchar2，nvarchar2、long、clob、blob、bfile

• char

1. 定长字符类型（未达到指定长度时，自动在末尾用空格补全）；默认值为1；最大2000字节；非unicode。

• nchar

1. 定长字符类型（未达到指定长度时，自动在末尾用空格补全）；默认值为1；最大1000字节；根据unicode，所有字符都占两个字节。

• varchar2

1. 变长字符类型（未达到指定长度时，不自动补全空格）；定义时需指定长度；最大为4000字节；非unicode。

• nvarchar2

1. 变长字符类型（未达到指定长度时，不自动补全空格）；定义时需指定长度；最大为2000字节；根据unicode，所有字符都占两个字节。

• long

1. 大文本类型，用于存储变长字符串，最大长度为2GB。这是为了与早期版本兼容而保留的字符类型。

• clob

1. 存储字符，最大可以存4G
2. 用于存储单字节或多字节的大型字符串对象，支持使用数据库字符集的定长或变长字符。在 Oracle 12c中 CLOB 类型最大存储容量为128TB。

• blob

1. 存储图像、声音、视频等二进制数据，最大可存4G
2. 用于存储大型的、未被结构化的变长的二进制数据，如二进制文件、图片文件、音频和视频等非文本文件。在 Oracle 12c中 BLOB 类型最大存储容量为128TB。

• bfile

1. 用于存储指向二进制格式文件的定位器，该二进制文件保存在数据库外部的操作系统中。在 Oracle 12c中 BFILE 文件最大容量为128TB，不能通过数据库操作修改 BFILE 定位器所指向的文件。

danger 补充提示
Oracle还有一些其他的数据类型，日常工作可能用的比较少，有需要自行查找，如有遗漏的这里不再做补充，自行查找。
二进制类型：ROW、LONG ROW
行类型：ROWID

日期与时间

日期与时间类型参照表

MySql：提供的日期与时间有以下几种类型：YEAR、TIME、DATE、DATETIME、TIMESTAMP

字段类型	日期格式	范围	大小（字节）	理解说明
YEAR	YYYY	1901~2155	1	只需要记录年份的可选
TIME	HH:MM:SS	-838:59:59~838:59:59	3	只需要记录时间的可选
DATE	YYYY-MM-DD	1000-01-01~9999-12-3	3	只需要记录日期的可选
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8	最常用，如果对于时间精度只需要到秒的都可选，如果没要求，直接选这个就好了
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC~2038-01-1903:14:07 UTC	4	受时区限制，会受数据库时区的影响，有范围限制，其他和DATETIME差不多

精确到毫秒

• TIME、DATETIME、TIMESTAMP都是支持毫秒格式的，可以精确保存到毫秒，毫秒部分需要以参数形式传参给数据类型，默认是不保存毫秒的，可以保存1-6位。

• 参数形式：TIME(q)、DATETIME(q)、TIMESTAMP(q)，p默认不设置则不保存毫秒，范围：0<q<=6

• 数据类型设置：TIME(3)、DATETIME(3)、TIMESTAMP(3)，意思都是保留毫秒3位，最大可以保存6位。

• 类型选择：如果对于日期范围没什么限制，一般选用：TIMESTAMP，因为占用字节小，但不在乎这点内存，可以直接用DATETIME，其他所有的都可以选择DATETIME，这个是最通用的，当然还有些是将日期是设置为字符串的，这个就不推荐使用了，还是使用DATETIME好。

Oracle：提供的日期与时间有以下几种类型：DATE、TIMESTAMP

• DATE

1. 日期格式：YYYY/MM/DD、YYYY/MM/DD HH24:MI:SS，存储格式只有：YYYY/MM/DD HH24:MI:SS

• TIMESTAMP

1. 日期格式：YYYY/MM/DD HH24:MI:SS.sss
2. 与DATE没什么区别，只是精确到的位数不一样
3. 格式：TIMESTAMPL(q)，q 表示秒的小数位数，取值范围为0~9
4. 精确到秒的小数点后9位。表示时间戳，是 DATE 数据类型的扩展，允许存储小数形式的秒值。 p 表示秒的小数位数，取值范围为0~9，默认值为6。根据 p 值的设置不同， TIMESTAMP 类型数据占据7~11B空间，由世纪、年、月、日、时、分、秒组成，如30-MAY-1207.56.07.544000PM。

warning 数据库设计一些建议

• 能用小存储的尽量用小存储字段，比如：能用TINYINT尽量不用INT

• 能确认字段大小的就确认好字段大小，不能确认字段的，考虑极端情况，给最大的类型和大小，以免后期维护

• 字段考虑基本信息：创建人、创建时间、更新人、更新时间、状态、类型、是否逻辑删除、ID存储类型是UUID字符串、自增数值还是序列，考虑优缺点

推荐一个数据库设计软件
PDMan数据库建模官网

也可以直接在银河系XYGALAXY中直接搜索下载

标签：字符,存储,字节,数据库,数据类型,选择,类型,长度
From： https://www.cnblogs.com/ONLYZYLY/p/17119843.html