使用 MySQL VIEW 和 MERGE 创建表别名

Habdul Hazeez 2023年1月30日 2022年7月18日
  1. 使用 MySQL VIEW 创建表别名
  2. 使用 MySQL MERGE 表引擎创建表别名
使用 MySQL VIEW 和 MERGE 创建表别名

本文教你如何使用 MySQL VIEWMERGE 创建表别名。

在这两种情况下,原始表中的更改都会反映在别名表中。此外,对别名表和原始表的 SELECT 查询结果将是相同的。

使用 MySQL VIEW 创建表别名

使用 MySQL VIEW 创建的表别名允许一个表指向原始表。在幕后,MySQL VIEW 创建原始表的虚拟副本。

例如,如果你有 TABLE_A,你可以使用 VIEW 创建 TABLE_B。结果,两个表将具有相同的数据,并且读写查询对两者具有相同的效果。

为了展示它是如何工作的,在 MySQL 中创建一个数据库并使用以下 SQL 创建一个表:

CREATE TABLE database_administrators (
    dba_id INT NOT NULL AUTO_INCREMENT,
    dba_first_name VARCHAR(50) NOT NULL,
    dba_last_name VARCHAR(50) NOT NULL,
    dba_experience VARCHAR(10) NOT NULL,
    PRIMARY KEY (dba_id)
) ENGINE = InnoDB;

接下来,使用 MySQL VIEW 创建表 dba_table

CREATE VIEW dba_table AS SELECT * FROM database_administrators;

从此时起,插入 database_administrators 中的记录将反映在 dba_table 中。

在原始表中插入记录

使用以下命令将记录插入 database_administrators

INSERT INTO database_administrators (dba_first_name, dba_last_name, dba_experience) VALUES('Michael', 'Faraday', '3 years');
INSERT INTO database_administrators (dba_first_name, dba_last_name, dba_experience) VALUES('Joseph', 'Klinsman', '2 years');
INSERT INTO database_administrators (dba_first_name, dba_last_name, dba_experience) VALUES('Matt', 'Abott', '2 years');

确认记录存在于 database_administrators 中:

SELECT * FROM database_administrators;

输出:

+--------+----------------+---------------+----------------+
| dba_id | dba_first_name | dba_last_name | dba_experience |
+--------+----------------+---------------+----------------+
|      1 | Michael        | Faraday       | 3 years        |
|      2 | Joseph         | Klinsman      | 2 years        |
|      3 | Matt           | Abott         | 2 years        |
+--------+----------------+---------------+----------------+
3 rows in set (0.00 sec)

当你检查 dba_table 时,你会得到相同的结果:

mysql > SELECT * FROM dba_table;
+--------+----------------+---------------+----------------+
| dba_id | dba_first_name | dba_last_name | dba_experience |
+--------+----------------+---------------+----------------+
|      1 | Michael        | Faraday       | 3 years        |
|      2 | Joseph         | Klinsman      | 2 years        |
|      3 | Matt           | Abott         | 2 years        |
+--------+----------------+---------------+----------------+
3 rows in set (0.00 sec)

在虚拟表中插入记录

你还可以在 dba_table 中插入记录,你将在 database_administrators 中看到它们:

INSERT INTO dba_table (dba_first_name, dba_last_name, dba_experience) VALUES('Calvert', 'Lewin', '9 years');

检查 dba_table 是否有新数据:

mysql > SELECT * FROM dba_table;
+--------+----------------+---------------+----------------+
| dba_id | dba_first_name | dba_last_name | dba_experience |
+--------+----------------+---------------+----------------+
|      1 | Michael        | Faraday       | 3 years        |
|      2 | Joseph         | Klinsman      | 2 years        |
|      3 | Matt           | Abott         | 2 years        |
|      4 | Calvert        | Lewin         | 9 years        |
+--------+----------------+---------------+----------------+
4 rows in set (0.00 sec)

确认 database_administrators 中是否存在新数据:

mysql > SELECT * FROM database_administrators;
+--------+----------------+---------------+----------------+
| dba_id | dba_first_name | dba_last_name | dba_experience |
+--------+----------------+---------------+----------------+
|      1 | Michael        | Faraday       | 3 years        |
|      2 | Joseph         | Klinsman      | 2 years        |
|      3 | Matt           | Abott         | 2 years        |
|      4 | Calvert        | Lewin         | 9 years        |
+--------+----------------+---------------+----------------+
4 rows in set (0.00 sec)

更新虚拟表

dba_table 中的更新也将反映在 database_administrators 中:

UPDATE dba_table SET dba_experience = '4 years' WHERE dba_id = 2;

以下是 dba_tabledatabase_administrators 中的更新表:

+--------+----------------+---------------+----------------+
| dba_id | dba_first_name | dba_last_name | dba_experience |
+--------+----------------+---------------+----------------+
|      1 | Michael        | Faraday       | 3 years        |
|      2 | Joseph         | Klinsman      | 2 years        |
|      3 | Matt           | Abott         | 2 years        |
|      4 | Calvert        | Lewin         | 9 years        |
+--------+----------------+---------------+----------------+
4 rows in set (0.00 sec)

从原始表中删除数据

当你删除 database_administrators 中的数据时,它会在 dba_table 中删除:

DELETE FROM database_administrators WHERE dba_id = 1;

database_administrators 的删除结果:

mysql> SELECT * from database_administrators;
+--------+----------------+---------------+----------------+
| dba_id | dba_first_name | dba_last_name | dba_experience |
+--------+----------------+---------------+----------------+
|      2 | Joseph         | Klinsman      | 4 years        |
|      3 | Matt           | Abott         | 2 years        |
|      4 | Calvert        | Lewin         | 9 years        |
+--------+----------------+---------------+----------------+
3 rows in set (0.00 sec)

如你所见,删除的数据不再存在于 dba_table 中:

mysql> SELECT * from dba_table;
+--------+----------------+---------------+----------------+
| dba_id | dba_first_name | dba_last_name | dba_experience |
+--------+----------------+---------------+----------------+
|      2 | Joseph         | Klinsman      | 4 years        |
|      3 | Matt           | Abott         | 2 years        |
|      4 | Calvert        | Lewin         | 9 years        |
+--------+----------------+---------------+----------------+
3 rows in set (0.00 sec)

使用 MySQL MERGE 表引擎创建表别名

使用 MySQL MERGE 表引擎,你可以从原始表创建别名。同时,要使其正常工作,原始表应具有 MyISAM 引擎。

但是,与使用 VIEW 创建的别名不同,使用 MERGE 的别名是只读的。这意味着你不能在别名表中插入任何数据。

为了展示它是如何工作的,请创建一个数据库并使用以下内容创建一个表:

CREATE TABLE devops_engineer (
    engineer_id INT NOT NULL AUTO_INCREMENT,
    engineer_first_name VARCHAR(50) NOT NULL,
    engineer_last_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (engineer_id)
) ENGINE = MyISAM;

现在,依次执行以下 SQL:

CREATE TABLE mergecopy_devops_engineer SELECT * FROM devops_engineer;

ALTER TABLE mergecopy_devops_engineer ENGINE=MERGE;

ALTER TABLE mergecopy_devops_engineer UNION=(devops_engineer);

使用之前的 SQL,mergecopy_devops_engineer 成为 devops_engineer 的只读副本。现在,将记录插入 devops_engineer

INSERT INTO devops_engineer (engineer_first_name, engineer_last_name) VALUES('Delft', 'Stack');
INSERT INTO devops_engineer (engineer_first_name, engineer_last_name) VALUES('Margaret', 'Thatcher');

以下是 devops_engineer 中的新记录:

mysql> SELECT * FROM devops_engineer;
 +-------------+---------------------+--------------------+
| engineer_id | engineer_first_name | engineer_last_name |
+-------------+---------------------+--------------------+
|           1 | Delft               | Stack              |
|           2 | Margaret            | Thatcher           |
+-------------+---------------------+--------------------+
2 rows in set (0.03 sec)

当你检查 mergecopy_devops_engineer 时,记录是相同的:

mysql> SELECT * FROM mergecopy_devops_engineer;
 +-------------+---------------------+--------------------+
| engineer_id | engineer_first_name | engineer_last_name |
+-------------+---------------------+--------------------+
|           1 | Delft               | Stack              |
|           2 | Margaret            | Thatcher           |
+-------------+---------------------+--------------------+
2 rows in set (0.03 sec)

同时,你可以删除和更新 mergecopy_devops_engineer 中的数据。因此,你将看到 devops_engineer 中的更改。

但是,你不能将数据插入 mergecopy_devops_engineer,因为它是只读的:

mysql> INSERT INTO mergecopy_devops_engineer (engineer_first_name, engineer_last_name) VALUES('John', 'Doe');
ERROR 1036 (HY000): Table 'mergecopy_devops_engineer' is read only
Habdul Hazeez avatar Habdul Hazeez avatar

Habdul Hazeez is a technical writer with amazing research skills. He can connect the dots, and make sense of data that are scattered across different media.

LinkedIn

相关文章 - MySQL Table