MySQL数据类型全解析

Wesley13
• 阅读 864

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

没写出的具体的精确值无符号的范围计算公式如下:

MySQL数据类型全解析

有符号的计算公式如下:

MySQL数据类型全解析

所有整形如果允许NULL的话默认为NULL,不允许为NULL的需要手动设置默认值.

MySQL数据类型全解析

如果插入或修改的数据范围不符合会提示操作失败,如上图提示列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

下面是测试的待插入的数据:

MySQL数据类型全解析

插入之后再查询:

MySQL数据类型全解析

可以看到,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:

MySQL数据类型全解析

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中的年月日插入时可以不分隔或者用各种符号进行分隔,比如

! @ # $ % ^ & * ( ) - / \ [ ] { } : ; ' " |  , . < > ?

MySQL数据类型全解析

上图中的date都是合法的,插入之后MySQL都会统一用横杠代替:

MySQL数据类型全解析

YEAR的话MySQL其实允许两位数的YEAR,其中00-69表示2000-2069,70-99表示1970-1999.

其他插入方式的话可以用CURRENT_DATE,CURRENT_DATE(),CURDATE(),NOW(),CURTIME(),CURRENT_TIME,CURRENT_TIME():

MySQL数据类型全解析

MySQL数据类型全解析

3.2 时间

时间就一种类型:TIME.

TIME所占的字节数为3个字节,取值范围为:

[-838:59:59,838:59:59]

插入时时分秒可以不分隔或者必须以冒号分隔.

MySQL数据类型全解析

插入后统一变为冒号分隔:

MySQL数据类型全解析

可以使用CURRENT_TIME或CURRENT_TIME(),CURTIME()或NOW()插入:

MySQL数据类型全解析

MySQL数据类型全解析

甚至也可以使用CURDATE(),不过此时插入的是零值:

MySQL数据类型全解析

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

会报错,范围不允许.

MySQL数据类型全解析

19700101080000

也不行.

MySQL数据类型全解析

在插入时可以指定默认值,使用now()或CURRENT_TIMESTAMP:

MySQL数据类型全解析

插入后DATETIME与TIMESTAMP存储的形式一致,横杠分隔日期,冒号分隔时间,时间与日期用空格隔开.

也可以使用CURTIME()或CURDATE()插入,对于CURTIME(),日期采用当前日期,对于CURDATE(),时间采用

00:00:00

MySQL数据类型全解析

MySQL数据类型全解析

MySQL数据类型全解析

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,超过了创建时指定的长度便插入失败:

MySQL数据类型全解析

MySQL数据类型全解析

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会存入这个编号而不是对应的值.

MySQL数据类型全解析

MySQL数据类型全解析

等价于

MySQL数据类型全解析

即取值列表的编号从1开始.

4.4 SET

SET占用1或2或3或4或8个字节,取决于里面元素的数量,最大数量为64.SET可以是一个或多个元素的组合,值后面多余的空格会被删除,取多个元素时,不同元素用逗号隔开.

如:

MySQL数据类型全解析

插入前:

MySQL数据类型全解析

插入后:

MySQL数据类型全解析

5 二进制类型

5.1 BIT

BIT在创建时指定最大长度:

BIT(M)

M表示最大二进制数的位数,M取值1-64,默认1,插入数据时,如果数据在BIT允许的范围则插入成功,否则插入失败.

插入时需要加上b前缀,后面跟的字符串由0与1组成:

MySQL数据类型全解析

查询之后就变成了十进制:

MySQL数据类型全解析

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:

MySQL数据类型全解析

这里的BINARY为3个字节,插入了a,等价是插入了

a\0\0

两个\0填充后面两个字节,因此,前面两个比较的结果都为0.

比较时按照二进制值进行比较,这与CHAR/VARCHAR有很大不同.

MySQL数据类型全解析

CHAR/VARCHAR比较的是字符值,忽略后面的空格,因此比较结果为1,但是BINARY比较的是二进制值,'a'的二进制值化为十六进制为61,'a '的二进制值化为十六进制为6120,因此比较结果为0.

6 JSON类型

JSON存储的数据不能是NULL,只能是JSON格式的数据,否则会报错,

创建很简单,直接为JSON类型:

MySQL数据类型全解析

插入时可以使用一个JSON对象或JSON数组,数组用方括号,一个对象用花括号:

MySQL数据类型全解析

MySQL同时提供了一组操作JSON的函数,具体看这里.

这里演示了json_object函数,用来创建一个JSON对象:

MySQL数据类型全解析

插入后:

MySQL数据类型全解析

7 空间数据类型

Spatial数据,又叫空间或几何数据,包含的数据类型有

  • GEOMETRY
  • POINT
  • LINESTRING
  • POLYGON

其中GEOMETRY可用于存储任意几何类型.其他集合类型的空间数据类型如下:

  • MULTIPOINT
  • MULTILINESTRING
  • MULTIPOLYGON
  • GEOMETRYCOLLECTION

其中GEOMETRYCOLLECTION可以存储任意类型的集合.

下面仅对POINT的使用作简单介绍.

MySQL数据类型全解析

插入数据使用st_geomfromtext()函数,参数是一个point(),里面的参数标识x,y坐标,空格分隔.

查询时直接

SELECT * FROM

会返回BLOB数据,需要使用st_astext(),参数为列名.

增加几行数据后,继续查询:

MySQL数据类型全解析

MySQL数据类型全解析

使用了st_x()与st_y()两个函数,分别取得point的x,y坐标.

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
皕杰报表(关于日期时间时分秒显示不出来)
在使用皕杰报表设计器时,数据据里面是日期型,但当你web预览时候,发现有日期时间类型的数据时分秒显示不出来,只有年月日能显示出来,时分秒显示为0:00:00。1.可以使用tochar解决,数据集用selecttochar(flowdate,"yyyyMMddHH:mm:ss")fromtablename2.也可以把数据库日期类型date改成timestamp
梦想橡皮擦 梦想橡皮擦
3年前
2. 无门槛学会数据类型与输入、输出函数,滚雪球学 Python
学习一门语言应该是一件充满乐趣的事情,为什么要把它变难呢?二、Python基本数据类型与输入、输出函数2.1基本数据类型对于任何一门语言都存在一些基本类型,Python也不例外,只不过数据类型在Python中要远远比其他语言简单,基本分为以下几种。1.数值数据类型,常见的是整数和浮点数2.布尔值数据类型,就是常说的真和假
Wesley13 Wesley13
3年前
Mysql系列第二讲 详解mysql数据类型(重点)
Mysql系列第二讲MySQL的数据类型整数类型示例1:有符号类型示例2:无符号类型类型(n)说明示例1(重点)示例2日期类型字符串类型mysql类型和java类型对应关系数据类型选择的一些建议MySQL的数据类型
Wesley13 Wesley13
3年前
mysql中时间比较的实现
MySql中时间比较的实现unix\_timestamp()unix\_timestamp函数可以接受一个参数,也可以不使用参数。它的返回值是一个无符号的整数。不使用参数,它返回自1970年1月1日0时0分0秒到现在所经过的秒数,如果使用参数,参数的类型为时间类型或者时间类型的字符串表示,则是从1970010100:00:0
Wesley13 Wesley13
3年前
Mysql 数据类型、约束类型
mysql数据类型MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。数值类型MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUME
Wesley13 Wesley13
3年前
MySQL数据库DDL、DML详解
一、MySQL数据库数据类型<1MySQL数据库中支持多种数据类型数值型字符型日期型<2常用的数据类型整型int:整型,存储整数int(M):M表示预期值,与存储大小和数值的范围无关idi
Wesley13 Wesley13
3年前
Java基础(二)数据类型
  数据类型主要分为基本类型和引用类型两大类。  一、基本类型  1.基本类型又分为数值类型和boolean类型,  (1)数值类型包括浮点数类型、整数类型和字符类型  整型                                          浮点型(初始化时需要加f或d)  字符类型  byte    
Wesley13 Wesley13
3年前
MYSQL学习笔记(一)数据类型
MySQL数据类型一、数值类型1.整型类型大小范围(有符号:默认)范围(无符号)TINYINT1字节(128,127)(0,255)SMALLINT2字节(32768,32767)(0,65535)MEDIUMINT