1 概述
本文主要介绍了MySQL的各种数据类型,版本为8.x,
MySQL的数据类型可以分为六类:数值类型,日期时间类型,字符串类型,二进制类型,JSON类型与空间数据类型.
2 数值类型
数值类型是最基本的类型,可以分为整数类型与浮点数类型.
2.1 整型
| 类型 | 字节数 | 无符号范围 | 有符号范围 |
|---|---|---|---|
| TINYINT | 1 | [0,255] | [-128,127] |
| SMALLINT | 2 | [0,65535] | [-32768,32767] |
| MEDIUMINT | 3 | 0-1677w | ±838w |
| INT/INTEGER | 4 | 0-42e | ±21e |
| BIGINT | 8 | 0-18446744e | ±9223372e |
没写出的具体的精确值无符号的范围计算公式如下:
有符号的计算公式如下:
所有整形如果允许NULL的话默认为NULL,不允许为NULL的需要手动设置默认值.
如果插入或修改的数据范围不符合会提示操作失败,如上图提示列c_mediumint的数据超出范围.
2.2 浮点型
可以用两种方式来表示小数,分别是浮点数类型与定点数类型.
浮点数用于不需要太精确的小数场合,类型包括FLOAT与DOUBLE,定点数类型就是DECIMAL.
| 类型 | 字节数 | 范围 |
|---|---|---|
| FLOAT | 4 | ± 1.17e-38 - 3.4e+38 和 0 |
| DOUBLE | 8 | ± 2.22e-308 - 1.79e+308 和 0 |
| DEC(M,D)/DECIMAL(M,D) | 取决于M与D | 取决于M与D |
下面是测试的待插入的数据:
插入之后再查询:
可以看到,float的精度最高为6位,后面的进行四舍五入,DOUBLE的最高精度为15位.
下面来说一下DECIMAL.
2.3 DECIMAL
DECIMAL也可用DEC来表示,用于计算精确的小数值,DECIMAL通常如下表示:
DECIMAL(M,D)
| 类型 | 含义 | 取值范围 | 默认值 | |
|---|---|---|---|---|
| M | 十进制有效数字总个数 | 1-65 | 10 | |
| D | 小数点后十进制数字个数 | 0-30(D<=M) | 0 |
如果要计算所占用的字节数,需要分为整数与小数两部分,分开计算再相加,每部分又按照9位数字去拆分,计算规则如下:
| 位数 | 占用字节数 |
|---|---|
| 1-2 | 1 |
| 3-4 | 2 |
| 5-6 | 3 |
| 7-9 | 4 |
也就是说对于
DECIMAL(24,8)
整数部分24-8=16位,小数部分8位,小数部分的8位根据规则所占用的字节数为4个,整数部分按每9位进行拆分,16=9+7,9位的部分占4个字节,7位的部分按照规则也占4个字节,所以整数部分占8个字节,加上小数部分的4个字节,总共占12个字节.
至于NUMERIC,在MySQL中,NUMERIC被实现为DECIMAL:
3 日期与时间类型
总共有五类:YEAR,TIME,DATE,DATETIME,TIMESTAMP.
3.1 日期
日期有两种类型:YEAR与DATE,YEAR表示年,DATE表示日期.
| 类型 | 字节数 | 取值范围 |
|---|---|---|
| YEAR | 1 | [1901,2155] |
| DATE | 3 | [1000-01-01,9999-12-31] |
DATE中的年月日插入时可以不分隔或者用各种符号进行分隔,比如
! @ # $ % ^ & * ( ) - / \ [ ] { } : ; ' " | , . < > ?
上图中的date都是合法的,插入之后MySQL都会统一用横杠代替:
YEAR的话MySQL其实允许两位数的YEAR,其中00-69表示2000-2069,70-99表示1970-1999.
其他插入方式的话可以用CURRENT_DATE,CURRENT_DATE(),CURDATE(),NOW(),CURTIME(),CURRENT_TIME,CURRENT_TIME():
3.2 时间
时间就一种类型:TIME.
TIME所占的字节数为3个字节,取值范围为:
[-838:59:59,838:59:59]
插入时时分秒可以不分隔或者必须以冒号分隔.
插入后统一变为冒号分隔:
可以使用CURRENT_TIME或CURRENT_TIME(),CURTIME()或NOW()插入:
甚至也可以使用CURDATE(),不过此时插入的是零值:
3.3 日期+时间
TIMESTAMP与DATETIME:
| 类型 | 字节数 | 取值范围 |
|---|---|---|
| DATETIME | 8 | [1000-01-01 00:00:00,9999-12-31 23:59:59] |
| TIMESTAMP | 4 | [19700101080001,20380119111407] |
DATETIME就是DATE与TIME的结合,先DATE再TIME,空格分隔.
TIMESTAMP表示时间戳,表示当前时间到1970年1月1日0时0分0秒经过的秒数,中的写法是年月日时分秒,比如
19700101080001
表示
1970年1月1日8时0分01秒
这里之所以使用080001是因为中国的时区.
19700101000000
会报错,范围不允许.
19700101080000
也不行.
在插入时可以指定默认值,使用now()或CURRENT_TIMESTAMP:
插入后DATETIME与TIMESTAMP存储的形式一致,横杠分隔日期,冒号分隔时间,时间与日期用空格隔开.
也可以使用CURTIME()或CURDATE()插入,对于CURTIME(),日期采用当前日期,对于CURDATE(),时间采用
00:00:00
4 字符串类型
4.1 CHAR/VARCHAR
| 类型 | 最大长度 | 特点 |
|---|---|---|
| CHAR | 255个字符 | 长度固定,CHAR(M)表明CHAR的长度为M个字符,所占用的字节数为字符个数*每个字符所占用字节数,也就是说与字符集有关,定长的效率较高,一般用于存储固定位数的数据或经常需要更新的数据,如电话号码,身份证,MD5值等.保存CHAR时,会在末尾填充空格用于对齐,查询时自动把空格删除 |
| VARCHAR | 65535字节,字符个数为(最大行大小-NULL标识-长度标识)/字符集单字符最大字节数,可以看这里 | 长度不固定,实际占用的空间为字符串实际长度+1/2,也就是说需要额外的1或2个字节记录字符串的长度.相对来说VARCHAR比CHAR更节省空间,但是在UPDATE时可能会使行变得更长,需要做额外的工作.VARCHAR适合存储字段更新少,字符串平均长度不稳定的情况 |
无论是CHAR还是VARCHAR,超过了创建时指定的长度便插入失败:
4.2 TEXT
TEXT是一种专用于存储长文本的变长数据类型,包括TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT:
| 类型 | 允许长度(字节) | 存储空间 |
|---|---|---|
| TINYTEXT | [0,255] | 长度+2字节 |
| TEXT | [0,65535] | 长度+2字节 |
| MEDIUMTEXT | 0-1677w | 长度+3字节 |
| LONGTEXT | 0-42e | 长度+4字节 |
TEXT在插入时超过最大允许长度会插入失败.
4.3 ENUM
枚举类型,在创建表时enum的取值范围以列表的形式指定:
ENUM('value1','value2',...,'valuen')
ENUM占用1或2个字节,列表中最多能有65535个值,每个值末尾的空格都会被删除,列表中的每个值独有一个顺序排列的编号,MySQL会存入这个编号而不是对应的值.
等价于
即取值列表的编号从1开始.
4.4 SET
SET占用1或2或3或4或8个字节,取决于里面元素的数量,最大数量为64.SET可以是一个或多个元素的组合,值后面多余的空格会被删除,取多个元素时,不同元素用逗号隔开.
如:
插入前:
插入后:
5 二进制类型
5.1 BIT
BIT在创建时指定最大长度:
BIT(M)
M表示最大二进制数的位数,M取值1-64,默认1,插入数据时,如果数据在BIT允许的范围则插入成功,否则插入失败.
插入时需要加上b前缀,后面跟的字符串由0与1组成:
查询之后就变成了十进制:
5.2 BLOB
BLOB是特殊的二进制数据类型,用来保存数据量很大的二进制数据,如图片等,BLOB包括TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOG,与TEXT类似:
| 类型 | 允许长度(字节) | 存储空间 |
|---|---|---|
| TINYBLOB | [0,255] | 长度+2字节 |
| BLOB | [0,65535] | 长度+2字节 |
| MEDIUMBLOB | 0-1677w | 长度+3字节 |
| LONGBLOB | 0-42e | 长度+4字节 |
5.3 BINARY/VARBINARY
BINARY(M)/VARBINARY(M)中的M指的是字节长度,由于BINARY/VARBINARY存储的是二进制字符串,因此没有字符集的概念.
| 类型 | 最大长度(字节) | 特点 |
|---|---|---|
| BINARY | 255字节 | 定长,允许长度0-M的二进制字符串,不足最大长度的空间由0x00补全,查询时不会截断后面的0x00,比较时所有字节都有效 |
| VARBINARY | 65535字节 | 变长,不会在后面填充0x00. |
下面的例子演示了插入时填充0x00:
这里的BINARY为3个字节,插入了a,等价是插入了
a\0\0
两个\0填充后面两个字节,因此,前面两个比较的结果都为0.
比较时按照二进制值进行比较,这与CHAR/VARCHAR有很大不同.
CHAR/VARCHAR比较的是字符值,忽略后面的空格,因此比较结果为1,但是BINARY比较的是二进制值,'a’的二进制值化为十六进制为61,'a '的二进制值化为十六进制为6120,因此比较结果为0.
6 JSON类型
JSON存储的数据不能是NULL,只能是JSON格式的数据,否则会报错,
创建很简单,直接为JSON类型:
插入时可以使用一个JSON对象或JSON数组,数组用方括号,一个对象用花括号:
MySQL同时提供了一组操作JSON的函数,具体看这里.
这里演示了json_object函数,用来创建一个JSON对象:
插入后:
7 空间数据类型
Spatial数据,又叫空间或几何数据,包含的数据类型有
- GEOMETRY
- POINT
- LINESTRING
- POLYGON
其中GEOMETRY可用于存储任意几何类型.其他集合类型的空间数据类型如下:
- MULTIPOINT
- MULTILINESTRING
- MULTIPOLYGON
- GEOMETRYCOLLECTION
其中GEOMETRYCOLLECTION可以存储任意类型的集合.
下面仅对POINT的使用作简单介绍.
插入数据使用st_geomfromtext()函数,参数是一个point(),里面的参数标识x,y坐标,空格分隔.
查询时直接
SELECT * FROM
会返回BLOB数据,需要使用st_astext(),参数为列名.
增加几行数据后,继续查询:
使用了st_x()与st_y()两个函数,分别取得point的x,y坐标.