• 首页
  • css3教程
  • html5教程
  • vue手册
  • php手册
  • MySQL手册
  • redis手册
  • jQuery手册
  • 创建表和生成的列

    CREATE TABLE支持生成列的规范。根据列定义中包含的表达式计算生成的列的值。

    NDB存储引擎还支持生成的列。

    以下简单示例显示了一个表,该表存储sideasideb列中直角三角形的边的长度,并计算下斜边的长度sidec(其他边的平方和的平方根):

    CREATE TABLE triangle (
      sidea DOUBLE,
      sideb DOUBLE,
      sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
    );
    INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
    

    从表中选择将产生以下结果:

    mysql> SELECT * FROM triangle;
    +-------	+-------	+--------------------	+
    | sidea 	| sideb 	| sidec              	|
    +-------	+-------	+--------------------	+
    |     1 	|     1 	| 1.4142135623730951 	|
    |     3 	|     4 	|                  5 	|
    |     6 	|     8 	|                 10 	|
    +-------	+-------	+--------------------	+
    

    使用该triangle表的任何应用程序都可以访问斜边值,而无需指定计算它们的表达式。

    生成的列定义具有以下语法:

    col_name data_type [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
    

    AS(expr)表示已生成列,并定义了用于计算列值的表达式。AS可以在前面GENERATED ALWAYS使列的生成的性质更明确。稍后将讨论表达式中允许或禁止的构造。

    VIRTUALSTORED关键字表示列的值的存储方式,其具有用于使用列含义:

    • VIRTUAL:不存储列值,但在任何BEFORE触发器之后立即读取行时会对其进行求值。虚拟列不占用任何存储空间。

      InnoDB支持虚拟列上的二级索引。请参见“二级索引和生成的列”。

    • STORED:插入或更新行时,将评估并存储列值。存储的列确实需要存储空间,并且可以建立索引。

    默认值是VIRTUAL如果未指定任何关键字。

    它允许混合VIRTUALSTORED表中的列。

    可以赋予其他属性以指示该列是已索引还是可以为NULL,或提供注释。

    生成的列表达式必须遵守以下规则。如果表达式包含不允许的构造,则会发生错误。

    • 允许使用文字,确定性内置函数和运算符。如果给定表中的相同数据,则独立于所连接的用户,如果多次调用产生相同的结果,则该函数为确定性函数。的是不确定性和失败,这个定义函数的例子:CONNECTION_ID()CURRENT_USER()NOW()
    • 不允许使用存储的函数和用户定义的函数。
    • 不允许存储过程和函数参数。
    • 不允许使用变量(系统变量,用户定义的变量和存储的程序局部变量)。
    • 不允许子查询。
    • 生成的列定义可以引用其他生成的列,但只能引用表定义中较早出现的列。生成的列定义可以引用表中的任何基础(未生成)列,而不论其定义是更早还是更晚。
    • AUTO_INCREMENT属性不能在生成的列定义中使用。
    • 一个AUTO_INCREMENT列不能用作在生成的列定义的基柱。
    • 如果表达式求值导致截断或向函数提供错误的输入,则该CREATE TABLE语句将以错误终止并拒绝DDL操作。

    如果表达式求值的数据类型不同于声明的列类型,则根据通常的MySQL类型转换规则,对声明的类型进行隐式强制转换。请参见“表达式评估中的类型转换”。

    注意

    表达式评估使用评估时有效的SQL模式。如果表达式的任何部分取决于SQL模式,则表的不同用法可能会产生不同的结果,除非在所有用法中SQL模式都相同。

    对于CREATE TABLE ... LIKE,目标表保留从原始表生成的列信息。

    对于CREATE TABLE ... SELECT,目标表不会保留有关selected-from表中的列是否是生成的列的信息。SELECT语句的一部分不能为目标表中的生成列分配值。

    允许按生成的列进行分区。请参阅表分区。

    在存储生成列外键约束不能使用CASCADESET NULLSET DEFAULT作为ON UPDATE参照动作,也不能使用SET NULLSET DEFAULT作为ON DELETE参照动作。

    在存储生成列的基本列外键约束不能使用CASCADESET NULLSET DEFAULT作为ON UPDATEON DELETE引用操作。

    外键约束不能引用虚拟生成的列。

    触发器不能使用或用于引用生成的列。NEW.col_nameOLD.col_name

    对于INSERTREPLACEUPDATE,如果将生成的列显式插入,替换或显式更新,则唯一允许的值为DEFAULT

    视图中生成的列被认为是可更新的,因为可以对其进行分配。但是,如果显式更新了此类列,则唯一允许的值为DEFAULT

    生成的列有几种用例,例如:

    • 虚拟生成的列可以用作简化和统一查询的方法。可以将复杂条件定义为生成的列,并从表上的多个查询中引用该条件,以确保所有条件都使用完全相同的条件。
    • 存储的生成的列可用作复杂条件的物化缓存,这些条件需要快速计算。
    • 生成的列可以模拟功能索引:使用生成的列定义功能表达式并为其编制索引。这对于处理无法直接索引的类型的列(例如,JSON列)很有用。有关详细示例,请参见为生成的列建立索引以提供JSON列索引。

      对于存储的生成列,此方法的缺点是值存储两次。一次作为生成的列的值,一次作为索引。

    • 如果为生成的列建立索引,则优化器将识别与列定义匹配的查询表达式,并在查询执行期间适当地使用该列的索引,即使查询未按名称直接引用该列也是如此。有关详细信息,请参见“优化器对生成的列索引的使用”。

    例:

    假设一个表t1包含first_namelast_name列,并且应用程序经常使用这样的表达式来构造全名:

    SELECT CONCAT(first_name,' ',last_name) AS full_name FROM t1;
    

    避免编写出表达的一种方法是创建一个视图v1t1,其通过使它们简化的应用程序来选择full_name的情况下直接使用的表达式:

    CREATE VIEW v1 AS
    SELECT *, CONCAT(first_name,' ',last_name) AS full_name FROM t1;
    
    SELECT full_name FROM v1;
    

    生成的列还使应用程序可以full_name直接选择而无需定义视图:

    CREATE TABLE t1 (
      first_name VARCHAR(10),
      last_name VARCHAR(10),
      full_name VARCHAR(255) AS (CONCAT(first_name,' ',last_name))
    );
    
    SELECT full_name FROM t1;