六.数据类型与约束
一.数据类型
MySQL中的数据类型包括:
数据类型 | 关键字 |
---|---|
整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT |
浮点数类型 | FLOAT、DOUBLE |
定点数类型 | DECIMAL |
位类型 | BIT |
日期时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP |
文本字符串类型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT |
枚举类型 | ENUM |
集合类型 | SET |
二进制字符串类型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB |
JSON类型 | JSON对象、JSON数组 |
空间数据类型(单值) | GEOMETRY、POINT、LINESTRING、POLYGON |
空间数据类型(集合) | MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION |
而其中常见的数据类型属性包括:
MySQL关键字 | 含义 |
---|---|
NULL | 数据量可包含NULL值 |
NOT NULL | 数据列不允许包含NULL值 |
DEFAULT | 默认值、缺省值 |
PRIMARY KEY | 主键 |
AUTO_INCREMENT | 自动递增,适用于整数类型 |
UNSIGNED | 无符号 |
CHARACTER SET … | 指定一个字符集 |
1.整数类型
整数类型一共有5中,包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
整数类型 | 字节 | 有符号数取值范围 | 无符号数取值范围 |
---|---|---|---|
TINYINT | 1 | ±128 | 255 |
SMALLINT | 2 | ±32768 | 65535 |
MEDIUMINT | 3 | ±8388608 | 16777215 |
INT | 4 | ±2147483648 | 4294967295 |
BIGINT | 8 | ±9223372036854775808 | 18446744073709551615 |
已成历史!
整数类型在MySQL5.7中的可选属性包括:M、ZEROFILL
其中,M
表示显示宽度,当不进行ZEROFILL
操作时对数据影响不大。
一旦配合ZEROFILL
使用,则会将不足M
的位数补0
CREATE TABLE test (test_field INT(5) ZEROFILL);
INSERT INTO test VALUES (123);
-- output: 00123
从MySQL8.0后,不再支持显示宽度。
2.浮点类型和定点数类型
浮点类型包括:FLOAT、DOUBLE、REAL
数据类型 | 字节 | 有符号数取值范围 | 无符号数取值范围 |
---|---|---|---|
FLOAT | 4 | ±3.4E38 | 3.4E38 |
DOUBLE | 8 | ±1.3E308 | 1.3E308 |
其中,MySQL还存在一种REAL
的数据类型,其默认值为DOUBLE
。
也可以对其默认值进行调整:
SET sql_mode = "REAL_AS_FLOAT";
且MySQL支持自定义浮点,如:FLOAT(M,D)、DOUBLE(M,D)
,此处M
称为精度、D
称为标度。其中,M
= 整数位 + 小数位,D
= 小数位。
若超出了标度,则会进行四舍五入,如:
CREATE TABLE test (test_field DOUBLE(5,2));
INSERT INTO test VALUES (123.456);
-- output: 123.46
但注意,数据的长度不能超过精度值:
CREATE TABLE test (test_field DOUBLE(5,2));
INSERT INTO test VALUES (1234.567);
-- error: Out of Range Value.
定点数DECIMAL
:
数据类型 | 字节 | 取值范围 |
---|---|---|
DECIMAL(M,D) | M+2 | ±1.3E308 |
M
和D
的定义与浮点数相同。DECIMAL
在定义时确定其数据范围,超过标度长度的内容同样会进行四舍五入。
与浮点数不同的是,定点数极大的改善了精度问题,适用于对精度要求极高的场景。
实际开发中,DECIMAL
的应用远大于浮点数的应用。
3.位类型
数据类型 | 字节 | 长度 | 数据范围 |
---|---|---|---|
BIT(M) | (M+7)/8 | M | 1<=M<=64 |
默认的M
为1,只能存储1位二进制。
CREATE TABLE test (test_field BIT(2));
INSERT INTO test VALUES (2);
-- output:10 (实际默认是用16进制0x来表示数值的)
将其数值表示为二进制可以通过BIN()
函数,表示为十进制则可以使用隐式转换,即output + 0
(此处0即十进制的0,会产生隐式转换)。
4.日期与时间类型
数据类型 | 字节 | 日期格式 | 最小值 | 最大值 |
---|---|---|---|---|
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-03 23:59:59 |
TIMESTAMP | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07 UTC |
两位数来表示年的换算:
以2位字符串格式表示YEAR类型,最小值为00,最大值为99
- 当取值为01到69时,表示2001到2069
- 当取值为70到99时,表示1970到1999
- 当取值整数的0或00时,表示0000年。
- 当取值是日期/字符串的‘0’时,表示2000年。
DATE
类型可以使用CURRENT_TIME()
或NOW()
函数来截取当前时间来获取。
DATETIME
和TIMESTAMP
除了数据范围的区别外,TIMESTAMP
还自带时区调整。
实际开发中使用更多的是TIMEDATE
类型。
5.文本字符串类型
数据类型 | 字节 | 长度(字符) | 长度范围 |
---|---|---|---|
CHAR(M) | M | M | 0 ~ 255 |
VARCHAR(M) | M+1 | M | 0 ~ 65535 |
TINYTEXT | L+2 | L | 0 ~ 255 |
TEXT | L+2 | L | 0 ~ 65535 |
MEDIUMTEXT | L+3 | L | 0 ~ 16777215 |
LONGTEXT | L+4 | L | 0 ~ 4294967295 |
ENUM | 1或2 | L | 1 ~ 65535 |
SET | 1,2,3,4或8 | L | 0 ~ 64 |
上述为常用的文本字符串类型。
VARCHAR
相较于CHAR
而言,其长度是可变的,所以增加了一个字节来存储字符串长度。
CHAR
和VARCHAR
的默认长度为1,且二者在读取过程中是不记录空格的。
TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
的长度可变,可读取空格,且实际存储长度不定,因此在MySQL
中不允许使用TEXT
类型的字段做主键。
ENUM
要求该字段内容必须为定义时要求的内容或NULL
:
CREATE TABLE test(
season ENUM('spring','summer','autumn','winter')
);
INSERT INTO test VALUES('spring'),('summer');
INSERT INTO test VALUES('1'),(2);
且可以通过索引来进行枚举内容的调用。
与此相比,SET
可以存储64个元素,但读取时可以取64个元素组成的集合中的任意子集。
CREATE TABLE test(
test SET('A','B','C')
);
INSERT INTO test VALUES('A,B,C'),('A,B'),('A,C'),('B,C');
6.二进制字符串类型
当需要将图片、音频或视频等文件存入数据库时,可以将其转化为二进制流(octet-stream
),并将对应的二进制字符串存入数据库中。
数据类型 | 字节 | 数据长度(字节) | 特点 |
---|---|---|---|
BINARY(M) | M | 0 ~ 255 | 固定长度 |
VARBINARY(M) | M+1 | 0 ~ 65535 | 可变长度 |
TINYBLOB | L+1 | 0 ~ 255 | |
BLOB | L+2 | 0 ~ 65535(64KB) | |
MEDIUMBLOB | L+3 | 0 ~ 16777215(16MB) | |
LONGBLOB | L+4 | 0 ~ 4294967295(4GB) |
BINARY、VARCHAR
的M
缺省值为1。
实际开发中,并不会将文件直接存入数据库,因此二进制字符串类型应用场景极少。
7.JSON类型
数据库同样可以存储数据交互类型,如JSON
类型:
CREATE TABLE test(
js json
);
INSERT INTO test VALUES(
'{"name":"Outer","age":"18"}');
json
类型可以存储JSON
信息,且支持通过键来检索对应值:
SELECT js -> '$.name' AS name,js -> '$.age' AS age FROM test;
二.约束
1.基础操作
通过增加约束,来限制数据的格式,增强数据的规范性。
从不同角度来分类约束,可以分为 单列约束和多列约束、列级约束和表级约束
常见的约束包括:NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT
关键字 | 作用 |
---|---|
NOT NULL | 限制该字段必须为非NULL |
UNIQUE | 限制该字段是唯一的,不能存在重复数据 |
PRIMARY KEY | 唯一约束 + 非空约束,即不能为NULL值且必须唯一,每个表只能有一个主键 |
FOREIGN KEY | 与主表的主键产生关联,限制从表的输入内容。只有当主表的主键有对应数据时才能在从表中输入该数据。 |
CHECK | 对数据增加特定限制(MySQL5.7不支持) |
DEFAULT | 为字段设置缺省值 |
如何在创建表时对某一字段增加约束:
CREATE TABLE test(
id INT NOT NULL,
name VARCHAR(15) NOT NULL,
salary DECIMAL(5,2)
);
也可以通过ALTER
指令在创建后添加约束:
ALTER TABLE test MODIFY id INT NOT NULL;
查询约束信息:
SELECT * FROM information_schema.table_constraints WHERE table_name = 'test';
我们也可以自定义约束的名字,便于查询时与其他信息进行区分。
CONSTRAINT constraint_of_id UNIQUE(id);
ALTER TABLE test ADD CONSTRAINT constraint_of_id UNIQUE(id);
也可以在创建表格的时候添加组合约束,组合约束只有在添加约束的字段均相等时才会报错。
CREATE TABLE test(
`name` VARCHAR(15),
`password` VARCHAR(15),
CONSTRAINT unique_name_and_psw UNIQUE(`name`,`password`)
);
也可以写作UNIQUE KEY(
name,
password)
删除某个字段上的约束:
ALTER TABLE test DROP INDEX constraint_of_id;
2.主键约束
如何在创建表时增加主键约束?
列级约束:
CREATE TABLE test(
id INT PRIMARY KEY,
name VARCHAR(15)
);
表级约束:
CREATE TABLE test(
id INT,
name VARCHAR(15),
PRIMARY KEY(id)
);
无论是否自定义约束名称,主键约束的名称永远为PRIMARY
。
主键约束也可以定义为复合约束。
同理,增添主键约束也可以用ALTER
:
ALTER TABLE test ADD PRIMARY KEY (id);
通过AUTO_INCREMENT
,可以实现数据从1开始的自增
CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(15)
)
其中,若在已定义AUTO_INCREMENT
的字段上传入数据,若为0或NULL
,则无影响,否则会覆盖AUTO_INCREMENT
的值。
删除AUTO_INCREMENT
的方法:
ALTER TABLE test MODIFY id INT;
3.外键约束
外键即不同的两个关联表之间,一个表的非主键字段是另一个表的主键,则称前一个表为从表,后一个表为主表。这段字段也被称为从表的外键。
外键的默认名不是列名,而是student_idfk_1
,所以建议为外键自定义名称。
CREATE TABLE main_table(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);
CREATE TABLE fellow_table(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(15),
department_id INT,
CONSTRAINT fk_main_fellow_id FOREIGN KEY(department_id) REFERENCES main_table(dept_id)
);
当删除从表的外键时若存在主从冲突,存在以下不同的操作:
约束等级 | 操作 |
---|---|
Cascade | 对主表进行更改时,同步对从表的对应内容进行更改 |
Set NULL | 对主表进行更改时,将从表的对应内容设置为NULL |
No action | 若存在主从冲突则报错,不进行任何操作 |
Restrict | 同No action |
Set default | 对主表进行更改时,将从表的对应内容设置为缺省值 |
常见设置为:
ON UPDATE CASCADE ON DELETE SET NULL
即UPDATE
时进行CASCADE
操作,DELETE
时进行SET NULL
操作。
删除外键约束:
删除时需要知晓要删除的外键约束在索引中的名字,不能用列名来删除。
ALTER TABLE test DROP FOREIGN KEY fk_main_fellow_id;
4.CHECK和DEFAULT
CHECK
用于增加特定的数据限制
CREATE TABLE test(
id INT,
`data` INT CHECK(`data`>2000)
);
DEFAULT
用于给未赋值的字段增加缺省值
CREATE TABLE test(
id INT,
`data` INT DEFAULT 2000
);