MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Virtual Columns?

with one comment

While preparing for next week’s classes, I ran across a new future feature of MySQL – the virtual column. It appears, according to the article, that MySQL will have virtual columns in MySQL 6.

The syntax is virtually identical to that found in Oracle databases. For example, here’s what suppose to work in the future:

1
2
3
4
5
CREATE TABLE salary
( salary_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, salary       DOUBLE NOT NULL
, bonus        DOUBLE
, compensation DOUBLE AS (salary + bonus));

It’s virtually (excuse the pun) how it would work in Oracle Database 11g. I wonder what else we see move over into MySQL. Just a note, virtual columns moved from Oracle RDB (a prior acquisition from Digital Equipment Corporation in 1996) to the Oracle Database 11g.

Equivalent syntax in Oracle Database 11g would be like this for concatenation:

1
2
3
4
5
CREATE TABLE employee
( employee_id NUMBER
, first_name  VARCHAR2(20)
, last_name   VARCHAR2(20)
, full_name   VARCHAR2(41) AS (first_name || ' ' || last_name));

And like this for a math operation (like the one noted above for a future release of MySQL):

1
2
3
4
5
CREATE TABLE salary
( salary_id    NUMBER       CONSTRAINT pk_salary PRIMARY KEY
, salary       NUMBER(15,2) CONSTRAINT nn_salary_01 NOT NULL
, bonus        NUMBER(15,2)
, compensation NUMBER(15,2) AS (salary + bonus));

Written by maclochlainn

May 5th, 2011 at 7:18 pm

Posted in MySQL,Oracle,sql