MySQL Synonym?
Somebody asked how to create a SYNONYM in MySQL, which is interesting because MySQL doesn’t support synonyms. I thought the prior entry explained how to do it, but here’s a new post. However, you can create a view in one database that relies on a table in another database.
The following SQL statements create two databases and grant appropriate privileges to the student as the root superuser:
/* Create two databases. */ CREATE DATABASE seussdb; CREATE DATABASE appdb; /* Grant privileges to a student user. */ GRANT ALL ON seussdb.* TO student; GRANT ALL ON appdb.* TO student; |
Log out from the root superuser and reconnect as the student user. Then, the following code connects to the seuss database and create a hat table; and inserts two rows into the hat table:
/* Use the appdb database. */ USE seussdb; /* Create a hat table. */ CREATE TABLE hat ( hat_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , hat_text VARCHAR(20)); /* Insert two rows into the hat table. */ INSERT INTO hat (hat_text) VALUES ('Thing 1'); INSERT INTO hat (hat_text) VALUES ('Thing 2'); |
The following code connects to the application database and creates a hat view; and then the code inserts one additional row into the hat table:
/* Connect to the application database. */ USE appdb; /* Create a hat view. */ CREATE VIEW hat AS SELECT * FROM seussdb.hat; /* Query the contents of the view, or seuss.hat table. */ SELECT * FROM hat; /* Insert a new row into the hat table. */ INSERT INTO hat (hat_text) VALUES ('Thing 3'); /* Query the contents of the view, after insert to the view. */ SELECT * FROM hat; |
The results will be the following:
+--------+----------+ | hat_id | hat_text | +--------+----------+ | 1 | Thing 1 | | 2 | Thing 2 | | 3 | Thing 3 | +--------+----------+ |
I hope this answers the question on how to mimic the Oracle database’s synonyms. The appdb.hat view acts as a synonym to the seuss.hat table.
Subscribe via RSS