MySQL - 数据库信息

MySQL - 获取数据库信息

MySQL 服务器存储两种基本类型的信息：数据本身（你的表内容）和元数据。元数据是“关于数据的数据”——它描述了你的数据库、表、列、索引和服务器配置的结构。

了解如何查询这些元数据是任何开发人员或管理员的一项关键技能。它允许你以编程方式检查数据库 Schema（模式）、监控服务器状态，并理解查询结果。你可以检索三种主要类型的信息：

• 查询结果信息： 例如由 INSERT、UPDATE 或 DELETE 语句影响的行数。
• Schema（模式）信息： 数据库及其对象（表、列、视图等）的结构。
• 服务器信息： 服务器的版本、当前状态和配置变量。

使用 SHOW 命令获取信息

MySQL 提供了一组简单、易读的 SHOW 命令，用于从命令行客户端快速检查服务器。

获取服务器版本

你可以在系统的命令行（登录 MySQL 之前）或在 MySQL 会话中使用 VERSION() 函数检查服务器版本。

# 从你的操作系统终端
C:\> mysql --version
# or
$ mysql -V

这可能会产生类似以下输出：

mysql  Ver 8.0.33 for macos13 on arm64 (MySQL Community Server - GPL)

或者，连接到 MySQL 后：

SELECT VERSION();

列出所有数据库

SHOW DATABASES 命令列出服务器上你拥有查看权限的所有数据库。

SHOW DATABASES;

数据库
information_schema
mysql
performance_schema
sys
TUTORIALS

注意：information_schema、mysql、performance_schema 和 sys 是 MySQL 内部使用的系统数据库。

列出数据库中的表

首先，使用 USE 命令选择一个数据库，然后使用 SHOW TABLES。

USE TUTORIALS;
SHOW TABLES;

TUTORIALS 中的表
CUSTOMERS
EMPLOYEES
PRODUCTS

描述表结构

要查看表的定义，包括其列、数据类型和键，请使用 DESCRIBE 或其缩写 DESC。

DESCRIBE CUSTOMERS;

字段	类型	是否为空	键	默认值	额外
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(50)	NO		NULL
AGE	int	NO		NULL
ADDRESS	varchar(100)	YES		NULL
SALARY	decimal(18,2)	YES		NULL

更标准的方法：INFORMATION_SCHEMA

尽管 SHOW 命令很方便，但它们是 MySQL 特有的。SQL 标准提供了一种更强大、更具可移植性的获取元数据的方式：INFORMATION_SCHEMA。这是一个特殊的只读数据库，包含存储所有其他数据库信息的表。

• 列出数据库： SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA;
• 列出表： SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'TUTORIALS';
• 描述表： SELECT column_name, data_type, is_nullable FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'TUTORIALS' AND table_name = 'CUSTOMERS';

对于需要与数据库无关的工具和应用程序来说，使用 INFORMATION_SCHEMA 被认为是一种最佳实践。

检查查询影响的行数

在运行 INSERT、UPDATE 或 DELETE 查询后，你通常需要知道有多少行被更改。所有现代数据库库都提供了一种直接的方法来获取此计数。

客户端库示例

以下是你在各种语言中获取受影响行数的方法：

/* PHP (mysqli) */
$mysqli->query("UPDATE CUSTOMERS SET SALARY = 7000 WHERE NAME = 'Chaitali'");
$affected_rows = $mysqli->affected_rows;
printf("%d rows were updated.\n", $affected_rows);


/* NodeJS (mysql2/promise) */
const [result] = await connection.execute(
  "UPDATE CUSTOMERS SET SALARY = ? WHERE NAME = ?",
  [7000, 'Chaitali']
);
console.log(`${result.affectedRows} rows were updated.`);


/* Java (JDBC) */
String sql = "UPDATE CUSTOMERS SET SALARY = ? WHERE NAME = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setBigDecimal(1, new BigDecimal("7000.00"));
    pstmt.setString(2, "Chaitali");
    int affectedRows = pstmt.executeUpdate();
    System.out.printf("%d rows were updated.%n", affectedRows);
}


# Python (mysql-connector-python)
update_query = "UPDATE CUSTOMERS SET SALARY = %s WHERE NAME = %s"
cursor.execute(update_query, (7000, 'Chaitali'))
connection.commit() # 不要忘记提交事务
print(f"{cursor.rowcount} rows were updated.")

获取服务器元数据

你可以使用这些命令和函数获取有关服务器状态和配置的高级信息。

命令 / 函数	描述
SELECT VERSION()	返回 MySQL 服务器的完整版本字符串。
SELECT DATABASE()	返回当前默认数据库的名称，如果未选择任何数据库则返回 NULL。
SELECT USER()	返回当前客户端连接的用户名和主机。
SHOW STATUS	提供服务器状态指标的长列表，对性能监控很有用。
SHOW VARIABLES	显示控制服务器配置的 MySQL 系统变量的值。