MySQL Forge

Resources for the MySQL Community

MySQL virtual columns ref manual

Contents 1 Overview 2 Description 2.1 TABLE CREATE 2.1.1 Syntax changes 2.1.2 Example 2.1.3 New MySQL errors 2.1.4 Unsupported expressions for virtual columns 2.2 ALTER TABLE 2.2.1 Syntax changes 2.2.2 New MySQL errors 2.3 DESCRIBE 2.4 SHOW CREATE TABLE 2.4.1 TODO: 2.5 INSERT & UPDATE 2.5.1 New MySQL errors 2.6 [EXPLAIN] SELECT & HANDLER 3 References

[edit] Overview

Main features provided to support of virtual columns are:

• full support of the MyISAM and INNODB storage engines
• full support of deterministic functions
• support of two types of virtual columns: stored and not stored (i.e. generated-only)
• partial support of indexes
  • no support of primary keys
  • no support of indexes based on generated-only virtual columns (I.e. indexes can only be based on stored virtual columns)
  • partial support of foreign keys (certain options such as ON DELETE SET NULL are restricted)
• full support for triggers and stored procedures
• full support for data queries (SELECT, HANDLER)
• full support of relevant DDL and informative SQL commands (CREATE TABLE, ALTER TABLE, DESCRIBE, SHOW CREATE TABLE, etc)
• full support of the partitioning based on virtual columns

[edit] Description

[edit] TABLE CREATE

[edit] Syntax changes

column_definition:
     data_type [NOT NULL | NULL] [DEFAULT default_value]
       [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
       [COMMENT 'string'] [reference_definition]
       [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
       [STORAGE {DISK|MEMORY}]
   | VIRTUAL data_type AS (expr) [UNIQUE [KEY]] 
       [COMMENT 'string'] [STORED]

where the syntax of data_type and expr has not been changed.

Note that unlike the implementation in Oracle, data_type is mandatory which will result in more predictable/reliable feature behavior for both the end user and MySQL engineers.

[edit] Example

SQL> create table t1 (a int not null, 
                      b varchar(32),
                      c virtual int as (a mod 10),
                      d virtual varchar(5) as (left(b,5)) stored);

[edit] New MySQL errors

Error	Explanation
A virtual column cannot be based on a virtual column
Non-deterministic expression for virtual column '%s'.	See "unsupported expressions" below.
Primary key cannot be defined upon a virtual column.
Key/Index cannot be defined on a non-stored virtual column.
Cannot define foreign key with %s clause on a virtual column.	%s can take three values: "ON UPDATE SET NULL", "ON UPDATE CASCADE" and "ON DELETE SET NULL"
'This type of storage engine' is not yet supported for virtual columns.	Generated for unsupported storage engines. Currently only MyISAM and InnoDB are supported.
Unknown column '%s' in 'virtual column function’	Error is generated when the virtual column expression has a reference to a column non-existent in the same table. This error is generated on creating a virtual column or renaming or dropping a field that an existing virtual column is based on.
Constant expression in virtual column function is not allowed.

[edit] Unsupported expressions for virtual columns

All functions described here are supported for the definition of virtual columns except for:

• Subqueries
• Stored procedures
• Long expression (expr length > 242)
• GROUP BY aggregate functions
• RAND()
• LOAD_FILE()
• CURDATE()
• CURRENT_DATE(), CURRENT_DATE
• CURRENT_TIME(), CURRENT_TIME
• CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
• CURTIME()
• LOCALTIME(), LOCALTIME
• LOCALTIMESTAMP, LOCALTIMESTAMP()
• NOW()
• SYSDATE()
• UNIX_TIMESTAMP()
• UTC_DATE()
• UTC_TIME()
• UTC_TIMESTAMP()
• MATCH()
• BENCHMARK()
• CONNECTION_ID()
• CURRENT_USER(), CURRENT_USER
• DATABASE()
• FOUND_ROWS()
• GET_LOCK()
• IS_FREE_LOCK()
• IS_USED_LOCK()
• LAST_INSERT_ID()
• MASTER_POS_WAIT()
• NAME_CONST()
• RELEASE_LOCK()
• ROW_COUNT()
• SCHEMA()
• SESSION_USER()
• SLEEP()
• SYSTEM_USER()
• USER()
• UUID_SHORT()
• UUID()
• VALUES()
• VERSION()
• ENCRYPT()
• ExtractValue()
• UpdateXML()

[edit] ALTER TABLE

[edit] Syntax changes

alter_specification:
   table_option ...
 | ADD [COLUMN] col_name column_definition
       [FIRST | AFTER col_name ]
 | ADD [COLUMN] (col_name column_definition,...)
 | ADD {INDEX|KEY} [index_name]
       [index_type] (index_col_name,...) [index_option] ...
 | ADD [CONSTRAINT [symbol]] PRIMARY KEY
       [index_type] (index_col_name,...) [index_option] ...
 | ADD [CONSTRAINT [symbol]]
       UNIQUE [INDEX|KEY] [index_name]
       [index_type] (index_col_name,...) [index_option] ...
 | ADD FULLTEXT [INDEX|KEY] [index_name]
       (index_col_name,...) [index_option] ...
 | ADD SPATIAL [INDEX|KEY] [index_name]
       (index_col_name,...) [index_option] ...
 | ADD [CONSTRAINT [symbol]]
       FOREIGN KEY [index_name] (index_col_name,...)
       reference_definition
 | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
 | CHANGE [COLUMN] old_col_name new_col_name column_definition
       [FIRST|AFTER col_name]
 | MODIFY [COLUMN] col_name column_definition
       [FIRST | AFTER col_name]
 | DROP [COLUMN] col_name
 | DROP PRIMARY KEY
 | DROP {INDEX|KEY} index_name
 | DROP FOREIGN KEY fk_symbol
 | DISABLE KEYS
 | ENABLE KEYS
 | RENAME [TO] new_tbl_name
 | ORDER BY col_name [, col_name] ...
 | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
 | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
 | DISCARD TABLESPACE
 | IMPORT TABLESPACE
 | partition_options
 | ADD PARTITION (partition_definition)
 | DROP PARTITION partition_names
 | COALESCE PARTITION number
 | REORGANIZE PARTITION partition_names INTO (partition_definitions)
 | ANALYZE PARTITION partition_names
 | CHECK PARTITION partition_names
 | OPTIMIZE PARTITION partition_names
 | REBUILD PARTITION partition_names
 | REPAIR PARTITION partition_names
 | REMOVE PARTITIONING
column_definition:
     data_type [NOT NULL | NULL] [DEFAULT default_value]
       [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
       [COMMENT 'string'] [reference_definition]
       [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
       [STORAGE {DISK|MEMORY}]
   | VIRTUAL data_type AS (expr) [UNIQUE [KEY]] 
       [COMMENT 'string'] [STORED]

[edit] New MySQL errors

The same as for CREATE TABLE.

[edit] DESCRIBE

Virtual fields are shown in the same way as their “real” analogs except for word “VIRTUAL” printed in the Extra column. Example:

describe t1; 
+-------+-------------+------+-----+---------+---------+ 
| Field | Type        | Null | Key | Default | Extra   | 
+-------+-------------+------+-----+---------+---------+ 
| a     | int(11)     | NO   |     | NULL    |         |  
| b     | varchar(10) | YES  |     | NULL    |         |  
| c     | int(11)     | YES  |     | NULL    | VIRTUAL |  
| d     | varchar(10) | YES  |     | NULL    | VIRTUAL |  
+-------+-------------+------+-----+---------+---------+ 

[edit] SHOW CREATE TABLE

Effectively the new CREATE TABLE syntax is restored. For example:

SQL> show create table t1; 

<header skipped>

| t1    | CREATE TABLE `t1` ( 
  `a` int(11) NOT NULL, 
  `b` varchar(10) DEFAULT NULL, 
  `c` VIRTUAL int(11) AS (a mod 10), 
  `d` VIRTUAL varchar(10) AS (left(b,5)) STORED 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |  

<tail skipped> 1 row in set (0.00 sec)

[edit] TODO:

Currently, the virtual column expressions are printed in the form they were entered at the field creation time. So if the fields names were specified unquoted then they will be printed by SHOW CREATE TABLE unquoted as well (i.e. “left(b,5)” instead of “left(`b`,5)”).

[edit] INSERT & UPDATE

Unlike the implementation in Oracle, the user can specify any value for a virtual field when inserting and updating. However, a warning like the following is generated every time the user puts any value other than NULL or DEFAULT.

For example, this is correct, no warnings are generated:

SQL> warnings;
SQL> insert into t1 values (1,'sample text',default,default);
Query OK, 1 row affected, 1 warning (0.01 sec) 

This is not correct:

SQL> warnings;
SQL> insert into t1 values (2,'sample text',5,default); 
Query OK, 1 row affected, 1 warning (0.01 sec) 
Warning 1680 The value specified for virtual column 'c' in table 't1' ignored.

In addition, the user will get a standard warning when the generated value of a virtual column will be truncated.

[edit] New MySQL errors

Error	Explanation
The value specified for virtual column '%s' in table '%s' ignored.	See above

[edit] [EXPLAIN] SELECT & HANDLER

Virtual columns can be used in SQL queries in the same way as “real” fields. The only difference in behavior is how the data is retrieved. The basic principles are:

• virtual columns stored in the database are read as the “real” fields.
• virtual columns not stored in the database are always generated/calculated on the fly
• virtual columns not involved in the query are not calculated

For example, SELECTing all data from the sample table t1 would result in `c` being calculated and `d` being read from the database:

SQL> select * from t1;                                  
+---+-------------+------+-------+ 
| a | b           | c    | d     | 
+---+-------------+------+-------+ 
| 1 | sample text |    1 | sampl |  
| 2 | sample text |    2 | sampl |  
+---+-------------+------+-------+ 
2 rows in set (0.00 sec) 

If an index is defined on a virtual column then the optimizer considers using it in the same way as indexes based on “real” columns.

[edit] References

• Preview on the Forge Wiki: MySQL support for virtual columns
• Source code on the Launchpad: https://code.launchpad.net/~andrey-zhakov/mysql-server/mysql-6.0-wl1075-wl411