生成列(generated column
)的值是根据列定义中包含的表达式计算得出的。 生成列包含下面两种类型:
virtual
(虚拟):当从表中读取记录时,将动态计算该列。stored
(存储):当向表中写入新记录时,将计算该列并将其存储为常规列。
virtual
生成列比stored
生成列更有用,因为一个虚拟的列不占用任何存储空间。你可以使用触发器模拟stored
生成列的行为。
案例
1. 创建表时
假设你的应用程序从t_employees_generated
表中检索数据时,使用full_name
表示concat(first_name, ' ', last_name)
,而不是使用表达式来表示,从而实现虚拟列实时计算full_name
。
# 创建测试表
mysql> create table if not exists employees.t_employees_generated (
-> emp_no int(11) not null,
-> birth_date date not null,
-> first_name varchar(14) not null,
-> last_name varchar(16) not null,
-> gender enum('M', 'F') not null,
-> hire_date date not null,
-> full_name varchar(30) as (concat(first_name, ' ', last_name)),
-> primary key (emp_no),
-> key name (first_name, last_name)
-> ) engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.04 sec)
请注意,应该根据虚拟列修改插入语句。你可以这样使用full insert
:
# 执行插入
mysql> insert into employees.t_employees_generated
-> (emp_no, birth_date, first_name, last_name, gender, hire_date)
-> values
-> (123456, '1987-10-02', 'ABC', 'XYZ', 'F', '2008-07-28');
Query OK, 1 row affected (0.11 sec)
# 验证数据
mysql> select * from employees.t_employees_generated where emp_no = '123456';
+--------+------------+------------+-----------+--------+------------+-----------+
| emp_no | birth_date | first_name | last_name | gender | hire_date | full_name |
+--------+------------+------------+-----------+--------+------------+-----------+
| 123456 | 1987-10-02 | ABC | XYZ | F | 2008-07-28 | ABC XYZ |
+--------+------------+------------+-----------+--------+------------+-----------+
1 row in set (0.00 sec)
如果要在INSERT
语句中包含full_name
,就只能将其指定为DEFAULT
。
# 执行插入
mysql> insert into employees.t_employees_generated
-> (emp_no, birth_date, first_name, last_name, gender, hire_date, full_name)
-> values
-> (123457, '1987-10-02', 'ABC', 'XYZ', 'F', '2008-07-28', DEFAULT);
Query OK, 1 row affected (0.01 sec)
# 验证数据
mysql> select * from employees.t_employees_generated where emp_no = '123457';
+--------+------------+------------+-----------+--------+------------+-----------+
| emp_no | birth_date | first_name | last_name | gender | hire_date | full_name |
+--------+------------+------------+-----------+--------+------------+-----------+
| 123457 | 1987-10-02 | ABC | XYZ | F | 2008-07-28 | ABC XYZ |
+--------+------------+------------+-----------+--------+------------+-----------+
1 row in set (0.00 sec)
其他值都会引发ERROR 3105
:错误,不允许在t_employees_generated
表中为生成的列full_name
指定值:
mysql> insert into employees.t_employees_generated
-> (emp_no, birth_date, first_name, last_name, gender, hire_date, full_name)
-> values
-> (123458, '1987-10-02', 'ABC', 'XYZ', 'F', '2008-07-28', 'TEST');
ERROR 3105: The value specified for generated column 'full_name' in table 't_employees_generated' is not allowed.
2. 已有表时
如果你已经创建了表并希望添加新的生成列,请执行ALTER TABLE
语句。
# 修改表结构 - 新增生成列
mysql> alter table employees.t_employees_generated add hire_date_year year as (year(hire_date)) virtual;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 验证数据
mysql> select * from employees.t_employees_generated where emp_no = '123456';
+--------+------------+------------+-----------+--------+------------+-----------+----------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date | full_name | hire_date_year |
+--------+------------+------------+-----------+--------+------------+-----------+----------------+
| 123456 | 1987-10-02 | ABC | XYZ | F | 2008-07-28 | ABC XYZ | 2008 |
+--------+------------+------------+-----------+--------+------------+-----------+----------------+
1 row in set (0.00 sec)
# 查看表结构
mysql> desc employees.t_employees_generated;
+----------------+---------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------------------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | MUL | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
| full_name | varchar(30) | YES | | NULL | VIRTUAL GENERATED |
| hire_date_year | year(4) | YES | | NULL | VIRTUAL GENERATED |
+----------------+---------------+------+-----+---------+-------------------+
8 rows in set (0.00 sec)
参考
原文地址:http://www.cnblogs.com/ciel717/p/16254368.html
1. 本站所有资源来源于用户上传和网络,如有侵权请邮件联系站长!
2. 分享目的仅供大家学习和交流,请务用于商业用途!
3. 如果你也有好源码或者教程,可以到用户中心发布,分享有积分奖励和额外收入!
4. 本站提供的源码、模板、插件等等其他资源,都不包含技术服务请大家谅解!
5. 如有链接无法下载、失效或广告,请联系管理员处理!
6. 本站资源售价只是赞助,收取费用仅维持本站的日常运营所需!
7. 如遇到加密压缩包,默认解压密码为"gltf",如遇到无法解压的请联系管理员!
8. 因为资源和程序源码均为可复制品,所以不支持任何理由的退款兑现,请斟酌后支付下载
声明:如果标题没有注明"已测试"或者"测试可用"等字样的资源源码均未经过站长测试.特别注意没有标注的源码不保证任何可用性