文章目录
- 数值类型
- 日期时间类型
- 字符串类型
- 不推荐存储到数据库的数据类型
MySQL 提供了多种数据类型,主要包括数值型、字符串类型、日期和时间类型。
数值类型
数值类型:tinyint、smallint、mediumint、int/integer、bigint、float、double、decimal
并增加了 bit 类型,用来存放位数据。
注意:
- 在整数类型中,如果保存的数值超出类型范围,会发生
Out of range
错误提示。为了避免此类问题发生,在选择数据类型时要根据应用的实际情况确定其取值范围。- 对于整型数据,MySQL 还支持在类型名称后面的小括号内指定显示宽度,例如 int(5) 表示当数值宽度小于 5 位的时候在数字前面填满宽度,如果不显示指定宽度则默认为 int(11)。一般配合 zerofill 使用,顾名思义,zerofill 就是用 “0” 填充的意思,也就是在数字位数不够的空间用字符 “0” 填满。如:
alert table test modify id int zerofill;
。如果这时插入大于宽度限制的值,会发生截断或者插不进去报错吗?答案是:不会对插入数据造成任何影响,还是按照类型的实际精度进行保存,只不过这时宽度已经任何意义,左边不填充0而已。- 所有的整数类型都有一个可选属性
UNSIGNED
(无符号),如果需要在字段里保存非负数或者需要较大上限值的时候,可以选用此选项,它的下限取 0 ,上限取原值的 2 倍。如果字段被指定为 zerofill ,则自动为该列添加UNSIGNED
属性。- 整数类型还有另一个属性:
AUTO_INCREMENT
。在需要产生唯一标识或顺序值时,可利用此属性,这个属性只用于整数类型。一个表最多只能有一个AUTO_INCREMENT
列,对于任何想要使用AUTO_INCREMENT
的列,应该定义为 NOT NULL,并定义为 PRIMARY KEY 或定义为 UNIQUE 键。- 浮点数(float、double)和定点数(decimal)都可以用类型名称后加
(M,D)
的方式来进行表示,(M,D)
表示该值一共显示M位数字(整数位+小数位),其中D位位于小数点后面,M和D又称为精度和标度。- 如果浮点数不写精度和标度,则会按照实际精度值显示,如果有精度和标度,则会自动将四舍五入后的结果插入,系统不会报错;定点数如果不写精度和标度,则按照默认值 decimal(10,0) 来进行操作,并且如果数据超越了精度和标度值,系统则会报错。
- bit 类型,用于存放位字段值,bit(m) 可以用来存放多位二进制数,m 范围从 1~ 64,如果不写则默认为1位(即只能保存0或者1)。数据插入时bit型字段时,首先转换为二进制,如果位数允许,将成功插入;如果位数小于实际定义的位数,则插入失败。对于位字段,直接使用 select 命令将不会看到结果,可以用 bin() (显示为二进制格式)或者 hex() (显示为十六进制格式)函数进行读取。
日期时间类型
日期时间类型:datetime、date、timestamp、time、year
每个时间类型有一个有效值范围和一个零值,当指定不合法的mysql不能表示的值时,使用零值
数据类型 | 零值表示 |
DATE | 0000-00-00 |
TIME | 00:00:00 |
YEAR | 0000 |
DATETIME | 0000-00-00 00:00:00 |
TIMESTAMP | 00000000000000 |
注意:
- YEAR 有 2 位或 4 位格式的年,默认是 4 位格式。在 2 位格式中,允许的值是 70 ~ 69 ,即 1970 ~ 2069年(MySQL 5.5.27以后就不支持了)
- 如果需要经常插入或者更新日期为当前系统时间,则通常使用 TIMESTAMP 来表示。TIMESTAMP 类型有专有的自动更新特性。MySQL 会给表中第一个 TIMESTAMP 字段创建 CURRENT_TIMESTAMP (当前系统日期),如果有第二个 TIMESTAMP 字段,则创建默认值为 0 值。TIMESTAMP 还有一个重要特点,就是和时区相关。当插入日期时,会先转换为本地时区后存放;而从数据库里面读取时,也同样需要将日期转换为本地时区后显示。这样,两个不同时区的用户看到的同一个日期可能是不一样的。
- 默认情况下,当MySQL遇到超出范围的日期/时间类型的值或该类型的其他无效值时,它会将该值转换为“零”值。
字符串类型
字符串类型:char、varchar、blob、text、binary、varbinary、enum、set
注意:
- char 存储定长字符串,存储时会使用空格补全右侧,读取时会截断末尾空格,最大长度为255个字符;varchar 存储可变长字符串,存储时需要加1个额外的字节存储长度,读取时保留末尾空格,最大长度为65532个字符;字符不区分中英文、数字、特殊字符;如果采用 gbk 字符集,1个字符占两个字节;如果采用 utf-8 字符集,1个字符占三个字节;如果插入值的长度超过设置的长度,将会报错
Data too long for column '*** at row *
(MySQL 5.7及以上)- binary 和 varbinary 类似于 char 和 varchar,不同的是它们只包含二进制字符串,而不包含非二进制字符串;
- enum 叫枚举类型,它的值范围需要在创建表时通过枚举方式显示指定,对 1~255 个成员的枚举需要1个字节存储;对于255~65535个成员,需要2个字节存储。插入数据时,enum 类型时忽略大小写的,但是不能插入空字符串和不在枚举集合里面的值,可以插入null(MySQL 5.7及以后)
- set 类型和 enum 类型非常类似,也是一个字符串对象,里面可以包含 0~64个成员。根据成员不同,存储上也不同。
- 1~8成员的集合,占1个字节;
- 9~16成员的集合,占2个字节;
- 17~24成员的集合,占3个字节;
- 25~32成员的集合,占4个字节;
- 33~64成员的集合,占8个字节;
set 类型和 enum 类型除了存储外,最主要的区别在于 set 类型 一次可以选取多个成员,而 enum 则只能选一个。对于 set 类型,只要插入的值是在允许的集合内,都可以正确插入;对于包含重复值的,则去重后插入;对于超出范围的则不能插入。
不推荐存储到数据库的数据类型
- 二进制多媒体数据
将二进制多媒体数据存放在数据库中,一个问题是数据库空间资源耗用非常严重,另一个问题是这些数据的存储很消耗数据库主机的CPU 资源。这种数据主要包括图片,音频、视频和其他一些相关的二进制文件。 这些数据的处理本不是数据的优势,如果我们硬要将他们塞入数据库,肯定会造成数据库的处理资源消耗严重。
对于图片,音频、视频的存储,如果说是特殊情况可以使用BLOB,但是通常来说跟推荐使用 varchar 存储资源的路径,而资源放在静态服务器中的文件夹中。 - 超大文本数据
对于 5.0.3 之前的 MySQL 版本,VARCHAR 类型的数据最长只能存放 255 个字节,如果需要存储更长的文本数据到一个字段,我们就必须使用 TEXT 类型(最大 可存放 64KB)的字段,甚至是更大的LONGTEXT 类型 (最大 4GB)。而 TEXT 类型数据的处理性能要远比 VARCHAR 类型数据的处理性能低下很多。从 5.0.3 版 本开始 ,VARCHAR 类型的最大长度被调整到 64KB 了,但是当实际数据小于 255Bytes 的时候,实际存储空间和实际的数据长度一样,可一旦长度超过 255 Bytes 之后,所占用的存储空间就是实际数据长度的两倍。
对于超大文本数据,如网络小说,一般的方案是将文本放在 txt 中,然后库里存放对应 txt 的地址。