如何为两个网站实现共享用户表并完成跨数据库关联查询？

Great question—unifying user accounts across multiple databases is a common scenario, and your initial idea of a central DB3 is a solid starting point. Let’s break down how to implement this, plus cover alternatives and the cross-database queries you need.

Recommended Approach: Centralized User Database (DB3) #

This is the most straightforward solution for creating a single source of truth for users, enabling cross-site login and easy cross-database joins.

1. Design the Unified Users Table in DB3 #

First, create a users table in DB3 that combines all necessary fields from DB1.users and DB2.users. Add a source_db field to track where each user originated (useful for debugging legacy data):

CREATE TABLE DB3.users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL, -- Use email as the unique identifier for cross-site matching
    password_hash VARCHAR(255) NOT NULL,
    username VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    source_db ENUM('DB1', 'DB2', 'DB3') NOT NULL -- Track original database
);

2. Migrate Existing Users to DB3 #

Write scripts to copy users from DB1 and DB2 to DB3, handling duplicates (e.g., users who exist in both databases):

-- Migrate from DB1 (keep existing entries if duplicates are found)
INSERT INTO DB3.users (email, password_hash, username, source_db)
SELECT email, password_hash, username, 'DB1' FROM DB1.users
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- Migrate from DB2
INSERT INTO DB3.users (email, password_hash, username, source_db)
SELECT email, password_hash, username, 'DB2' FROM DB2.users
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

For duplicate users, you can choose to merge accounts (e.g., keep the latest profile data) or flag them for manual review later.

3. Update Website Authentication Logic #

Modify both websites to use DB3 as the single source of truth:

• When a user registers on either site, write their data directly to DB3.users (stop writing to DB1.users or DB2.users).
• For login, validate credentials against DB3.users instead of local tables.
• Optional: Keep DB1.users and DB2.users as read-only copies (via replication) if legacy features require them, but never write to them directly.

4. Implement Cross-Database Joins #

The syntax for cross-database queries varies by database system—here are examples for the most common ones:

MySQL/MariaDB

Use database_name.table_name to reference tables across databases (ensure your application user has access to all three databases):

-- For DB2 website: Join DB3 users with DB2 prices
SELECT u.*, p.*
FROM DB3.users u
INNER JOIN DB2.prices p ON u.id = p.user_id -- Critical: Always include a join condition
WHERE p.price > 0;

-- For DB1 website: Join DB3 users with DB1 prices
SELECT u.*, p.*
FROM DB3.users u
INNER JOIN DB1.prices p ON u.id = p.user_id
WHERE p.price > 0;

PostgreSQL

Use a Foreign Data Wrapper (FDW) to connect DB1/DB2 to DB3, then query the linked table:

-- Set up FDW in DB2 to access DB3
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER db3_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db3-host', dbname 'DB3');
CREATE USER MAPPING FOR current_user SERVER db3_server OPTIONS (user 'db3-user', password 'db3-pass');
CREATE FOREIGN TABLE users_db3 (LIKE DB3.users) SERVER db3_server OPTIONS (schema_name 'public', table_name 'users');

-- Now run your join
SELECT u.*, p.*
FROM users_db3 u
INNER JOIN prices p ON u.id = p.user_id
WHERE p.price > 0;

SQL Server

Use database_name.schema_name.table_name syntax (ensure cross-database permissions are granted):

SELECT u.*, p.*
FROM DB3.dbo.users u
INNER JOIN DB2.dbo.prices p ON u.id = p.user_id
WHERE p.price > 0;

Alternative Approaches #

If a centralized DB3 isn’t feasible for your setup, consider these options:

• Federated Tables (MySQL Only): Create federated tables in DB1 and DB2 that point to DB3.users. This lets you query local users tables that mirror DB3 data, but federated tables have limitations (no transaction support, limited indexing).
• Bidirectional Sync: Use tools like MySQL Replication, PostgreSQL Logical Replication, or Debezium to sync user data between DB1, DB2, and DB3. This adds complexity but keeps local copies up to date.
• Single Sign-On (SSO): Implement an OAuth2/OpenID Connect service where both websites delegate authentication to a central system. This is ideal for scaling but requires more setup than a centralized database.

Key Considerations #

• Security: Grant minimal permissions to application users accessing cross-database tables. Always encrypt sensitive data like password hashes.
• Performance: Index the user_id field in DB1.prices and DB2.prices, and the id field in DB3.users to speed up joins.
• Data Consistency: Ensure all user updates (password changes, profile edits) are only made to DB3 to avoid conflicts.
• Testing: Validate migrations and authentication changes in a staging environment before deploying to production.

内容的提问来源于stack exchange，提问作者Toleo