使用 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