龙空技术网

MySQL生成列(二十六)

IT技术院 221

前言:

当前小伙伴们对“mysql指定列”大致比较注意,兄弟们都想要了解一些“mysql指定列”的相关资讯。那么小编同时在网络上网罗了一些有关“mysql指定列””的相关文章,希望咱们能喜欢,大家一起来学习一下吧!

在本教程中,您将学习如何使用MySQL生成的列来存储从表达式或其他列计算的数据。

MySQL生成列简介

创建新表时,可以在CREATE TABLE语句中指定表列。 然后,使用INSERT,UPDATE和DELETE语句直接修改表列中的数据。

MySQL 5.7引入了一个名为生成列的新功能。它之所以叫作生成列,因为此列中的数据是基于预定义的表达式或从其他列计算的。

例如,假设有以下结构的一个contacts表:

CREATE TABLE IF NOT EXISTS contacts ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL);

要获取联系人的全名,请使用CONCAT()函数,如下所示:

SELECT  id, CONCAT(first_name, ' ', last_name), emailFROM contacts;

这不是最优的查询。

通过使用MySQL生成的列,可以重新创建contacts表,如下所示:

DROP TABLE IF EXISTS contacts;CREATE TABLE contacts ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)), email VARCHAR(100) NOT NULL);

GENERATED ALWAYS as(expression)是创建生成列的语法。

要测试“全名”列,请在contacts表中插入一行。

INSERT INTO contacts(first_name,last_name, email)VALUES('john','doe','john.doe@yiibai.com');

现在,可以从contacts表中查询数据。

当从contacts表中查询数据时,fullname列中的值将立即计算。

MySQL提供了两种类型的生成列:存储和虚拟。每次读取数据时,虚拟列都将在运行中计算,而存储的列在数据更新时被物理计算和存储。

基于此定义,上述示例中的fullname列是虚拟列。

MySQL生成列的语法

定义生成列的语法如下:

column_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]]

首先,指定列名及其数据类型。

接下来,添加GENERATED ALWAYS子句以指示列是生成的列。

然后,通过使用相应的选项来指示生成列的类型:VIRTUAL或STORED。 默认情况下,如果未明确指定生成列的类型,MySQL将使用VIRTUAL。

之后,在AS关键字后面的大括号内指定表达式。 该表达式可以包含文字,内置函数,无参数,操作符或对同一表中任何列的引用。 如果你使用一个函数,它必须是标量和确定性的。

最后,如果生成的列被存储,可以为它定义一个唯一约束。

MySQL存储列示例

我们来看一下示例数据库(yiibaidb)中的products表。

mysql> desc products;+--------------------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------------------+---------------+------+-----+---------+-------+| productCode | varchar(15) | NO | PRI | | || productName | varchar(70) | NO | | NULL | || productLine | varchar(50) | NO | MUL | NULL | || productScale | varchar(10) | NO | | NULL | || productVendor | varchar(50) | NO | | NULL | || productDescription | text | NO | | NULL | || quantityInStock | smallint(6) | NO | | NULL | || buyPrice | decimal(10,2) | NO | | NULL | || MSRP | decimal(10,2) | NO | | NULL | |+--------------------+---------------+------+-----+---------+-------+9 rows in set

使用quantityInStock和buyPrice列的数据,通过以下表达式计算每个SKU的股票值:

quantityInStock * buyPrice

但是,可以使用以下ALTER TABLE … ADD COLUMN语句将名为stock_value的存储的生成列添加到products表:

ALTER TABLE productsADD COLUMN stockValue DOUBLE GENERATED ALWAYS AS (buyprice*quantityinstock) STORED;

通常,ALTER TABLE语句需要完整的表重建,因此,如果更改大表是耗时的。 但是,虚拟列并非如此。

现在,我们可以直接从products表中查询库存值。

SELECT  productName, ROUND(stockValue, 2) AS stock_valueFROM products;

执行上面查询语句,得到以下结果 -

+---------------------------------------------+-------------+| productName | stock_value |+---------------------------------------------+-------------+| 1969 Harley Davidson Ultimate Chopper | 387209.73 || 1952 Alpine Renault 1300 | 720126.90 || 1996 Moto Guzzi 1100i | 457058.75 || 2003 Harley-Davidson Eagle Drag Bike | 508073.64 || 1972 Alfa Romeo GTA | 278631.36 || 1962 LanciaA Delta 16V | 702325.22 || 1968 Ford Mustang | 6483.12 ||************** 省略了一大波数据 ****************************|| The Queen Mary | 272869.44 || American Airlines: MD-11S | 319901.40 || Boeing X-32A JSF | 159163.89 || Pont Yacht | 13786.20 |+---------------------------------------------+-------------+110 rows in set

在本教程中,我们向您介绍了MySQL生成的列以存储从表达式或其他列计算的数据。

标签: #mysql指定列