Thông tin_schema truy vấn mysql

Khi viết truy vấn cho một cơ sở dữ liệu mà bạn có thể chưa quen hoặc truy vấn thường xuyên thay đổi, bạn có thể muốn chạy kiểm tra nhanh để tìm tất cả các bảng trong một cơ sở dữ liệu cụ thể hoặc các cột trong cơ sở dữ liệu hoặc để tìm kiếm xem bảng hay cột

Tại sao điều này là một vấn đề?

Hiểu lược đồ và những bảng nào trong đó giúp viết SQL hiệu quả và giúp tránh chạy truy vấn nhiều lần chỉ để xem tên lược đồ hoặc tên cột có đúng không

Hướng dẫn này sẽ giúp giải quyết những vấn đề này

Sử dụng lược đồ thông tin

Truy vấn siêu dữ liệu trên nguồn dữ liệu là cách dễ nhất để xác định cấu trúc của bảng nếu bạn chưa hiểu về nó. Microsoft SQL Server cung cấp dạng xem lược đồ thông tin dưới dạng một trong số các phương pháp để lấy siêu dữ liệu này. Như tài liệu hỗ trợ của họ nêu rõ, “Chế độ xem lược đồ thông tin cung cấp chế độ xem nội bộ, độc lập với bảng hệ thống của siêu dữ liệu SQL Server. Chế độ xem lược đồ thông tin cho phép các ứng dụng hoạt động chính xác mặc dù các thay đổi quan trọng đã được thực hiện đối với các bảng hệ thống cơ bản. ”

Chúng tôi sẽ sử dụng một vài dạng xem trong lược đồ thông tin để chạy các truy vấn giúp xác định cấu tạo của các bảng trong nguồn dữ liệu

Để hiển thị TABLES và COLUMNS trong cơ sở dữ liệu hoặc tìm TABLES và COLUMNS

Truy vấn đầu tiên này sẽ trả về tất cả các bảng trong cơ sở dữ liệu mà bạn đang truy vấn

SELECT
  	TABLE_NAME
FROM
  	INFORMATION_SCHEMA.TABLES

Truy vấn thứ hai sẽ trả về danh sách tất cả các cột và bảng trong cơ sở dữ liệu mà bạn đang truy vấn

SELECT
  	TABLE_NAME,
COLUMN_NAME
FROM
  	INFORMATION_SCHEMA.COLUMNS

Hoặc, bạn cũng có thể truy vấn chỉ COLUMNS từ một bảng cụ thể và trả về tên cột từ bảng 'Album' cụ thể trong cơ sở dữ liệu của chúng tôi

SELECT
	COLUMN_NAME
FROM
  	INFORMATION_SCHEMA.COLUMNS
WHERE
	TABLE_NAME = 'Album'

Với truy vấn tiếp theo này, bạn có thể tìm hiểu xem có hay không có một

SELECT
  	TABLE_NAME,
COLUMN_NAME
FROM
  	INFORMATION_SCHEMA.COLUMNS

0 trong nguồn dữ liệu phù hợp với một số loại tham số tìm kiếm

IF EXISTS(
SELECT
  			*
  		FROM
  			INFORMATION_SCHEMA.TABLES
  		WHERE
  			TABLE_NAME = 'Album'
			)
SELECT 'found' AS search_result ELSE SELECT 'not found' AS search_result;

Truy vấn sẽ trả về từ 'đã tìm thấy' nếu bảng 'Album' tồn tại trong cơ sở dữ liệu của chúng tôi

Bây giờ để đi xa hơn một chút, bạn có thể sử dụng truy vấn này để tìm hiểu xem có hay không có ____1_______1 trong nguồn dữ liệu phù hợp với một số loại tham số tìm kiếm

IF EXISTS(
SELECT
  				*
  			FROM
  				INFORMATION_SCHEMA.COLUMNS
  			WHERE
  				COLUMN_NAME = 'Title'
				)
SELECT 'found' AS search_result ELSE SELECT 'not found' AS search_result;

Sử dụng chế độ xem

SELECT
  	TABLE_NAME,
COLUMN_NAME
FROM
  	INFORMATION_SCHEMA.COLUMNS

2 trong nguồn dữ liệu của bạn có thể là một cách đáng tin cậy để xác định những gì có trong nguồn dữ liệu trong khi bạn xây dựng các truy vấn của mình

Để biết thêm thông tin về Chế độ xem lược đồ thông tin hệ thống Microsoft SQL Server, vui lòng đọc thêm từ tài liệu hỗ trợ của họ

Trong phiên bản 5. 0, MySQL đã triển khai cơ sở dữ liệu INFORMATION_SCHEMA, hiển thị dưới dạng cơ sở dữ liệu bình thường. Mặc dù hành vi và chức năng của nó tương tự như cơ sở dữ liệu thông thường nhưng thông tin chứa trong cơ sở dữ liệu không phải là tất cả dữ liệu thông thường

Đây là cách tốt nhất tôi có thể mô tả cơ sở dữ liệu MySQL INFORMATION_SCHEMA. Nó là một cơ sở dữ liệu chứa thông tin về các cơ sở dữ liệu khác. Nó có sẵn trong mọi phiên bản MySQL và được sử dụng để lưu trữ thông tin siêu dữ liệu về tất cả các cơ sở dữ liệu khác trong máy chủ. Nó còn được gọi là danh mục hệ thống hoặc từ điển dữ liệu

Giống như tất cả các cơ sở dữ liệu MySQL, thông tin trong cơ sở dữ liệu INFORMATION_SCHEMA được lưu trữ trong các bảng chỉ đọc. Tuy nhiên, trên thực tế, chúng là các dạng xem chứ không phải các bảng MySQL cơ sở. Theo nghĩa đó, bạn không thể thực hiện các trình kích hoạt MySQL đối với các bảng được lưu trữ trong cơ sở dữ liệu, cũng như không có bất kỳ tệp nào được liên kết với chúng

GHI CHÚ. Bạn cũng sẽ không tìm thấy thư mục có tên INFORMATION_SCHEMA

Bất chấp tất cả những điều đó, cơ sở dữ liệu INFORMATION_SCHEMA là nơi lý tưởng để truy vấn thông tin về các cơ sở dữ liệu khác được lưu trữ trên máy chủ. Hướng dẫn này nhằm mục đích cung cấp cho bạn cái nhìn tổng quan về cơ sở dữ liệu INFORMATION_SCHEMA và cung cấp cho bạn một vài ví dụ về cách sử dụng cơ sở dữ liệu

Thông tin cơ bản về INFORMATION_SCHEMA

Kể từ MySQL 5. 0 trở lên, nếu bạn thực thi cơ sở dữ liệu hiển thị, cơ sở dữ liệu information_schema đó sẽ hiển thị và có thể được sử dụng như một cơ sở dữ liệu bình thường

mysql > hiển thị cơ sở dữ liệu;

+---------------------+

| Cơ sở dữ liệu           .

+---------------------+

| information_schema .

| mysql              .

| performance_schema .

| sakila             .

| hệ thống                .

| thế giới              .

+---------------------+

6 hàng trong bộ (0.00 giây )

Người ta có thể sử dụng cơ sở dữ liệu INFORMATION_SCHEMA đơn giản bằng cách sử dụng truy vấn sử dụng MySQL như

mysql > SỬ DỤNG information_schema;

Khi ở trong cơ sở dữ liệu, bạn có thể xem các bảng được lưu trữ bằng lệnh dưới dạng

mysql > hiển thị bảng;

+--------------------------------------- +

| Bảng_in_thông tin_lược đồ          .

+--------------------------------------- +

| ADMINISTRABLE_ROLE_AUTHORIZATIONS     .

| ỨNG DỤNG_ROLES                    .

| CHARACTER_SETS                      .

| CHECK_CONSTRAINTS                   .

| COLLATION_CHARACTER_SET_ÁP DỤNG .

| BỘ SƯU TẬP                          .

| COLUMN_PRIVILEGES                   .

| COLUMN_STATISTICS                   .

| CỘT                           .

| COLUMNS_EXTENSIONS                  .

| ENABLED_ROLES                       .

| ĐỘNG CƠ                           .

| SỰ KIỆN                            .

| TỆP                               .

| INNODB_BUFFER_PAGE                  .

| INNODB_BUFFER_PAGE_LRU              .

------------DỮ LIỆU BỊ CẮT----------------

Đầu ra ở trên cho thấy một số bảng được lưu trữ trong cơ sở dữ liệu information_schema. Hãy nhớ rằng chúng không phải là bảng thực tế và do đó không thể sửa đổi. Thay vào đó, chúng được lưu trữ trong bộ nhớ và bị xóa sau khi máy chủ MySQL bị tắt. Khi khởi động lại, information_schema được xây dựng lại và điền dữ liệu hiện có trên máy chủ

Chọn từ Information_schema

Để xem thông tin được lưu trữ trong cơ sở dữ liệu information_schema, bạn có thể sử dụng câu lệnh select. Ví dụ: để liệt kê thông tin trong bảng động cơ, lệnh như

mysql > CHỌN * TỪ information_schema. động cơ;

Điều này sẽ kết xuất dữ liệu được lưu trữ trong bảng như được hiển thị

Ví dụ 1 – Hiển thị các bảng lớn nhất

Ví dụ dưới đây cho thấy cách sử dụng INFORMATION_SCHEMA để hiển thị cơ sở dữ liệu lớn nhất trên máy chủ

Khi thực hiện truy vấn trên, bạn sẽ nhận được tất cả các bảng theo kích thước của mình, bắt đầu từ lớn nhất đến nhỏ nhất

Đây là một ví dụ đầu ra

Ví dụ 2 – Hiển thị đặc quyền

Sử dụng TABLE_PRIVILEGES trong cơ sở dữ liệu lược đồ thông tin, chúng ta có thể nhận được các đặc quyền trên máy chủ MySQL. Một số cột trong bảng này là

• GRANTEE – Phần này hiển thị tài khoản MySQL được cấp đặc quyền. Điều này thường ở định dạng tên người dùng @ tên máy chủ
• TABLE_CATALOG – Chứa tên của danh mục mà bảng thuộc về. Giá trị là def theo mặc định
• TABLE_SCHEMA – Tên của cơ sở dữ liệu chứa bảng
• TABLE_NAME – Tên của bảng
• IS_GRANTEE – Giá trị Boolean nếu người dùng có đặc quyền GRANT. Thường chứa giá trị CÓ hoặc KHÔNG

Sử dụng thông tin trên, chúng ta có thể xem các đặc quyền trong máy chủ MySQL bằng truy vấn

CHỌN * TỪ information_schema. SCHEMA_PRIVILEGES;

Lệnh này sẽ cung cấp cho bạn một đầu ra như được hiển thị

Ví dụ 3 – Hiển thị các tiến trình đang chạy

Chúng ta có thể sử dụng bảng PROCESSLIST có sẵn trong bảng INFORMATION_SCHEMA để xem các hoạt động đang chạy trên máy chủ

Đây là một truy vấn ví dụ để hiển thị tất cả các quy trình đang chạy

CHỌN * TỪ information_schema. DANH SÁCH QUY TRÌNH;

Kết xuất tất cả các quy trình đang chạy sẽ hiển thị một đầu ra tương tự như được hiển thị

Bạn cũng có thể sử dụng lệnh

HIỂN THỊ ĐẦY ĐỦ PROCESSLIST, sẽ hiển thị thông tin tương tự như khi truy vấn bảng PROCESSLIST trong INFORMATION_SCHEMA

Ví dụ 4 – Hiển thị thông tin chỉ mục bảng

Sử dụng bảng THỐNG KÊ, chúng tôi có thể hiển thị thông tin về chỉ mục bảng. Đây là một truy vấn ví dụ

CHỌN * TỪ THÔNG TIN_SCHEMA. THỐNG KÊ WHERE table_schema = 'sakila' GIỚI HẠN 5;

Thông tin về tất cả các chỉ mục trong lược đồ sakila như hình bên dưới

Đó là một số ví dụ mà bạn có thể truy vấn từ cơ sở dữ liệu INFORMATION_SCHEMA

Phần kết luận

Cơ sở dữ liệu INFORMATION_SCHEMA là nguồn thông tin cho tất cả các cơ sở dữ liệu khác và chính máy chủ MySQL. Ngoài ra, nó cung cấp cho bạn rất nhiều tùy chọn để quản lý và điều chỉnh thông tin cần truy vấn. Nếu bạn kết hợp sức mạnh của INFORMATION_SCHEMA và sự kỳ diệu của SQL, bạn sẽ có nhiều sức mạnh nhất đối với bất kỳ người dùng cơ sở dữ liệu nào

Làm cách nào để xem lược đồ thông tin trong MySQL?

INFORMATION_SCHEMA Table_constraint là gì?

Các bảng INFORMATION_SCHEMA trong SQL Server là gì?

Cột INFORMATION_SCHEMA là gì?