MySQL—数据类型与约束

数据类型

数字类型

整数类型

整数类型可分为五种：TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16 777 215	-8 388 608~8 388 607
INT	4	0~4 294 967 295	-2 147 483 648~2 147 483 647
BIGINT	8	0~18 446 744 073 709 551 615	-9 223 372 036 854 775 808~9 223 372 036 854 775 807

若使用无符号数据类型，需要在数据类型的右边加上UNSIGNED关键字修饰。
当数值超过显示宽度的时候，不填充0，当数值未达到显示宽度时，在左边用0填充。

浮点数类型

数据类型	字节数	负数的取值范围	非负数的取值范围
FLOAT	4	-3. 402 823 466E+38~-1. 175 494 351E-38	0和1.175 494 351E-38~3.402 823 466E+38
DOUBLE	8	-1.797 693 134 862 315 7E+308~-2.225 073 858 507 201 4E-308	0和2.225 073 858 507 201 4E-308~1.797 693 134 862 315 7E+308

当浮点数类型使用UNSIGNED修饰时，取值范围将不包括负数。

定点数类型

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

BIT类型

BIT（位）类型用于存储二进制数据，语法为BIT（M），M表示位数，范围为1~64。

BIT类型字段在数字插入时转换为二进制保存，但在利用SELECT查询时，会自动转换为对应的字符显示。

时间和日期类型

数据类型	取值范围	日期格式	零值
YEAR	1901~2155	YYYY	0000
DATE	1000-01-01~9999-12-31	YYYY-MM-DD	0000-00-00
TIME	-838:59:59~838:59:59	HH:MM:SS	00:00:00
DATETIME	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00

YEAR类型

YEAR用于表示年份

CREATE TABLE my_year(y year);#设置y字段的数据类型为y
INSERT INTO my_year VALUES(2020);#插入年份数据2020

DATE类型

DATE用于表示日期值，不包括时间部分。

CREATE TABLE my_date (d DATE);#设置d字段的数据类型为DATE
INSERT INTO my_date VALUES('2020-01-21');#插入日期数据
INSERT INTO my_date VALUES(CURRENT_DATE);#插入当前系统数据
INSERT INTO my_date VALUES(NOW());#插入当前系统日期

TIME 类型

TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS.

DATETIAME类型

DATETIME类型用于表示时间和日期，它的显示时间为‘YYYY-MM-DD HH:MM:SS’。

TIMESTAMP类型

TIMESTAMP（时间戳）类型用于表示日期和时间，它的显示时间与DATETIME相同，但取值范围比DATETIME小。

注意：

NOT NULL表示非空约束，该字段不允许保存空值‘
DEFAULT表示默认约束，当字段无任何输入时，自动设置某个值作为默认值。
ON UPDATE用于当一条记录中的其他字段被UPDATE语句修改时，自动更改该字段为某个值。

字符串类型

数据类型	类型说明
CHAR	固定长度字符串
VARCHAR	可变长度字符串
TEXT	大文本数据
ENUM	枚举类型
SET	字符串对象
BINARY	固定长度的二进制数据
VARBINARY	可边长度的二进制数据
BLOB	二进制大对象

CHAR和VARCHAR类型

CHAR和VARCHAR类型都用来保存字符串类型

CHAR(M);
VARCHAR(M);

TEXT类型

TEXT用于存储大文本数据

数据类型	存储范围	数据类型	存储范围
TINYTEXT	0~2^8-1字节	MEDIUMTEXT	0~2^24-1字节
TEXT	0~2^16-1字节	LONGTEXT	0~2^32-1字节

ENUM类型

ENUM类型又称为枚举类型。

ENUM('值1','值2',…)

SET类型

SET类型用于保存字符串对象。

SET('值1','值2','值n');

BINARY和VARBINARY类型

他们表示定义二进制数据。

BINARY(M);
VARBINARY(M);

BLOB类型

BLOB类型用于保存数据量很大的二进制数据，如图片，PDF文档等。

数据类型	存储范围	数据类型	存储范围
TINYBLOB	0~2^8-1字节	MEDIUMBLOB	0~2^24-1字节
BLOB	0~2^16-1字节	LONGBLOB	2^24-1字节

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

表的约束

默认约束

字节名 数据类型 DEFAULT 默认值

默认约束用于为数据表中的字段指定默认值。但BLOB类型与TEXT类型不支持默认约束。

非空约束

字段名 数据类型 NOT NULL

非空字段指字段的值不能为NULL。

唯一约束

#列级约束
字段名 数据类型 UNIQUE;
#表级约束
UNIQUE (字段名 1，字段名 2…);

唯一约束用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。

列级约束定义在一个列上，只对该列起约束作用；
表级约束是独立于列的定义，可以应用在一个表的多个列上。

主键约束

#列级约束
字段名 数据类型 PRIMARY KEY;
#表级约束
PRIMARY KEY(字段名 1,字段名2…);

自动增长

字段名 数据类型 AUTO_INCREMENT

注意：

一个表中只能有一个自动增长字段，该字段类型是整数类型，且必须定义为键，如UNIQUE KEY,PRIMARY KEY。
若为自动增长字段插入NULL,0,DEFAULT或在插入时省略该字段，则该字段就会使用自动增长值；若插入的是一个具体值，则不会使用自动增长值。
自动增长值从1开始自增，每次加1.若插入的值大于自动增长的值，则下次插入的自动增长值会自动使用最大值加1，若插入的值小于自动增长值，则不会对自动增长值产生影响
使用DELETE删除记录时，自动增长值不会减小或填补空缺。

字符集与校对集

字符集与校对集概述

字符集

字符指计算机中保存的各种文字和符号。由于计算机采用二进制保存数据，用户输入的字符将会按照一定的规则转换成为二进制后保存，这个过程就是字符编码。将一系列字符的编码规则组合起来就形成了字符集。

常用字符集说明

字符集	单字符最大长度	支持的语言
latinl	1字节	西欧字符、希腊字符等
gbk	2字节	简体和繁体中文、日文、韩文等
utf8	3字节	世界上大部分国家的文字

校对集

MySQL中提供许多校对集，用于对不同字符集指定比较和排序规则。

字符集与校对集的设置

MySQL环境

SHOW VARIABLES LIKE 'character%'；#查看与字符及相关的变量

数据库

在创建数据库时设置字符集和校对集的语法:

[DEFAULT] CHARACTER SET [=] charset_name;
[DEFAULT] COLLATE [=] collation_name;

数据表

[DEFAULT] CHARACTER SET [=] charset_name;
[DEFAULT] COLLATE [=] collation_name;

字段

[CHARACTER SET charset_name] [COLLATE collation_name]

实现：