How to Create Table Alias With MySQL VIEW and MERGE
This article teaches you how to create a table alias using MySQL VIEW and MERGE.
In both cases, changes in the original table will reflect in the aliased table. Also, the results of SELECT queries on the aliased and original table will be the same.
Create Table Alias With MySQL VIEW
A table alias created with MySQL VIEW allows one table to point to an original table. Behind the scenes, MySQL VIEW creates a virtual copy of the original table.
For example, if you have TABLE_A, you can use VIEW to create TABLE_B. As a result, both tables will have the same data, and a read-write query has the same effect on both.
To show how this works, create a database in MySQL and use the following SQL to create a table:
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, use MySQL VIEW to create table dba_table:
CREATE VIEW dba_table AS SELECT * FROM database_administrators;
From this point onward, records inserted in database_administrators will reflect in dba_table.
Insert Records in the Original Table
Use the following to insert records into 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 the records 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)
When you check dba_table, you’ll get the same results:
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 in the Virtual Table
You can also insert records in dba_table, and you’ll see them in database_administrators:
INSERT INTO dba_table (dba_first_name, dba_last_name, dba_experience) VALUES('Calvert', 'Lewin', '9 years');
Check dba_table for the 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)
Confirm if the new data exists in 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)
Update the Virtual Table
Updates in the dba_table will also reflect in database_administrators:
UPDATE dba_table SET dba_experience = '4 years' WHERE dba_id = 2;
The following is the updated table in dba_table and 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 data in database_administrators, it gets deleted in dba_table:
DELETE FROM database_administrators WHERE dba_id = 1;
Result of the deletion on 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)
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)
Create Table Alias With MySQL MERGE Table Engine
With MySQL MERGE table engine, you can create an alias from an original table. Meanwhile, for this to work, the original table should have the MyISAM engine.
However, unlike the alias created with VIEW, the alias with MERGE is read-only. This means you cannot insert any data in the aliased table.
To show how this works, create a database and use the following to create a table:
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 one after the other:
CREATE TABLE mergecopy_devops_engineer SELECT * FROM devops_engineer;
ALTER TABLE mergecopy_devops_engineer ENGINE=MERGE;
ALTER TABLE mergecopy_devops_engineer UNION=(devops_engineer);
With the previous SQL, mergecopy_devops_engineer becomes a read-only copy of devops_engineer. Now, insert records into 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');
The following are the new records in 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)
When you check mergecopy_devops_engineer, the records are the same:
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)
Meanwhile, you can delete and update data in mergecopy_devops_engineer. As a result, you’ll see the changes in devops_engineer.
However, you cannot insert data into mergecopy_devops_engineer because it’s 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
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