Mysql – Connecting to “foreign” database on the same DBMS

jdbcmariadbMySQL

I am somewhat surprised to see that JDBC allows me to transparently SELECT from a table in a "foreign" database, on the same DBMS, for which I have the requisite privileges, without requiring an explicit connection to the foreign database. Is this how it is supposed to be with MySQL, or is it just a JDBC quirk?

Details:
I created two databases on my DBMS: stkovrflo_1 and stkovrflo_2. I populated tables in these databases from the MySQL World database.

CREATE TABLE stkovrflo_1.Country
SELECT name, region FROM world.Country;

CREATE TABLE stkovrflo_2.City
SELECT world.City.name, world.Country.name AS country
FROM world.City INNER JOIN world.Country ON world.City.CountryCode = world.Country.code;

In JDBC I am able to select entries to the stkovrflo_2.City table via a connection to the stkovrflo_1 database. I have SELECT access to both databases.

Here is my JDBC code:

import java.sql.*;

public class JDBCExample {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://localhost/stkovrflo_1";

    public static void main(String[] args) throws Exception{
        
        String uid = args[0];
        String pswd = args[1];
        
        if(pswd.toUpperCase().equals("NULL"))
            pswd = null;

        Connection conn = null;
        conn = DriverManager.getConnection(DB_URL,uid,pswd);
        
        processTableSameDB(conn);
        System.out.println("\n\n");
        processTableDifferentDB(conn);
        
        if(conn != null)
            conn.close();
    }

    protected static void processTableSameDB(Connection conn) throws Exception {
        Statement stmt = null;
        String tableName = "Country";

        try{
            Class.forName("com.mysql.jdbc.Driver");

            System.out.println("Retrieving from table in same database...");

            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM " + tableName + " LIMIT 10";
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next()){
                String name = rs.getString("name");
                String region = rs.getString("region");

                System.out.print("Name: " + name);
                System.out.println(", Region: " + region);
            }
            rs.close();
        }
        catch(SQLException se){
            se.printStackTrace();
        }
        finally{
            if(stmt!=null)
                stmt.close();
        }
    }

    protected static void processTableDifferentDB(Connection conn) throws Exception {
        Statement stmt = null;
        String tableName = "stkovrflo_2.City";

        try{
            Class.forName("com.mysql.jdbc.Driver");

            System.out.println("Retrieving from table in different database...");

            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM " + tableName + " LIMIT 10";
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next()){
                String name = rs.getString("name");
                String country = rs.getString("Country");

                System.out.print("Name: " + name);
                System.out.println(", Country: " + country);
            }
            rs.close();
        }
        catch(SQLException se){
            se.printStackTrace();
        }
        finally{
            if(stmt!=null)
                stmt.close();
        }
    }
}

JDBC output is as follows:

Retrieving from table in same database...
Name: Aruba, Region: Caribbean
Name: Afghanistan, Region: Southern and Central Asia
Name: Angola, Region: Central Africa
Name: Anguilla, Region: Caribbean
Name: Albania, Region: Southern Europe
Name: Andorra, Region: Southern Europe
Name: Netherlands Antilles, Region: Caribbean
Name: United Arab Emirates, Region: Middle East
Name: Argentina, Region: South America
Name: Armenia, Region: Middle East



Retrieving from table in different database...
Name: Oranjestad, Country: Aruba
Name: Kabul, Country: Afghanistan
Name: Qandahar, Country: Afghanistan
Name: Herat, Country: Afghanistan
Name: Mazar-e-Sharif, Country: Afghanistan
Name: Luanda, Country: Angola
Name: Huambo, Country: Angola
Name: Lobito, Country: Angola
Name: Benguela, Country: Angola
Name: Namibe, Country: Angola

Best Answer

What MySQL calls a database is in fact a schema. They say as much in their documentation:

CREATE SCHEMA is a synonym for CREATE DATABASE

Schema is a logical grouping of database objects (tables etc.); multiple schemas can be defined in a single MySQL instance and you use the same connection properties (except the "database"/schema name) to access them, so they are not in fact very "foreign" to each other.

In most other DBMSes multiple schemas can be defined in a database; in those you connect to a database and access objects in its schemas. MySQL conflates these two terms.

In the database terminology foreign (or federated) database or table is one that resides in a different DBMS instance.

Related Question