MySQL学习（五）：数据类型与约束

MySQL学习（五）：数据类型与约束

1. 数据类型与属性

---

1.1 所有的数据类型

MySQL数据库中支持多种数据类型，它们决定了数据的存储方式和处理方式。

1.2 所有属性

除了数据类型本身，MySQL还提供了各种属性来进一步定义字段的行为和存储需求。

2. 数据类型详解

---

2.1 整型

整型数据用于存储没有小数部分的数字。

• int (n)：n 表示显示宽度，配合 zerofill 属性，当数据不足 n 位时，会自动用 0 填充。需要注意的是，在 5.7 版本中，单独使用 int (n) 没有意义，它会被自动视为 unsigned，使其只能存储正数。

• unsigned：指定整型字段只能存储正数。

2.2 浮点类型

浮点数用于存储有小数部分的数字。

• double(n,m)：n 表示总位数，m 表示小数位数。这是非标准语法，其他数据库语言可能不支持。

• 浮点数在存储和计算时可能会出现精度问题。

2.3 定点数类型

定点数类型用于需要精确到小数点后指定位数的数值。

• (M,D)：M 表示总位数，D 表示小数位数。默认值为 (5,0)。

• 定点数类型精度高，通常使用字符串在底层存储。

• 如果需要表示范围大，对精度要求不高，可以使用 double 类型。

2.4 位类型

位类型用于存储固定长度的位字段。

• bit(M)：M 表示位数。

2.5 日期与时间

日期和时间类型用于存储日期和时间数据。

• DATE：存储日期。
• TIME：存储时间。
• DATETIME：存储日期和时间。
• TIMESTAMP：存储日期和时间，并具有时区信息。

2.6 文本字符串

文本字符串类型用于存储字符数据。

• char(M)：固定长度字符串，如果实际长度小于 M，则用空格填充。
• varchar(M)：可变长度字符串，根据实际内容长度存储。
• TEXT：用于存储大文本数据。
• ENUM：枚举类型，只允许从一组预定义的值中选择。
• SET：集合类型，允许从一组预定义的值中选择多个值。

2.7 二进制字符串

二进制字符串类型用于存储二进制数据。

• BINARY(M)：固定长度二进制字符串。
• VARBINARY(M)：可变长度二进制字符串。
• BLOB：用于存储大量二进制数据，如图片、音频、视频等。

2.8 JSON

JSON类型用于存储JSON格式的数据。

• JSON：用于存储JSON对象。
• JSON document：用于存储JSON文档。

create table `tableName`(
`名` JSON
);

insert into `tableName`(`名`)
values ('{key:value,key:value}');

select `名` -> '$.key' as 别名
from `tableName`

3. 约束基础

---

3.1 约束结构

在MySQL数据库中，约束是维护数据完整性和一致性的重要工具。它们规定了数据在表中应该如何存储和操作，防止不合法的数据进入数据库。

3.2 添加约束的位置

约束可以添加到表中的阶段：

• 创建表时：这是最常见的方式，在定义表结构的同时即可指定约束条件。

  CREATE TABLE `employee` (
      `id` INT NOT NULL,
      `name` VARCHAR(100) NOT NULL,
      PRIMARY KEY (`id`)
  );
  

• 修改表时：使用 ALTER TABLE 语句可以在表创建后添加约束。

  ALTER TABLE `employee`
  ADD CONSTRAINT `email_unique` UNIQUE (`email`);
  

3.3 查看约束信息

要查看表中的所有约束及其定义，可以使用 INFORMATION_SCHEMA 数据库中的 table_constraints 表。

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name = 'employee';

3.4 约束分类

MySQL中的约束可以分为：

• 列级约束：直接作用于单个列，例如非空约束、唯一约束、主键约束等。
• 表级约束：作用于整个表，如外键约束和检查约束（在MySQL 5.7及以上版本支持）。
• 单列约束：仅对单个列生效，如非空约束、唯一约束等。
• 多列约束：对多个列组合生效，如复合唯一约束、复合外键约束等。

3.5 约束功能一览

MySQL中常用的约束类型及其功能：

约束类型	功能描述
NOT NULL	确保列中的数据不能为空。
UNIQUE	确保列中的数据是唯一的，但可以有多个 NULL 值。
PRIMARY KEY	确保列中的数据是唯一的，并且非空，同时自动创建唯一索引。一个表只能有一个主键。
FOREIGN KEY	用于建立两个表之间的引用关系，确保数据的引用完整性。
CHECK	确保列中的数据满足特定的条件。MySQL 5.7版本及以下不支持该约束。
DEFAULT	为列指定默认值，如果插入数据时没有指定该列的值，则自动使用默认值。

4. 约束详解

---

4.1 非空约束

非空约束 (NOT NULL) 确保指定的列在插入数据时不能为空。

CREATE TABLE `employee` (
    `id` INT NOT NULL,
    `name` VARCHAR(100) NOT NULL
);

尝试插入一个包含空值的非空列将会导致错误。

4.2 唯一约束

唯一约束 (UNIQUE) 确保列中的数据是唯一的，但允许有多个 NULL 值。

CREATE TABLE `employee` (
    `email` VARCHAR(255) UNIQUE
);

4.3 主键约束

主键约束 (PRIMARY KEY) 结合了非空约束和唯一约束的功能，确保数据唯一且非空。

CREATE TABLE `employee` (
    `id` INT PRIMARY KEY,
    `name` VARCHAR(100)
);

一个表只能有一个主键，且主键列的值必须是唯一的。

4.4 自增约束

自增约束通常与主键约束结合使用，自动为每条记录生成唯一的标识符。

CREATE TABLE `employee` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(100)
);

当向表中插入新记录时，如果没有指定主键值，MySQL会自动为该字段生成一个唯一的值。

4.5 外键约束

外键约束 (FOREIGN KEY) 用于建立两个表之间的引用关系，确保数据的一致性。

CREATE TABLE `department` (
    `id` INT PRIMARY KEY,
    `name` VARCHAR(100)
);

CREATE TABLE `employee` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(100),
    `department_id` INT,
    CONSTRAINT `fk_department` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
);

在这个例子中，employee 表的 department_id 字段是 department 表 id 字段的外键。

4.6 检查约束

在MySQL数据库中，检查约束（CHECK）是一种用于确保列中的值满足特定条件的机制。它允许数据库管理员定义复杂的数据验证规则，从而保证数据的准确性和合理性。

MySQL 5.7 之前的限制

值得注意的是，MySQL 5.7版本之前的版本不支持检查约束。因此，在创建表时，需要使用其他方法来确保数据的合理性，例如：

CREATE TABLE `tableName` (
    `id` INT,
    `last_name` VARCHAR(15),
    `salary` DECIMAL(10,2),
    CONSTRAINT `salary_check` CHECK (`salary` > 2000)
);

在上述示例中，尝试插入一个薪资小于或等于2000的记录将会失败，因为违反了检查约束。

MySQL 5.7 及以上版本

从MySQL 5.7版本开始，检查约束被引入，允许数据库管理员以更简洁的方式定义数据验证规则。

CREATE TABLE `tableName` (
    `id` INT,
    `last_name` VARCHAR(15),
    `salary` DECIMAL(10,2) CHECK (`salary` > 2000)
);

在此示例中，CHECK 约束确保了 salary 列中的值必须大于2000。每次插入或更新数据时，MySQL都会自动执行这个检查，确保数据的合理性。

注意事项

• 检查约束不能直接在 ALTER TABLE 语句中添加，只能在创建表时使用。
• 检查约束不能与 NOT NULL 约束组合使用。
• 检查约束可能对性能产生一定影响，因为它需要对每一行数据进行检查。

4.7 默认值约束

默认值约束（DEFAULT）允许您为列指定一个默认值，当插入新记录时，如果没有为该列指定值，系统将自动使用默认值填充。

CREATE TABLE `tableName` (
    `id` INT AUTO_INCREMENT,
    `last_name` VARCHAR(15),
    `salary` DECIMAL(10,2) DEFAULT 2000
);

在上述示例中，如果尝试插入一个只包含 id 的记录，salary 列将自动被设置为默认值 2000。

修改默认值

与 NOT NULL 约束类似，修改默认值的方法也与修改列定义相同：

ALTER TABLE `tableName`
MODIFY COLUMN `salary` DECIMAL(10,2) DEFAULT 3000;

通过这种方式，可以轻松更新列的默认值，而无需重新创建表。

注意事项

• 默认值可以是任何有效的SQL表达式，包括常量、函数调用或计算表达式。
• 默认值对于自动填充数据非常有用，尤其是在创建新记录时。
• 默认值不会影响现有记录，除非明确修改这些记录的值。