MySQL virtual columns preview
Contents |
[edit] Introduction
MySQL virtual columns is a new feature that is currently under development.
As can be guessed from the definition, the term "virtual column" denotes two meanings. First of all, the new column type behaves as a SQL table field when you query or describe a table, define indexes or constraints. From the other hand virtual columns differ from real ones by the way the field values are specified and stored.
The virtual column is defined using a determenistic expression containing references to real field(s) within the same table. This expression is then used to generate a value for this virtual column when inserting or updating the table.
Another feature of the virtual columns is an option to make the virtual field be not physically stored in the database but be always generated on every query (note that as of now, this is not an option for virtual columns on which an index/key is defined as described in Restrictions/Limitations).
[edit] Simple example: Unique index on virtual column
Lets assume you have a table with two columns. The first one is a record type (e.g. values 'A', 'B' and 'C') and the second column is a number. And suppose you need to ensure that type 'A' records have unique column b values.
create table t1 (
a enum('A','B','C'),
b int,
c virtual int as (if(a<>'A',null,b)) stored unique);
insert into t1 values ('A',1,default);
Query OK, 1 row affected (0.00 sec)
insert into t1 values ('A',1,default);
ERROR 1062 (23000): Duplicate entry '1' for key 'c'
insert into t1 values ('B',1,default);
Query OK, 1 row affected (0.00 sec)
select * from t1; +------+------+------+ | a | b | c | +------+------+------+ | A | 1 | 1 | | B | 1 | NULL | +------+------+------+ 2 rows in set (0.00 sec)
[edit] Use cases
Potentially this feature can be used to:
- speed up queries using functional indexes
- simplify frequently used queries
- define function-based constaints (unique indexes and foreign keys)
[edit] Non-stored virtual columns vs. Views
You may notice that non-stored (generated-only) columns can be completely replaced by Views. Your are right. However using virtual columns will reduce the need for new database object such as Views to provide derived column values.
[edit] Stored virtual columns vs. columns filled in by Triggers
You may also notice that the stored virtual columns is what can be implemented via a combination of real fields and BEFORE INSERT Triggers. To some extent this is correct. However, if the function determining the derived column is altered then values of the virtual field will be automatically changed whereas values filled in by Triggers will remain the same. As such, you should consider using Triggers or stored virtual columns depending on your requirements.
[edit] Reference manual
Detailed description of the feature can be found here.
[edit] Restrictions/Limitations
Restricted operations:
- Creating virtual columns for storage engines other than MyISAM and InnoDB
- Creating virtual columns based on non-determenistic and constant functions
- Defining primary key on a virtual column
- Defining a key/index on a non-stored virtual column
- Defining a virtual column based on another virtual column
- Renaming/dropping a column on which a virtual column is based
- Defining FOREIGN KEY on a virtual column with options
- ON UPDATE SET NULL
- ON UPDATE CASCADE
- ON DELETE SET NULL
Allowed but not recommended operations (warnings are generated):
- Specifying a value other than DEFAULT and NULL for a virtual column when inserting or updating the table
[edit] Downloads
[edit] Binary packages
None yet.
[edit] Development Source Code
The source code can be downloaded from the Launchpad server at https://code.launchpad.net/~andrey-zhakov/mysql-server/mysql-6.0-wl1075-wl411.