(四) 列的三要素

摘要
我们将实体或实体间的关系抽象为一张表，用来表示所有个体形成的集合。为此，我们使用统一的特征及格式来定义“它们”，并暴露操作的接口以便后续持续性地记录它们。
在表中，使用列来体现这些“统一的特征和格式”。故，列的定义展开如： 列名 列的数据类型 [列的约束属性]

  • 数据表是由一个或多个数据列构成的。
  • 为了确保存储合乎规格的实体数据以及后续使用这些数据，我们在创建表时，必须给列提供名称标识和数据类型。
  • 至于列的约束属性，则是可选的。因为它是增设在数据类型之上的一个额外的筛子，可以令实体数据的合规性得到进一步的保障——当符合了所设置的约束的要求，方能通过这个筛子，登记在册。

注意
本文在列的数据类型方面将不会做过多地介绍。可通过右侧链接直接查阅和了解语法的有关内容：菜鸟教程

列名

1. 知识点讲解

  • 列名，配置的字符必须为合法字符

    字符主要为以下三类：

    • 非法字符：NUL字节（\0）和Unicode补充字符（范围在U+10000及以上）

    • 擦边字符：SQL保留字、空格

    • 合法字符

      • 大小写字母a-zA-Z、数字0-9
      • 美元符号$、下划线_
      • 范围在U+0080到U+FFFF之间的Unicode扩展字符

    说明：擦边字符是我自己造出来的一个名词。意在表明它虽不属于合法字符，但可以通过某种形式转换为合法字符的一种状态——比如，在列名完全赋值为保留字或含有空格时，需要使用``将列名包裹起来，暂时消除它的特殊含义，比如`id`，`launch date`。

  • 引用列时，所使用的列名必须已经存在

2. 问题总结

  • 问题1：列名的首个字符是否可以是数字？
    答案：MySQL和其他数据库不同，允许列名由“数字+其他合法字符”构成（即首个字符为数字、但并非所有字符都为数字，因为这难以与数字区分开）

  • 问题2：SQL保留字是否能够作为列名？列名是否可以包含空格？
    答案：笔者将特殊的SQL保留字和空格自称为“擦边字符”，就像擦边球一样。原因是在单独使用保留字或包含空格时，额外携带一对反引号（``），擦边字符就能转换为合法字符。

  • 问题3：引用其他对象时，名称是否需要注意大小写的问题？
    答案：数据库、表、视图、表别名、触发器，区分大小写；SQL关键字、函数、存储过程、事件、列、索引，不区分大小写。（其实，数据库、数据表、视图的大小写与由服务器的部署环境有关，Linux系统区分大小写，Windows、Mac系统不区分）

  • 问题4：列的数量是否有限制？
    答案：列名的数量就是列的数量。MySQL定义行的长度不得超过65535（64k），正是该值限制了每一行能拥有的多少个列。

列的数据类型

1. 知识点讲解

在MySQL中，主要的数据类型有：数值类型、字符串类型、日期和时间类型、枚举和集合类型。

数值类型

数值类型包含了整数和小数。

  • 区分整数
    根据有无正负号，我们将整数分为有符号整数、无符号整数（非负数）。

    • 有符号
      用法：整数类型 SIGNED
      有符号是指整数的最高位是符号位，用于表示数值的正负。也就是说，有符号整数类型可以表示真的针数（正数、零、负数）。

    • 无符号
      用法：整数类型 UNSIGNED
      反过来讲，无符号整数类型表示的就是非负数了。而且由于少了符号位作为最高位，整体范围也因此翻了一倍。例如：smallint的值范围是-128~127，但smallint unsigned的值范围是0~255。其他的以此类推。

  • 区分小数
    根据有无小数位、小数位数固定不固定，我们将小数细分为定点数（小数为固定）、浮点数（小数位不固定）。

    • 定点数
      用法：DECIMAL(p, s)
      示例：数字 3.1415926 的精度(pricision)为 8，刻度(scale)为 7
      DECIMAL(p, s)是一种精确的固定点数类型，p表示总的有效数字位数，s表示小数部位数。在存储和计算时，MySQL会按照定义来保持所需的小数点位数。

    • 浮点数
      用法：FLOAT和DOUBLE
      它们之间的区别在于存储范围和精度：

      • FLOAT是单精度，占4个字节，用于存储较小范围的浮点数，大约存储6-7位有效数字。
      • DOUBLE是双精度，占8个字节，用于存储更大范围的浮点数，大约存储15-16位有效数字。

  • 区分精确值与近似值

    • 整数、定点数是精确值，浮点数是近似值
      在底层，整数、定点数一般使用二进制表示，而浮点数使用二进制的科学计数法表示，故浮点数虽然可以用来表示很大的数和很小的数，却可能存在损失精度的问题。

    • 一旦运算涉及小数，计值结果将可能是近似的
      实际上知道了这个可能发生的事实，我们就能预判产生误差问题的源来。

      • 浮点数的精度有限，不能精确表示所有的小数。
      • 定点数虽然有确定的精度范围，但我们也必须承认以下的情况可能会产生误差：
        • 大数之间的运算，可能会超出decimal值范围
        • 除法运算，可能会产生无限循环小数或无限不循环小数而遭到自动舍入
        • 类型转换运算，转换为其他低精度类型时，可能会为了兼容该数据类型而发生截断

字符串类型

字符串类型按照长短，分为字符串、文本。按照存储性质和用途还可以进一步分为普通字符串、普通文本、二进制字符串、二进制文本。

  • 区分定长与变长

    • 定长
      用法：CHAR(N)
      定长，指的是固定不变的长度，为了确保长度统一，系统会使用空白字符进行二次填充；

    • 变长
      用法：VARCHAR(N)
      变长，指的是长度在可伸缩的范围内，适应数据长度的变化。

    • 示例讲解
      我们通过“c1 CHAR(10)”和“c2 VARCHAR(10)"来讲解它们之间的区别。
      首先我们知道括号里的10，指的是字符的数量。
      其次我们发现无论输入多少个字符，它们都遵从CHAR(10)=10, VARCHAR(10)<=10这个规律：

      • 当字符数超过10个时，有效字符为前10个字符（因为多余的字符在存储前就被截断了）；
      • 当字符数量少于10个时，c1保存的除了原来的字符，还会被系统以空白字符填满，c2则还是原来的字符。
      CREATE TABLE test(c1 CHAR(10), c2 VARCHAR(10));
INSERT INTO test(c1, c2) VALUES('hello world', 'hello world');
INSERT INTO test(c1, c2) VALUES('hello', 'hello');
SELECT c1, length(c1) c1长度, c2, length(c2) c2长度 FROM test;

日期和时间类型

在MySQL中，日期时间类型是特殊的数值类型，可参与计算。

  • 常见的日期和时间类型

    • DATE：用于存储日期，格式为'YYYY-MM-DD'，例如'2023-08-21'。
    • TIME：用于存储时间，格式为'HH:MM:SS'，例如'14:30:00'。
    • DATETIME：用于存储日期和时间，格式为'YYYY-MM-DD HH:MM:SS'，例如'2024-08-21 14:30:00'。
    • TIMESTAMP：类似于DATETIME，但存储范围更广且会自动更新，通常用于记录数据修改的时间戳

  • 示例讲解（参照枚举和集合类型-示例讲解）
    在MySQL中，日期的表示方式默认为'YYYY-MM-DD'。为了表示2023年8月21日这天，我们必须写作'2023-08-21'，而其他诸如‘08-21-2023’、‘21-08-2023’之类的表达都是不正确的。

枚举和集合类型

用于限制字段的取值范围。要使用枚举类型和集合类型，可以或，并且指定允许的值列表。

  • 枚举类型（ENUM）
    用法：ENUM(值列表)
    在创建表时指定字段的类型为ENUM，并预定义一个值列表，将列的可取值限制为有限个元素。
    在存储数据时必须取预定义值列表中的一个值，或者使用NULL值表示该值是未知的或不适用的。

  • 集合类型（SET）
    用法：SET(值列表)
    在创建表时指定字段的类型为SET，并预定义一个值列表，将列的可取值限制为有限个元素。
    在存储数据时可以取预定义值列表中的一个或多个值，或者使用NULL值表示该值是未知的或不适用的。

  • 示例讲解
    products表使用了枚举类型来定义size字段，只能取'XS'、'S'、'M'、'L'、'XL'中的一个值；
    products表使用了集合类型来定义types字段，可以取'Spring'、'Summer'、'Autumn'、'Winter'中的任意组合。

    CREATE TABLE products(
	`id` INT AUTO_INCREMENT PRIMARY KEY,
	price Decimal(5, 2) NOT NULL,
	size ENUM('XS', 'S', 'M', 'L', 'XL'),
	types SET('Spring', 'Summer', 'Autumn', 'Winter'),
	`launch date` DATE
);
INSERT INTO product(id, price, size, types, `launch date`) VALUES(1, 99.98, 'XS', 'Summer', '2023-5-1'), (2, 79, 'S', 'Spring,Autumn', '2023-4-21');
INSERT INTO product(id,  price, size, types, `launch date`) VALUES(NULL, 999.98, 'S', 'Winter', '2023-9-21');`

2. 问题总结

  • 问题1：选用定点数还是浮点数？
    答案：在选择小数类型时，我们需要根据数据的特点和使用场景来选择合适的类型，以确保数据的存储和计算准确性。

    • 选用定点数
      定点数在定义时需要指定精度，而小数点后的位数会影响存入的最大值和最小值。该类型常用于存储对精确度要求较高的货币金额，比如原材料的批量价格、产品的单价。

    • 选用浮点数
      浮点数在定义时不必限定小数点后的位数，它的值范围很大，但精度有限且是近似值，有可能存在精度损失。

  • 问题2：选用定长还是变长？
    答案：我们可以将这个问题简化为“什么时候选用CHAR(N)，什么时候选用VARCHAR(N)?”。
    这里我们需要了解一个事实：使用VARCHAR类型的字段，通常会额外占用1个或2个字节的存储空间，用于记录该字段存储的实际字符长度（当N小于等于255使用1个字节，大于255则使用2个字节）。但这部分空间是数据库自动添加的，与括号里的N无关。

    • 选用CHAR(N)
      当各个值的长度差别不大或者都是N时，CHAR(N)更能节省存储空间；
      当某个表使用MyISAM存储引擎时，选用CHAR(N)更合适，因为这个存储引擎对固定长度行的处理效率更高。

    • 选用VARCHAR(N)
      当数据长短不一或难以统一为固定长度时，选用VARCHAR(N)更合适。

列的约束属性

在配置表时，列的约束属性是可选的。
在向表文件存储数据时，由于我们是以行为单位进行存储的，因而只有满足所有列的约束的行数据，才会予以保存。

1. 知识点讲解

NULL/NOT NULL

用法：NULL 或 NOT NULL
NULL值是一个值，NULL约束是一个用于附加在列上的属性，表示允许列不使用值或者使用空值作为数据进行填充。NULL约束表示允许用NULL值填充；NOT NULL约束表示禁用NULL值，也就是说必须使用NULL以外的值对列进行填充。

DEFAULT

用法：DEFAULT 值
DEFALUT约束是一个“紧急装置”，一旦该列没有被赋值，就会确保其为预定义的值。
但是，在这里我们仍然需要区分“没有向该列赋值”与“向该列赋NULL值”所获得的结果。

  • 没有向该列赋值
    在插入数据时如果不指定该字段，不管该列是NULL约束还是NOT NULL约束，列值将填充为DEFAULT预定义的值。

  • 向该列赋NULL值
    如果该列是NULL约束，列值将填充为NULL值；如果是NOT NULL约束，则无法保存。

比如下面的sex列，当不赋值或赋NULL值时，就会才用默认值'未知'。

CREATE TABLE user(
	`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	nickname VARCHAR(50) NOT NULL,
	real_name VARCHAR(50),
	sex ENUM('M', 'F', 'Unknown') DEFAULT 'Unknown'
);

INSERT INTO user(`id`, nickname, real_name, sex) VALUES(NULL, 'Smile', 'Bob Smith', 'M'), (NULL, 'To be or Not to be', 'Sue Jone', 'F'), (NULL, 'Emily', 'Chen Qing', NULL);

ALTER TABLE user CHANGE sex sex  ENUM('M', 'F', 'Unknown') DEFAULT 'Unknown' NOT NULL;
UPDATE user SET sex='Unknown' WHERE sex IS NULL;
ALTER TABLE user CHANGE sex sex  ENUM('M', 'F', 'Unknown') DEFAULT 'Unknown' NOT NULL;
INSERT INTO user(nickname, sex) VALUES('GG', NULL);

在创建数据库表时，通常是先设置默认值（DEFAULT），然后再设置是否允许为空（NOT NULL）。这种基本约定是因为默认值是字段在插入数据时的一个默认取值，而是否允许为空则是对该字段的非空约束。
实际上并不严格要求DEFAULT在前NOT NULL在后，但这种顺序更符合常规的逻辑和阅读习惯。

COMMENT

用法：COMMENT '描述文本'
在数据库表的创建语句中，可以使用COMMENT关键字为表或列添加注释文本。
注释文本用于提供关于表或列的额外描述、解释或备注信息，便于开发人员或数据库管理员理解表结构和数据含义。
因此使用DESC xxx表;无法查看到它，必须使用SHOW CREATE TABLE xxx表;或者SHOW FULL FIELDS FROM xxx表;进行查看。

CREATE TABLE products(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT 'id',
	price INT UNSIGNED NOT NULL COMMENT '价格',
	size ENUM('XS', 'S', 'M', 'L', 'XL') COMMENT '尺码',
	types SET('Spring', 'Summer', 'Autumn', 'Winter') COMMENT '类型',
	`launch date` DATE COMMENT '上市日期'
) COMMENT '商品表';

DESC products;

SHOW CREATE TABLE products;
SHOW FULL FIELDS FROM products;

PRIMARY KEY

用法：PRIMARY KEY 键名

  • 一个表只有一个主键，主键值唯一且不能为空值
    主键指的是一个表里面最主要的键。“最”字就说明了一个表的主键有且只有一个。换句话说，主键就是行数据的“身份证号”。
    我们根据身份证号的性质反过来推导主键的特点，可以确定一旦一个表有了主键，那么作为主键的列的数据值一定是唯一且不能为空的（空值无法标识行，PRIMARY KEY = UNIQUE KEY + NOT NULL，注意与第5小点区分开 ）。

  • 一个主键可以由一个或多个列构成
    主键就像表里的主角，可以由一个列扮演，也可以多个列充当。“多个列扮演主键”称为“组合主键”。当一个列不足以作为一个实体的标识时，就需要用到它。

UNIQUE KEY

用法：UNIQUE KEY 键名
既然主键可以约束行数据的列值唯一，为什么还需要唯一键？一个表中能有一个主键！当需要确保列值不重复时，如果主键已被使用、或者不适合使用（实体无法通过这几个列就被区分）的情况下，就需要唯一键这样的护花使者来互为补充了。

  • 一个表可以有多个唯一键，唯一键值唯一但可以为空值
    • 唯一键约束列的数据值互不重复。先行存入表中的列值就是参照标准。
    • 设置了唯一键约束的列，是可以同时包含多个NULL值的。因为NULL不等于NULL。

AUTO_INCREMENT

用法：AUTO_INCREMENT PRIMARY KEY 或 AUTO_INCREMENT UNIQUE KEY
AUTO_INCREMENT约束用于自动生成序列编号，为了保证生成的序列编号唯一，我们需要与主键或唯一键约束一起搭配使用。

  • 一个表中，只能有一个使用AUTO_INCREMENT属性的列。
  • 只有具有某种形式的唯一性索引的列，才能使用了AUTO_INCREMENT属性。
posted @ 2025-07-04 21:13  枫林羡晚  阅读(21)  评论(0)    收藏  举报