Làm cách nào để kết nối hai cơ sở dữ liệu trong MySQL?

Sản phẩm SaaS đang đạt được sức hút, người thuê nối tiếp người thuê. Bạn đã có 100 người thuê và bây giờ bạn muốn biết những người thuê này đang sử dụng phần mềm của bạn như thế nào. Họ đã thêm bao nhiêu sản phẩm, bao nhiêu đơn đặt hàng mà người dùng của họ đã đặt, v.v. ? . Hmm, làm gì đây?

Điều gì sẽ xảy ra nếu chúng ta có thể tạo một bảng bằng cách nào đó chứa các bảng của tất cả người thuê nhà? . Sau khi chúng tôi làm tương tự cho mọi bảng khác, chúng tôi có thể truy vấn tất cả các bảng này cùng một lúc, viết THAM GIA, GROUP BY, v.v. Tuy nhiên, làm thế nào chúng ta có thể xây dựng một bảng như vậy, bạn hỏi

Chà, chúng ta có thể xem kho vũ khí mà RDBMS (MySQL) mang lại cho chúng ta. VIEWs là một công cụ như vậy; . Vì vậy, nếu chúng tôi có thể xây dựng một chế độ xem chứa các bảng của tất cả các đối tượng thuê, thì chúng tôi sẽ có một khởi đầu tốt. Tất cả các bảng này sẽ cần phải được nối với nhau, giống như một liên kết của tất cả các bảng

Hơn nữa, đó chính xác là những gì chúng ta sẽ xây dựng, một UNION gồm tất cả các bảng của người thuê được biểu diễn dưới một VIEW. Khi chúng tôi lặp lại quy trình cho mọi bảng, chúng tôi có thể có tất cả cơ sở dữ liệu trong tầm tay

Vì vậy, một XEM như vậy sẽ trông như thế nào

Mỗi VIEW chứa tất cả các cột từ các bảng ban đầu với một cột bổ sung được gọi là “tenant. ” Cột chứa tên DB của đối tượng thuê mà bản ghi được lấy từ đó. Một mặt, bạn có thể dễ dàng biết mình đang xem dữ liệu của ai. Tuy nhiên, mặt khác, bạn cần cẩn thận khi thực hiện THAM GIA, vì bạn cũng phải thêm cột này vào điều kiện THAM GIA

Bây giờ, tất cả những gì còn lại là tạo một VIEW như vậy cho mọi bảng trong cơ sở dữ liệu. Chúng ta có thể sử dụng bất kỳ ngôn ngữ lập trình nào cho nhiệm vụ, nhưng điều thú vị là. Chúng tôi muốn sử dụng các thủ tục được lưu trữ vì

• không phụ thuộc (môi trường thực thi đã có trong RDBMS),
• cập nhật VIEWs đơn giản như chạy lệnh SQL từ ứng dụng khách yêu thích của bạn

Kế hoạch trò chơi rất đơn giản;

Quy trình được lưu trữ để tạo chế độ xem của một bảng

Các thủ tục được lưu trữ có 2 đối số

• tbl_name. tên của bảng mà chúng ta cần tạo VIEW
• db_potype_re. biểu thức chính quy để lọc ra các cơ sở dữ liệu sẽ được đưa vào XEM

A) Đầu tiên, chúng ta cần khai báo một con trỏ sẽ được sử dụng để duyệt qua tất cả các DB có trong VIEW. Để chỉ lọc ra các DB mong muốn, chúng tôi sử dụng biểu thức chính quy db_potype_re. TIẾP TỤC XỬ LÝ sẽ đảm bảo rằng vòng lặp dừng sau khi lặp qua tất cả các DB được tìm thấy

B) Khởi tạo biến, all_dbs_view, sẽ chứa câu lệnh CREATE VIEW đầy đủ của chúng ta. Biến có thể khá dài (LONGTEXT), tùy thuộc vào số lượng DB được lọc ra

C) Sau đó, chúng tôi mở con trỏ và bắt đầu lặp lại từng DB đã lọc. Câu lệnh IF sẽ kiểm tra biến all_dbs_done trong từng bước. Biến sẽ được đặt thành 1 khi CONTINUE Handler được kích hoạt

D) Lần lặp đầu tiên không cần thêm UNION ALL vào trước, vì vậy chúng tôi bỏ qua nó; . Dòng tiếp theo là bắn tiền;

SELECT "tenants_DB" AS tenant, t.* FROM tenants_DB.some_table AS t

E) Trước khi tạo CHẾ ĐỘ XEM, chúng tôi phải đảm bảo rằng chúng tôi xóa một cái hiện có nếu nó tồn tại

F) Bây giờ, chúng ta thực sự chạy câu lệnh sẽ tạo VIEW

Chúng ta có thể thực hiện quy trình tạo VIEW của một bảng

CALL update_table_view("sample_table", "tenant_[0-9]+");

Thủ tục được lưu trữ để lặp qua tất cả các bảng

Thủ tục được lưu trữ có 2 đối số

• db_first. tên của cơ sở dữ liệu sẽ được sử dụng để liệt kê tất cả các bảng;
• db_potype_re. biểu thức chính quy để lọc ra các cơ sở dữ liệu sẽ được đưa vào XEM

A) Chúng tôi khai báo một con trỏ được sử dụng để lặp qua tất cả các bảng được tìm thấy trong cơ sở dữ liệu db_first. TIẾP TỤC XỬ LÝ sẽ đảm bảo rằng vòng lặp dừng sau khi lặp qua tất cả các bảng được tìm thấy

B) Chúng tôi mở con trỏ và bắt đầu lặp qua từng bảng tìm thấy. Câu lệnh IF sẽ kiểm tra biến all_tbls_done trong từng bước. Biến sẽ được đặt thành 1 khi CONTINUE Handler được kích hoạt

C) Khi chúng ta có giá trị của bảng hiện tại được lưu trữ trong biến cur_tbl, chúng ta có thể thực thi thủ tục được lưu trữ để tạo VIEW cụ thể cho cur_tbl

Khi chúng ta thực hiện thủ tục lưu sẵn

CALL update_all_views("tenant_1", "tenant_[0-9]+");

chúng tôi nhận được

• danh sách tất cả các CHẾ ĐỘ XEM đại diện cho từng bảng từ đối tượng thuê cơ sở dữ liệu_1;
• mỗi VIEW về cơ bản là UNION của cùng một bảng trên tất cả các đối tượng thuê

đó là nó

những ưu điểm

lợi ích của việc sử dụng phương pháp này là gì

• không phụ thuộc (tất cả được chạy trong máy chủ MySQL)
• có thể được sử dụng từ bất kỳ máy khách MySQL nào
• biết và mô hình dữ liệu quen thuộc (về cơ bản giống nhau)
• dễ cài đặt, dễ cập nhật, dễ sử dụng, dễ hiểu
• có thể chạy bất kỳ truy vấn tùy ý nào

khuyết điểm

Hơn nữa, những bất lợi là gì

• khi nối các bảng phải thêm điều kiện phụ
• truy vấn không quá nhanh (tùy thuộc vào số lượng người thuê) nhưng đủ nhanh
• không mở rộng quá 100 người thuê

Phần kết luận

Trong thiết lập hiện tại của chúng tôi, chúng tôi có cca. 340 bảng và cca. 250 khách thuê. Chúng tôi chưa thực hiện bất kỳ thử nghiệm nào để xem quy mô thiết lập này có thể mở rộng đến đâu. Hiện tại, các truy vấn đều phản hồi nhanh và khá thú vị khi sử dụng nó. Chúng tôi đã phát hiện ra nhiều điều thú vị, chỉ bằng cách chạy các truy vấn đơn giản này

Mặc dù sẽ không thực tế nếu sử dụng thiết lập này cho hàng nghìn hoặc hàng triệu người thuê, nhưng nó vẫn có thể hữu ích đối với một nhóm nhỏ người thuê của bạn e. g. , người thuê nhà từ 34 đến 76

CALL update_all_views("tenant_34", "tenant_(3[4-9]|[4-6][0-9]|7[0-6])$");

Vì vậy, tóm lại, đây là một thiết lập rất thuận tiện để chạy phân tích đặc biệt về tất cả những người thuê nhà của bạn. Khi tất cả những gì bạn cần là chỉ chạy một số truy vấn đơn giản;

những lưu ý

Giống như mọi giải pháp phần mềm, có những lưu ý nhỏ mà chúng tôi chưa đề cập ở trên

1) Khi thực thi lệnh SQL thực tế để tạo VIEW, máy chủ MySQL có thể đưa ra một ngoại lệ như thế này

Prepared statement needs to be re-prepared

Cách giải quyết của chúng tôi là thế này

SET GLOBAL table_definition_cache = 2800;

Con số thực tế của bạn (2800) có thể sẽ hơi khác một chút, tùy thuộc vào số lượng người thuê nhà của bạn

2) Khi thực hiện truy vấn với điều kiện đối với một đối tượng thuê cụ thể

SELECT * FROM sample_table WHERE tenant = "tenant_1"

Bạn có thể gặp ngoại lệ (tùy thuộc vào cách bạn tạo cơ sở dữ liệu)

Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘=’

Giải pháp là buộc mã hóa (đối chiếu) tên cơ sở dữ liệu trong khi tạo VIEW. Thêm lệnh gọi hàm CONVERT trong câu lệnh SELECT của bạn

________số 8

Thay vì utf8mb4,, hãy sử dụng bất cứ thứ gì bạn đặt để mã hóa khi tạo cơ sở dữ liệu

3) Thời gian chờ kết nối cũng có thể là một vấn đề. Việc tạo tất cả các CHẾ ĐỘ XEM này có thể mất một chút thời gian; . Vì vậy, hãy đảm bảo bạn đặt đủ thời gian chờ cho các kết nối trong máy khách của mình

4) Tốt nhất là bạn nên chuẩn bị VIEWs và chạy các truy vấn trên máy chủ bản sao để không tạo thêm gánh nặng cho máy chủ sản xuất của mình. Truy vấn có thể trở nên nặng nề khá nhanh

5) Để thử nghiệm phương pháp trên, chúng tôi đã sử dụng MySQL Community Server 5. 7. 20

Tín dụng hình ảnh. Ảnh của Kolar. io trên Bapt

Kiểm tra dự án mới nhất của tôi WEBACUS. nhà phát triển

Máy tính còn thiếu dành cho nhà phát triển bao gồm nhiều thao tác đa dạng mà nhà phát triển cần trong công việc hàng ngày của họ. Thay vì tìm kiếm trên Google và tìm hiểu giao diện cho từng thao tác, Webacus cung cấp giao diện nhất quán và đơn giản cho phép quy trình làm việc trôi chảy

Trọng tâm chính của máy tính là KHẢ NĂNG TƯƠNG TÁC — có thể xâu chuỗi tất cả các hoạt động thành một luồng và tạo ra kết quả. Không còn SAO CHÉP & DÁN giữa các thao tác khi làm việc với máy tính

Chúng ta có thể sử dụng hai cơ sở dữ liệu trong MySQL không?

Làm cách nào để kết nối hai cơ sở dữ liệu trong MySQL Workbench?

Bạn có thể tham gia 2 bảng từ các cơ sở dữ liệu khác nhau không?