- 快召唤伙伴们来围观吧
- 微博 QQ QQ空间 贴吧
- 文档嵌入链接
- 复制
- 微信扫一扫分享
- 已成功复制到剪贴板
Virtual Columns in MySQL and MariaDB
请加入费德里科·拉佐利(Federico Razzoli),这位顾问展示了“MySQL和Mariadb中的虚拟专栏”。
Mariadb5.2和MySQL5.7引入了虚拟列,具有不同的实现。
它们的特点和局限性是相似的,但不完全相同。主要的区别是只有MySQL允许您在非持久列上构建索引。
在本文中,我们将介绍虚拟列的一些用例,例如查询简化和基于SQL表达式的唯一约束。特别是,我们将看到如何使用虚拟列来索引MySQL中的JSON数据,或者Mariadb中的动态列。
还将讨论性能和限制。
展开查看详情
1 .Virtual Columns in MySQL and MariaDB
2 .$ whoami • My name is Federico Razzoli • Percona Consultant • MariaDB Community Ambassador • federico.razzoli@percona.com
3 .What is a Virtual Column? It’s a… column. But: • Its value is generated automatically • Can be stored on disk or computed on the fly • VIRTUAL, STORED • Cannot be changed directly by the user (UPDATE) • Changed automatically by DBMS
4 .Supported in… Syntax and semantics are not standard • DB2 (Expression Generated Columns) • Sql Server (Computed, Persisted) • Oracle 11g (only virtual) Open source world: • Firebird (Computed Columns) • PostgreSQL has functional indexes
5 .What about the MySQL world? • MariaDB 5.2 (2010), improved in 10.2 • MySQL 5.7 (2015)
6 .MySQL syntax <type> [GENERATED ALWAYS] AS ( < expression > ) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT < text >] [NOT NULL | NULL] [[PRIMARY] KEY] Example: taxed_price INTEGER UNSIGNED AS (price + (price / 100 * 20)) VIRTUAL NOT NULL
7 .MariaDB syntax <type> [GENERATED ALWAYS] AS ( <expression> ) [VIRTUAL | PERSISTENT | STORED] [UNIQUE [KEY]] [COMMENT <text>] Example: taxed_price INTEGER UNSIGNED AS (price + (price / 100 * 20)) VIRTUAL
8 .Virtual Column values • Simple SQL expression • No Stored Functions • No Subqueries • No Variables
9 .Virtual Column types Virtual: • Generated when needed (SELECT) • No overhead on writes Stored/Persistent: • Written on disk • No delay on SELECT
10 .Alternatives: Views Pros: • Any expression is allowed • Value can be NULL (MariaDB) Cons: • Administration is harder • Cannot index the value
11 .View Example CREATE VIEW v_product AS ( SELECT id, price, name, description, (price + (price / 100 * 20)) AS taxed_price FROM v_product );
12 .Alternatives: Triggers Pros: • More flexible Cons: • Complex triggers are slow • Administration is harder
13 .Triggers Example CREATE TRIGGER … BEFORE INSERT … BEGIN SET NEW.taxed_price := NEW.price + (price / 100 * 20); SET NEW.label := IFNULL(name, id); END CREATE TRIGGER … BEFORE UPDATE … BEGIN SET NEW.taxed_price := NEW.price + (price / 100 * 20); SET NEW.label := IFNULL(name, id); END
14 .Triggers Example CREATE TRIGGER … BEFORE INSERT … BEGIN SET NEW.taxed_price := NEW.price + (price / 100 * (SELECT … FROM tax WHERE ...)); END CREATE TRIGGER … BEFORE UPDATE … BEGIN SET NEW.taxed_price := NEW.price + (price / 100 * (SELECT … FROM tax WHERE ...)); END
15 .Alternatives: DEFAULT in MariaDB 10.2 • Can be the right choice • Depending if you want the value to be immutable and updated automatically
16 .DEFAULT Example taxed_price INTEGER UNSIGNED NOT NULL DEFAULT (price + (price / 100 * 20))
17 .Summary of Alternatives Virtual: • Views Stored: • Triggers • DEFAULT (MariaDB 10.2)
18 .More Virtual Columns Examples name VARCHAR(50) NOT NULL, normalized_name VARCHAR(50) AS (REPLACE(name, ' ', ''))
19 .More Virtual Columns Examples birth_date DATE NOT NULL, death_date DATE, is_alive BOOL AS (death_date IS NULL)
20 .More Virtual Columns Examples age INTEGER AS (DATEDIFF(NOW(), birth_date) DIV 365) STORED
21 .Indexes on Virtual Columns MySQL: • Both STORED and VIRTUAL columns can be indexed MariaDB: • Only STORED columns can be indexed
22 .Indexed Virtual Columns Examples name VARCHAR(50) NOT NULL, normalized_name VARCHAR(50) AS (REPLACE(name, ' ', '')) UNIQUE
23 .Indexed Virtual Columns Examples label VARCHAR(10) NOT NULL, prefix VARCHAR(3) AS (LEFT(label, 3)) UNIQUE
24 .MySQL Limitations A generated value: • Cannot refer to an AUTO_INCREMENT column • Can refer to a virtual column if it appears earlier in CREATE TABLE • Has to be deterministic • Cannot use variables Syntax: • Cannot specify DEFAULT value in an INSERT • Cannot use: INSERT INTO t SELECT * FROM t
25 .MariaDB Limitations • No binary compatibility with MySQL • Dump is required to switch between MySQL and MariaDB • MariaDB virtual columns cannot be NULLable • and NOT NULL syntax is not supported • Cannot be used as Primary Keys • STORED keyword not supported before MariaDB 10.2 • Many functions not supported before MariaDB 10.2
26 .MariaDB 10.2 Limitations • Can refer to a virtual column if it appears before in CREATE TABLE • To avoid circular references • Non-STORED columns need to be deterministic
27 .Optimizer CREATE TABLE person ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), initials VARCHAR(2) AS (UPPER(CONCAT(LEFT(first_name, 1), LEFT(last_name, 1)))) PERSISTENT, INDEX idx_initials (initials) ); SELECT * FROM person WHERE UPPER(CONCAT(LEFT(first_name, 1), LEFT(last_name, 1))) = 'MJ‘;
28 .MySQL Optimizer mysql> EXPLAIN SELECT * FROM person -> WHERE UPPER(CONCAT(LEFT(first_name, 1), LEFT(last_name, 1))) = 'MJ' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: person partitions: NULL type: ref possible_keys: idx_initials key: idx_initials key_len: 5 ref: const rows: 1 filtered: 100.00
29 .Storage Engines The following engines support Virtual Columns: • InnoDB • MyISAM • Aria (MariaDB) • CONNECT (MariaDB)