Using Views to avoid cross database connection – MySQL
You can have your "contacts" table in "my_db" with following syntax:
CREATE TABLE `contacts` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `first_name` VARCHAR(100) NOT NULL, `last_name` VARCHAR(100) NOT NULL, `email` VARCHAR(100) NULL DEFAULT NULL, `cell_phone` VARCHAR(25) NULL DEFAULT NULL, `home_phone` VARCHAR(25) NULL DEFAULT NULL, `note` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM ROW_FORMAT=DEFAULT;
And you can create your view in "another_db" database with following syntax:
SELECT * FROM `my_db`.`contacts`
View definition will be updated to following automatically:
SELECT `my_db`.`contacts`.`id` AS `id`,`my_db`.`contacts`.`first_name` AS `first_name`,`my_db`.`contacts`.`last_name` AS `last_name`,`my_db`.`contacts`.`email` AS `email`,`my_db`.`contacts`.`cell_phone` AS `cell_phone`,`my_db`.`contacts`.`home_phone` AS `home_phone`,`my_db`.`contacts`.`note` AS `note` FROM `my_db`.`contacts`
That's it we are done.
Now we can execute any Insert, Update or Select query on this view, which will in turn change data in "mydb.contacts" table.
This can be a simpler solution if we want to avoid dealing with handling multiple database in PHP or any other server-side programming language.
INSERT INTO `v_contacts` (`first_name`, `last_name`, `email`, `cell_phone`, `note`) VALUES ('Bill', 'Gates', 'firstname.lastname@example.org', '7188802201', 'Former Microsoft CEO');
This is not something new; we knew and we read this all while we were at college. But it makes us feel good if we can put something like this at work which can be a small thing but can help us saving some time..!
Friends, comment if you liked this post, or share your experiences.