原文在我的 Github 上,欢迎订阅。
前言
本文接着上篇 Mysql入门第一课《建表、改表、删表》 继续学习。
要建一个优秀的表,选择合适的数据类型非常重要,如果数据类型选择不当,不仅开发起来给自己找麻烦,而且还会造成数据库性能低下。
比如给student
(学生表)添加age
字段,选择TINYINT
类型就够了,它的范围是 0-255
(无符号) 比较适合,如果使用 INT
也可以满足条件,但INT
占 4 个字节,而TINYINT
只占 1 个字节,相比较当然TINYINT
性能更好。
刚才提到了UNSIGNED
(无符号),我会在下文说明。
数据类型
Mysql
支持多种类型,大致分为三类:数值、字符串、日期/时间类型。
我们各个击破
数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1字节 | (-128, 127) | (0, 255) | 小整数值 |
SMALLINT | 2字节 | (-32768, 32767) | (0, 65535) | 大整数值 |
MEDIUMINT | 3字节 | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8字节 | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4字节 | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度浮点数值 |
DOUBLE | 8 字节 | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) |
0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D, 为M+2否则为D+2 |
依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
这里解释下上面提到的有符号
、无符号
:
- 有符号:默认为有符号,其实就是从负数到正数的取值范围
- 无符号(UNSIGNED):没有负数,最低从 0 开始
对于平常开发来说,整数类型其实到 INT
的数值范围已经很大了。
建表时经常看到类似 INT(5)
后面有个 5,它表示显示宽度(M),M 的值不能大于取值范围长度。
举个例子: 如果age
字段类型是INT(5) UNSIGNED ZEROFILL
,插入一条数据age
为99,最后显示为:00099
UNSIGNED
为无符号, ZEROFILL
的作用是用 0 填充没有数字的位置。
我问过一些同事,在开发时为了方便,很多字段应该用数字类型 他们选择用字符串类型。这句话看看就好
字符串类型
字符串类型是建表时最最最常用的,下面看下它有哪些类型:
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255字节 | 定长字符串 |
VARCHAR | 0-65535 字节 | 变长字符串 |
TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255字节 | 短文本字符串 |
BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
TEXT | 0-65 535字节 | 长文本数据 |
MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
通常情况下,二进制的数据用的很少,一般像图片、音频都是存在 CDN 或 云服务器里,用的比较多的就是CHAR
、VARCHAR
、TEXT
了。
光看表格没啥概念,但可以知道字符串主要以字节来提现大小,我们开发中用的字符串一般就是英文字母和汉字,那就需要知道字节与它们的关系:
在 Mysql 的UTF8
编码下:
- 1 个英文字母(包括大小写)占 1 个字节
- 1 个汉字占 3 个字节
所以当我们存名称、简介和文章时,可以通过占用字节数选择合适的类型了,完美。
日期/时间类型
这个类型我们用的也很多,像生日、创建时间、修改时间等等都需要它。
类型 | 大小 | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3字节 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3字节 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1字节 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8字节 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4字节 | 1970-01-01 00:00:00/2038 (结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07) | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
在开发中,常用的是 DATETIME
和 TIMESTAMP
也有使用INT
来记录时间,下面从可读性、存储空间、操作性上来分析:
- 可读性,INT 可读性最差,显示时需要使用代码进行格式转换,没有
DATETIME
和TIMESTAMP
直观。 - 存储空间,INT 和 TIMESTAMP 最小 都是 4 个字节,DATETIME 占 8 个字节。
- 操作性,在平时,我们对日期的操作有读、写、比较、计算。 读写大家都一样,没啥区别;在比较和计算上
INT
要方便很多,可以直接比较,加减等运算,其余两种需要利用代码工具进行计算和比较,此时性能最好的是INT
综合考虑,个人比较偏向TIMESTAMP
,占用空间小,可读性强,如果对性能不是非常苛刻,在代码帮助下操作也很简单,但使用时要考虑它的时间范围!
番外
结合 Mysql入门第一课《建表、改表、删表》 和本篇文章,有几处出现了约束条件
,这里有必要说一下:
- UNSIGNED :无符号,值从0开始,无负数
- ZEROFILL:零填充,当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
- NOT NULL:非空约束,表示该字段的值不能为空
- DEFAULT:表示如果插入数据时没有给该字段赋值,那么就使用默认值
- PRIMARY KEY:主键约束,表示唯一标识,不能为空,且一个表只能有一个主键。一般都是用来约束id
- AUTO_INCREMENT:自增长,只能用于数值列,而且配合索引使用,默认起始值从1开始,每次增长1
- UNIQUE KEY:唯一值,表示该字段下的值不能重复,null除外。比如身份证号是一人一号的,一般都会用这个进行约束
- FOREIGN KEY:外键约束,目的是为了保证数据的完成性和唯一性,以及实现一对一或一对多关系
总结
本篇文章主要介绍数据类型 以及在开发中 如何使用合适的数据类型,然后在番外
中介绍了下建表时出现的条件约束
。
下篇文章将开始 Mysql入门第三课《数据的增删改》 欢迎阅读。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。