In PostgreSQL databases, the general method for cross-database queries is to first establish a dblink on the corresponding database, and then define query parameters during the query to retrieve data. In MySQL databases, cross-database queries are simpler; for queries across databases on the same server, you only need to include the database name during the join. The SQL syntax is as follows:
select * from db1.tb1 t1 join db2.tb2 t2 on t1.a1= t2.a2;The MySQL mode of the Halo database (hereinafter referred to as Halo-MySQL) also supports this function compatible with MySQL.
Requirement: Table t0 in the database halo_mysql_test_db needs to be associated with t1 in halo_mysql_test_db1 to query the required data.
# Database Parameters for Halo-MySQL DDL Statements
postgresql_host = "127.0.0.1"
postgresql_port = 19233
postgresql_user = "halo_mysql_test_user1"
postgresql_db = "halo_mysql_test_db"# Halo-MySQL Parameters
halo_mysql_host = "127.0.0.1"
halo_mysql_port = 3307
halo_mysql_user = "halo_mysql_test_user1"
halo_mysql_password = "1234wjj"
halo_mysql_db = "halo_mysql_test_db"Execute on the table creation database:
CREATE SCHEMA halo_mysql_test_db;
CREATE SCHEMA halo_mysql_test_db1;
SET SEARCH_PATH TO halo_mysql_test_db1, "$user", public;
CREATE TABLE halo_mysql_test_db1.t1 (
id int,
ident_no VARCHAR(32) NOT NULL,
name VARCHAR(64) NOT NULL,
age INT NOT NULL,
tel_no CHAR(32),
description VARCHAR(256)
);Execute on the Halo-MySQL database:
INSERT INTO halo_mysql_test_db1.t1 VALUES(1, '202201010001', 'zhangsan1', 18, '15812345678', '');
INSERT INTO halo_mysql_test_db1.t1(id, ident_no, name, age, tel_no, description) VALUES(2, '202201010002', 'zhangsan2', 18, '15812345678', '');
INSERT INTO halo_mysql_test_db1.t1 VALUES(3, '202201010003', 'zhangsan3', 18, '15812345678', '');
INSERT INTO halo_mysql_test_db1.t1(id, ident_no, name, age, tel_no, description) VALUES(4, '202201010004', 'zhangsan4', 18, '15812345678', '');
INSERT INTO halo_mysql_test_db1.t1(id, ident_no, name, age, tel_no, description) VALUES(5, '202201010004', 'zhangsan4', 18, '15812345678', '');
INSERT INTO halo_mysql_test_db1.t1(id, ident_no, name, age, tel_no, description) VALUES(6, '202201010004', 'zhangsan4', 18, '15812345678', '');
INSERT INTO halo_mysql_test_db1.t1(id, ident_no, name, age, tel_no, description) VALUES(9, '202201010004', 'zhangsan4', 19, '15812345678', '');
SELECT count(*) as c FROM halo_mysql_test_db1.t1;Execute on the table creation database:
SET SEARCH_PATH TO halo_mysql_test_db, "$user", public;
CREATE TABLE halo_mysql_test_db.t0 (
id int,
ident_no VARCHAR(32) NOT NULL,
name VARCHAR(64) NOT NULL,
age INT NOT NULL,
tel_no CHAR(32),
description VARCHAR(256)
);Execute on the Halo-MySQL database:
INSERT INTO t0 VALUES(1, '202201010001', 'zhangsan1', 18, '15812345678', '');
INSERT INTO t0(id, ident_no, name, age, tel_no, description) VALUES(2, '202201010002', 'zhangsan2', 18, '15812345678', '');
INSERT INTO halo_mysql_test_db.t0 VALUES(3, '202201010003', 'zhangsan3', 18, '15812345678', '');
INSERT INTO halo_mysql_test_db.t0(id, ident_no, name, age, tel_no, description) VALUES(4, '202201010004', 'zhangsan4', 18, '15812345678', '');
INSERT INTO t0(id, ident_no, name, age, tel_no, description) VALUES(5, '202201010004', 'zhangsan4', 18, '15812345678', '');
INSERT INTO halo_mysql_test_db.t0(id, ident_no, name, age, tel_no, description) VALUES(6, '202201010004', 'zhangsan4', 18, '15812345678', '');
SELECT count(*) as c FROM t0;
SELECT count(*) as c FROM halo_mysql_test_db.t0;
select * from t0;
select * from t1;
select * from halo_mysql_test_db1.t1;
select count(*) as c FROM halo_mysql_test_db1.t1;
select * from t0 join halo_mysql_test_db1.t1 t1 where t0.id =t1.id;When there is existing business data and cross-database queries are required, when importing the existing business data into Halo-MySQL, you need to create a schema with the same name as the original MySQL database for each database under the original MySQL on Halo-MySQL. Then, create tables under the newly created schema in Halo-MySQL for each table under the original MySQL databases.
That concludes all the content regarding cross-database queries in Halo-MySQL. Thank you for reading.