数据库基础之MySQL学习 part four

Posted by OuterCyrex on June 28, 2024

六.数据类型与约束

一.数据类型

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

MD的定义与浮点数相同。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()函数来截取当前时间来获取。

DATETIMETIMESTAMP除了数据范围的区别外,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而言,其长度是可变的,所以增加了一个字节来存储字符串长度

CHARVARCHAR的默认长度为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、VARCHARM缺省值为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
);