数据库设计-数据库表字段类型分析

100 阅读 0 评论 66 点赞

我是靠谱客的博主朴实云朵，最近开发中收集的这篇文章主要介绍数据库设计-数据库表字段类型分析，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、字符串类型
char 和 varchar 都是用来存储字符串类型的数据，但是他们保存和检索的方式不一样，char 属于固定长度的字符类型，varchar 属于可变长的字符类型。

gbk 一个字符，两个字节

例如：

值	char(4)	字节	varchar(4)	字节
‘’	‘’	8个字节	’‘	1个字节
‘ab’	‘ab’	8个字节	‘ab’	5个字节
‘abcd’	‘abcd’	8个字节	‘abcd’	9个字节
‘abcdefg’	‘abcd’	8个字节	‘abcd’	9个字节

utf8 一个字符，三个字节

例如：

值	char(4)	字节	varchar(4)	字节
‘’	‘’	12个字节	’‘	1个字节
‘ab’	‘ab’	12个字节	‘ab’	7个字节
‘abcd’	‘abcd’	12个字节	‘abcd’	13个字节
‘abcdefg’	‘abcd’	12个字节	‘abcd’	13个字节

由于 char 是固定长度的，所以它的处理速度比 varchar 快得多，但是其缺点是浪费存储空间，程序需要对尾行空格进行处理，所以对那些变化不大并且查询速度有较高的要求的数据可以考虑使用 char 类型存储。

在 MySQL 中，不同的存储引擎对 char 和 varchar 的使用原则有所不同。

MyISAM 存储引擎

建议使用固定长度的数列代替可变长度的数据列

InnoDB 存储引擎

建议使用 varchar 类型，对于 InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度，因此使用 char 列不一定比可变长度的 varchar 性能好
由于 char 平均占用空间多于 varchar ，因此 varchar 要消化需要处理的数据行的存储总量和磁盘 I/O 是比较好的

二、数字类型

数据类型	范围	字节
bigint	-2^63 (-9,223,372,036,854,775,808) 到 2^63-1(9,223,372,036,854,775,807)	8字节
	0到18446744073709551615	较大整数
int	-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)	4个字节
	0到4294967295	标准整数
smallint	-2^15 (-32,768) 到 2^15-1 (32,767)	2字节
	0到65535	较小整数
tinyint	-2^7 (-128) 到 2^7 - 1 (123)	1字节
	0到255	非常小的整数
float[(m,d)]	±1.175494351e - 38 （单精度浮点数）	4字节
double[(m,d)]	±2.2250738585072014e - 308 （双精度浮点数）	8字节
decimal(m,d)	可变；其值的范围依赖于m 和d （一个串的浮点数）	m+2字节

id 必为主键，类型为 int bigint unsigned ，单表时自增、步长为 1；注意一下因为一些表可能因为数据量的关系导致主键会很大可能会超出 int 的范围这个时候就比较建议使用 bigint ，通常 int 即可。

注：不过当一个表数据量超过了500万的时候或者单表容量超过 2GB 的时候推荐分库分表；这一步操作是需要事先对于数据量在项目上线之后的思考点

UNSIGNED 属性就是将数字类型无符号化，unsigned 的使用

注意 unsigned tinyint 的范围就是 0 - 255

可以看到上图，0 - 255 内的数是可以正常写入数据表，而超出 0 -255 范围的是会报错 1264 (22003): Out of range value for column ‘id’ at row 1 超出范围。

人的年龄可以使用 unsigned tinyint（表示范围 0 - 255，人的寿命在目前为止是不会超过 255 岁的）。

status , sexuality 相对来说在系统中我们更愿意使用数字作为表示，代替实际该字段所传达的意思，因为 char 所占用的 3 个字节，相对来说 status , sexuality 比较适合使用 unsigned tinyint 。

三、时间类型

注意：默认情况下，当MySQL遇到超出范围的日期或时间类型的值或该类型的其他无效值时，它会将值转换为 “零” 值的值。唯一的例外是超出了范围。time 值被裁剪到 time 的范围。

MySQL 允许在 date 或 datetime 列。这对于需要存储你可能不知道确切日期的生日的应用程序非常有用。在这种情况下，你只需将日期存储为 ‘2009-00-00’ 或 ‘2009-01-00’。如果存储这样的日期，就不应该期望得到正确的结果，例如：DATE_SUB() 或 DATE_ADD() 需要完整的日期。若要在日期中不允许零个月或日部分，请启用 NO_ZERO_IN_DATE 模式。

MySQL 允许你存储 “零” 假值 “0000-00-00”。在某些情况下，这不使用 NULL 值，并使用较少的数据和索引空间。不允许 ‘0000-00-00’，启用 NO_ZERO_DATE 模式。

DATETIME

DATETIME 用于表示年月日时分秒，是 DATE 和 TIME 的组合，并且记录的年份比较长久。如果实际应用中有这样的需求，就可以使用 DATETIME 类型。

DATETIME 列可以设置为多个，默认可为null，可以手动设置其值。
DATETIME 列不可设定默认值。
DATETIME 列可以变相的设定默认值，比如通过触发器、或者在插入数据时候，将 DATETIME 字段值设置为 now() ，这样就可以做到了，尤其是后者，在程序开发中常常用到。

TIMESTAMP

TIMESRAMP 用于表示年月日时分秒，但记录的年份比较短暂，TIMESTAMP 列必须有默认值，默认值可以为 “0000-00-00 00:00:00”，但不能为 null 。TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放：当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的。表中的第一个 TIMESTAMP 列自动设置为系统时间（CURRENT_TIMESTAMP）。当插入或更新一行，但没有明确给 TIMESTAMP 列赋值，也会自动设置为当前系统时间。如果表中有第二个 TIMESTAMP 列，则默认值设置为 0000-00-00 00:00:00。TIMESTAMP 的属性受MySQL版本和服务器 SQLMode 的影响较大。如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。

注：一般建表时候，创建时间用 datatime ，更新时间用 timestamp。

DATA

DATE 用于表示年月日，如果实际应用值需要保存年月日就可以使用 DATE。

TIME

TIME 用于表示时分秒，如果实际应用值需要保存时分秒就可以使用 TIME。

YEAR

YEAR 用于表示年份，YEAR 有 2 位（最好使用4位）和 4 位格式的年。默认是 4 位。如果实际应用只保存年份，那么用 1 Bytes 保存 YEAR 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。