JIYIK CN >

This article teaches you how to use MySQL VIEWand MERGEcreate table aliases.

In both cases, changes in the original table are reflected in the alias table. Also, SELECTquery results on the alias table and the original table will be the same.

VIEWCreating a table alias using MySQL

VIEWTable aliases created with MySQL allow a table to point to an original table. Behind the scenes, MySQL VIEWcreates a virtual copy of the original table.

For example, if you have TABLE_A, you can VIEWcreate it using TABLE_B. As a result, both tables will have the same data, and read and write queries will have the same effect on both.

To show how it works, create a database in MySQL and create a table using the following 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;

Next, VIEWcreate the table using MySQL dba_table:

CREATE VIEW dba_table AS SELECT * FROM database_administrators;

From this point on, database_administratorsrecords inserted into will be reflected in dba_table.

Insert records into the original table

Insert the records using the following command 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');

Confirm that the record exists in database_administrators:

SELECT * FROM database_administrators;

Output:

+--------+----------------+---------------+----------------+
| 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_tableYou get the same result when you check :

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)

Insert records into the virtual table

You can also dba_tableinsert records in , and you will database_administratorssee them in :

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

Check dba_tableif there is new data:

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)

Check database_administratorsif there is new data in :

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)

Update virtual table

dba_tableUpdates in will also be reflected in database_administrators:

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

Here are the updated tables in and dba_table: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)

Delete data from the original table

When you delete database_administratorsdata in , it is dba_tabledeleted in :

DELETE FROM database_administrators WHERE dba_id = 1;

database_administratorsThe deletion results are:

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)

As you can see, the deleted data no longer exists in 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)

MERGECreating a table alias using the MySQL table engine

Using MySQL MERGEtable engine, you can create alias from original table. Also, for it to work properly, original table should have MyISAM engine.

VIEWHowever, unlike aliases created with , MERGEaliases created with are read-only. This means that you cannot insert any data into the alias table.

To show how it works, create a database and create a table with the following content:

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;

Now, execute the following SQL in sequence:

CREATE TABLE mergecopy_devops_engineer SELECT * FROM devops_engineer;

ALTER TABLE mergecopy_devops_engineer ENGINE=MERGE;

ALTER TABLE mergecopy_devops_engineer UNION=(devops_engineer);

Using the previous SQL, mergecopy_devops_engineerbecome devops_engineera read-only replica of . Now, insert the record 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');

Here are devops_engineerthe new records in :

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)

When you inspect mergecopy_devops_engineer, the records are identical:

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)

At the same time, you can delete and update mergecopy_devops_engineerdata in . Therefore, you will see devops_engineerthe changes in .

However, you cannot insert data into it mergecopy_devops_engineerbecause it is read-only:

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