MySQL数据类型与约束 数值字符串日期

在数据库中，数据表用来组织和保存各种数据，它是由表结构和数据构成的，在设计表结构时，经常需要根据实际需求选择合适的数据类型与约束

数字类型

整数类型

MySQL中的整数类型用于保存整数，根据取值范围不同选择的整数类型不同

不同的整数类型占用字节数和取值范围不同，如果需要使用无符号数据类型，需要在数据类型右边加上UNSIGNED关键字。

zerofill零填充

首先来讲讲显示宽度，显示宽度是专门为数据类型创建的格式显示规则，显示宽度不影响你存储的数值，它并不是取值范围

MySQL的每一个整数类型都有其默认的显示宽度，tinyint的显示宽度为4，int的显示宽度为11

对于有符号类型，符号也占用一个宽度，比如unsigned int的宽度为10

显示宽度需要配合zerofill使用，如果你为字段设置了零填充，若数值宽度小于显示宽度，则会在数值左侧填充0；如果数值超过显示宽度则不会填充0。注意：设置零填充后字段自动设为无符号类型。

浮点数

在MySQL中，小数都是通过浮点数或者定点数来表示的，浮点数分为float（单精度）和double（双精度）两种。

浮点数虽然取值范围很大，但是精度并不高。float的精度为6位或7位，double的精度为15位；超出精度会导致给定的数值与实际保存的数值不一致（四舍五入）。

定点数

定点数类型通过decimal（M，D）设置位数和精度，M表示数字总位数，默认值为10；D表示小数点后面的位数，最大值为30，默认值为0.

例如decimal（5，2）的取值范围就是-999.99-999.99，系统会自动根据存储的数据来分配存储空间。如果小数部分超出范围会进行四舍五入，如果整数部分超出范围，数据会插入失败。

Bit类型

BIT(M)用于存储二进制数据，M表示位数，范围为1-64.

插入方法:

• INSERT INTO my_bit VALUES (b'101')

• INSERT INTO my_bit VALUES (5)

这里二进制数b'101'(在二进制字符串前加前缀b)和十进制数5等价。

查询方法：

客户端默认不显示数字而是十六进制，如果想要查看二进制结果，可以使用bin(M)手动转换；如果想要查看十进制结果，可以使用M+0手动转换

时间与日期类型

为了方便在数据库中存储日期，MySQL提供了表示日期和时间的数据类型。常用的有year、date、time、datatime、timestamp

• year：表示年份，格式为“YYYY”

• data：表示日期，格式为“YYYYMMDD”

• time：表示时间值，格式为“HHMMSS”

• datetime:表示日期和时间，格式为“YYYY-MM-DD HH：MM：SS”

• timestamp：表示时间戳，记录某条数据的创建时间、修改时间。

查看当前时间可以用select now();

timestamp

如果你把timestamp设置为DEFAULT CURRENT_TIMESTAMP，那么它就会记录下当前的日期与时间。

create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP

如果你把timestamp设置为DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，那么它就会记录下每次表结构更新（update）的时间（insert插入数据时不记录）。

update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

timestamp和datatime虽然显示结构一样，但是timestamp一般用于记录操作的发生时刻，比如用户登录、操作记录、访问时间；而datatime用于存储固定时间，比如说公告发布时间、入职时间

字符串类型

char和varchar类型

• char（M）

• varchar（M）

这是char和varchar类型的定义方式，M指的是字符串的最大长度。

对于char(4)而言，无论插入值的长度是多少，所占用的存储空间都是4字节；而varchar(4)占用的字节数是实际长度+1。

MySQL中的字节数和C++里的字节数不同，C++里面一个字符可能对应多个字节比如中文字符，但是MySQL中一个字符就相当于一个字节。

text类型

text类型用于保存非查询大文本数据，比如评论、文章等比较长的文本，text类型只能创建前缀索引（只能索引前 N 个字符），无法创建全列普通索引、唯一索引。

ENUM类型

enum类型就是枚举类型，定义方式为

enum ('值1','值2',...);

enum类型的数据只能从枚举列表中选取，而且只能选一个。

枚举列表最多可以有65535个值，每个值都有一个顺序编号，实际上保存在记录中的是顺序编号而不是列表中的值，因此不用担心过长的值占用空间。

SET类型

set类型用于保存字符串对象

set('值1','值2'...)

set类型和enum类型区别在于，set类型可以从列表中一个或多个来保存，多个值之间用“ , ”相隔。

create table my_set(hobby set('book','game','code')); insert into my_set values(''), ('book'), ('book,code');

set和enum类型的优势在于规范数据本身，限定只能插入规定的数据项，节省存储空间，查询数据比char、varchar更快。

binary和varbinary类型

binary和varbinary类似于char和varchar，但不同的是他们表示二进制数据。定义方式如下：

• binary(M)

• varbinary(M)

M指的是二进制数据最大字节长度，binary类型长度是固定的，如果数据不足最大长度，将在数据后面用“\0”补齐以达到指定长度，比如数据类型为binary（3），当插入a时，实际存储的数据为“a\0\0”。在查询binary类型时，查询条件字符串也需要加上"\0"填充。并且binary和varbinary都区分大小写。

BLOB类型

BLOB类型用于存储数据量很大的二进制数据，比如图片、pdf文档等等

BLOB类型和TEXT类型很相似，但是BLOB类型数据是根据二进制编码进行比较和排序，而TEXT类型数据是根据文本模式进行比较和排序

json类型

JSON是一种轻量级的数据交换格式，由JavaScript发展而来，本质上是一个字符串。MySQL中的JSON类型有两种：JSON数组和JSON对象。

#json数组 ["abc",10,null,true] #json对象 {“k1":"value","k2":10} ​

JSON数组中保存的数据可以是任意类型，JSON数组使用" [ "和" ] "实现，多个值之间用逗号分隔；JSON对象使用" { "和" } "实现，保存的数据时一组键值对。

与直接使用MySQL字符串相比，JSON数据类型具有自动验证格式、优化存储格式的优点。

注意事项

• char和varchar类型在插入数据时。若字符串末尾有空格，char类型会自动去掉空格保存，而varchar、text类型会保留空格。

• 在默认情况下创建的数据库和表使用的校对集对大小写不敏感，因此char、varchar、text、enum、set类型都不区分大小写，例如使用where查询’a'字符串，那么"a"和"A"都会被查询出来；而binary、varbinary、blob类型区分大小写，因为它们以二进制方式保存数据。

• 从执行效率来说，text和blob不如char和varchar，建议只有当需要保存大量文本时才使用text或blob类型。