Mysql基础篇（3）—— MySQL数据库类型
整数类型
| 类型名 | 占用字节 | 有符号范围 | 无符号范围(UNSIGNED) |
|---|---|---|---|
| TINYINT | 1 | -128~127 | 0~255 |
| SMALLINT | 2 | -32768~32767 | 0~65535 |
| MEDIUMINT | 3 | -8388608~8388607 | 0~16777215 |
| INT、INTEGER | 4 | -2147483648~2147483647 | 0~4294967295 |
| BIGINT | 8 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
浮点类型
| 类型名 | 占用字节 | 有符号范围 | 无符号范围(UNSIGNED) |
|---|---|---|---|
| FLOAT | 4 | （-3.4E+38，-1.17E-38），0，（1.17E-38，3.4E+38） | 0，（1.17E-38，3.4E+38） |
| DOUBLE | 8 | （-1.7E+308，-2.2E-308），0，（2.2E-308，1.7E+308） | 0，（2.2E-308，1.7E+308） |
ps：
-
mysql存储浮点数的格式为：符号(S)、尾数(M)和阶码(E)。有无符号，正数的范围是一样的
-
尾数是二进制存储的，除了以5结尾的小数，其他的都会有精度上的缺失。所以浮点数不要用“=”来判断两个数是否相等
-
不建议定义精度M和标度D
-
整数部分超出范围会直接报错，小数部分超出范围会四舍五入
定点数类型
DECIMAL
使用DECIMAL(M,D)方式方式表示高精度小数。0<=M<=65，0<=D<=30，D<M。DECIMAL的存储空间为M+2。
底层是以字符串的形式存储的，这就决定了它是精确的。
不指定MD，默认是DECIMAL(10, 0)。
定点数和浮点数
定点数精确但是通常情况占用空间相对较大(M大于2/6的时候就大于float/double的占用大小了)。
相反，浮点数通常情况下相对于定点数来说占用空间小，范围大但是不精确。
位类型
| 类型名 | 长度 | 长度范围 | 占用字节 |
|---|---|---|---|
| BIT(M) | M,默认1 | 1<=M<=64 | 约为(M + 7) / 8 个字节 |
### 日期与时间类型
| 类型名 | 介绍 | 占用字节 | 格式 | 最小值 | 最大值 |
|---|---|---|---|---|---|
| YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 |
| TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | 838:59:59 |
| DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 |
| DATETIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| TIMESTAMP | 日期时间 | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07UTC |
ps:
- TIMESTAMP存储数据的时候会转换成距离1970-01-01 00:00:01 UTC的毫秒值，查询的时候会根据查询时候所在的时区将毫秒值转换成时间，因此不同时区查看看一个数据会得到不同的结果。
- TIMESTAMP类型的日期比较相对于DATATIME来说较快。
文本字符串类型
| 类型 | 字符长度 | 长度范围（存储字节范围） | 占用存储空间 |
|---|---|---|---|
| CHAR(M) | M | 0<=M<=255 | M字符所占用的字节 |
| VARCHAR(M) | M | 0<=M<=65535 | M字符所占用的字节+1个字节 |
| TINYTEXT | L | 0<=L<=255 | L字符所占用的字节+2个字节 |
| TEXT | L | 0<=L<=65535 | L字符所占用的字节+2个字节 |
| MEDIUMTEXT | L | 0<=L<=16777215 | L字符所占用的字节+3个字节 |
| LONGTEXT | L | 0<=L<=4294967295 | L字符所占用的字节+4个字节 |
| ENUM | L | 1<=L<=65535 | 1或2个字节 |
| SET | L | 0<=L<=64 | 1,2,3,4或8个字节 |
CHAR和VARCHAR
| 类型 | 特点 | 空间上 | 时间上 | 试用场景 |
|---|---|---|---|---|
| CHAR(M) | 固定长度 | 浪费存储空间 | 效率高 | 存储不大，速度要求高 |
| VARCHAR(M) | 可变长度 | 节省存储空间 | 效率低 | 非CHAR的情况 |
具体存储引擎的情况：
- MyISAM：最好用CHAR，这样使得整个表静态化，以空间换时间
- MEMORY：底层都是用固定长度去存储数据，用啥都一样
- InnoDB：建议使用varchar类型，因为InnoDB内部行存储的格式并没有区分固定长度和可变长度（所有数据行都是用指向数据列值得头指针），而且主要影响性能因素是数据行使用的存储总量，所以一般情况除了简短并且固定长度的使用char，其他都使用varchar。
二级制字符串
| 类型 | 特点 | 值的长度 | 占用空间 |
|---|---|---|---|
| BINARY(M) | 固定长度 | M （0 <= M <= 255） | M个字节 |
| VARBINARY(M) | 可变长度 | M （0 <= M <= 65535） | M+1个字节 |
和char、varchar很像
二进制大对象
| 类型 | 长度 | 长度范围 | 占用空间 |
|---|---|---|---|
| TINYBLOB | L | 0 <= L <= 255 | L + 1 个字节 |
| BLOB | L | 0 <= L <= 65535（相当于64KB） | L + 2 个字节 |
| MEDIUMBLOB | L | 0 <= L <= 16777215 （相当于16MB） | L + 3 个字节 |
| LONGBLOB | L | 0 <= L <= 4294967295（相当于4GB） | L + 4 个字节 |
TEXT和BLOB的使用注意事项
-
BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的
" 空洞 "，以后填入这些"空洞"的记录可能长度不同。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理 -
把BLOB或TEXT列 分离到单独的表 中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会 减少主表中的碎片 ，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。
JSON类型
MYSQL5.7已经支持，MYSQL8.0加了自动验证的json文档和优化的存储结构。
空间类型
MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东西，可以是一个实体，例如一座山；可以是空间，例如一座办公楼；也可以是一个可定义的位置，例如一个十字路口等等。MySQL中使用 Geometry（几何） 来表示所有地理特征。Geometry指一个点或点的集合，代表世界上任何具有位置的事物。
MySQL的空间数据类型（Spatial Data Type）对应于OpenGIS类，包括单值类型：GEOMETRY、POINT、LINESTRING、POLYGON以及集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION 。