MySQL系统信息函数

系统信息函数是MySQL中用于获取数据库系统信息的函数。这些函数可以帮助我们了解数据库的版本、连接状态、当前用户、数据库名等系统信息，对于数据库管理和监控非常有用。本章将详细介绍MySQL中的系统信息函数及其用法。

获取MySQL版本号的函数

MySQL提供了多个函数用于获取数据库服务器的版本信息：

函数	描述	示例
VERSION()	返回MySQL服务器的版本号字符串	VERSION() = '8.0.30'
@@VERSION	系统变量，返回MySQL服务器的版本号字符串	@@VERSION = '8.0.30'
MYSQL_VERSION_ID()	返回MySQL版本号的数值形式	MYSQL_VERSION_ID() = 80030
@@MYSQL_VERSION_ID	系统变量，返回MySQL版本号的数值形式	@@MYSQL_VERSION_ID = 80030

获取MySQL版本号示例

-- 获取MySQL版本号
SELECT VERSION();

-- 获取MySQL版本号的数值形式
SELECT MYSQL_VERSION_ID();

-- 检查MySQL版本是否满足特定要求
SELECT CASE
    WHEN MYSQL_VERSION_ID() >= 80000 THEN 'MySQL 8.0 或更高版本'
    WHEN MYSQL_VERSION_ID() >= 50700 THEN 'MySQL 5.7 版本'
    WHEN MYSQL_VERSION_ID() >= 50600 THEN 'MySQL 5.6 版本'
    ELSE '较早版本'
END AS version_info;

-- 将版本号分解为主版本号、次版本号和修订版本号
SELECT
    SUBSTRING_INDEX(VERSION(), '.', 1) AS major_version,
    SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '.', 2), '.', -1) AS minor_version,
    SUBSTRING_INDEX(VERSION(), '.', -1) AS patch_version;

获取连接数和数据库状态的函数

用于获取数据库连接数和服务器状态信息的函数：

函数/变量	描述	示例
CONNECTION_ID()	返回当前连接的连接ID	CONNECTION_ID() = 12345
@@MAX_CONNECTIONS	系统变量，返回MySQL服务器允许的最大连接数	@@MAX_CONNECTIONS = 151
@@MAX_USER_CONNECTIONS	系统变量，返回当前用户允许的最大连接数	@@MAX_USER_CONNECTIONS = 0 (表示无限制)
SHOW STATUS LIKE 'Threads_connected'	显示当前打开的连接数	Threads_connected = 10

获取连接数示例

-- 获取当前连接的连接ID
SELECT CONNECTION_ID();

-- 获取MySQL服务器允许的最大连接数
SELECT @@MAX_CONNECTIONS;

-- 查看当前的连接状态
SHOW STATUS LIKE 'Threads_%';

-- 查看服务器的连接统计信息
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW GLOBAL STATUS LIKE 'Aborted_clients';

-- 监控连接使用情况
SELECT
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected') AS current_connections,
    @@MAX_CONNECTIONS AS max_connections,
    CONCAT(ROUND((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected') / @@MAX_CONNECTIONS * 100, 2), '%') AS connection_usage;

获取数据库名的函数

用于获取当前数据库名或列出所有数据库的函数：

函数/语句	描述	示例
DATABASE()	返回当前使用的数据库名	DATABASE() = 'test_db'
SCHEMA()	DATABASE()的同义词	SCHEMA() = 'test_db'
@@DATABASE	系统变量，返回当前使用的数据库名	@@DATABASE = 'test_db'
SHOW DATABASES	列出所有可用的数据库	-

获取数据库名示例

-- 获取当前使用的数据库名
SELECT DATABASE();
SELECT SCHEMA();

-- 切换到指定数据库
USE test_db;

-- 列出所有数据库
SHOW DATABASES;

-- 列出当前用户有权限访问的数据库
SHOW DATABASES WHERE `Database` NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

获取用户名的函数

用于获取当前登录用户信息的函数：

函数	描述	示例
USER()	返回当前MySQL用户名和主机名	USER() = 'root@localhost'
CURRENT_USER()	返回当前MySQL用户名和主机名（基于权限）	CURRENT_USER() = 'root@localhost'
SESSION_USER()	USER()的同义词	SESSION_USER() = 'root@localhost'
SYSTEM_USER()	USER()的同义词	SYSTEM_USER() = 'root@localhost'

获取用户名示例

-- 获取当前MySQL用户名和主机名
SELECT USER();
SELECT CURRENT_USER();

-- 分解用户名和主机名
SELECT
    SUBSTRING_INDEX(USER(), '@', 1) AS username,
    SUBSTRING_INDEX(USER(), '@', -1) AS hostname;

-- 查看当前用户的权限
SHOW GRANTS;

-- 列出所有MySQL用户
SELECT User, Host FROM mysql.user;

获取字符串的字符集和排序方式的函数

用于获取字符串或系统字符集和排序规则信息的函数：

函数/变量	描述	示例
CHARSET(str)	返回字符串str的字符集	CHARSET('hello') = 'utf8mb4'
COLLATION(str)	返回字符串str的排序规则	COLLATION('hello') = 'utf8mb4_0900_ai_ci'
@@character_set_client	系统变量，返回客户端使用的字符集	@@character_set_client = 'utf8mb4'
@@character_set_connection	系统变量，返回当前连接的字符集	@@character_set_connection = 'utf8mb4'
@@character_set_database	系统变量，返回当前数据库的字符集	@@character_set_database = 'utf8mb4'
@@character_set_results	系统变量，返回查询结果的字符集	@@character_set_results = 'utf8mb4'
@@character_set_server	系统变量，返回服务器的默认字符集	@@character_set_server = 'utf8mb4'
@@collation_connection	系统变量，返回当前连接的排序规则	@@collation_connection = 'utf8mb4_0900_ai_ci'
@@collation_database	系统变量，返回当前数据库的排序规则	@@collation_database = 'utf8mb4_0900_ai_ci'
@@collation_server	系统变量，返回服务器的默认排序规则	@@collation_server = 'utf8mb4_0900_ai_ci'

获取字符集和排序方式示例

-- 获取字符串的字符集和排序规则
SELECT CHARSET('hello'), COLLATION('hello');

-- 查看当前会话的字符集设置
SELECT
    @@character_set_client AS client,
    @@character_set_connection AS connection,
    @@character_set_results AS results,
    @@character_set_server AS server,
    @@collation_connection AS collation;

-- 查看数据库的字符集设置
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = DATABASE();

-- 查看表的字符集设置
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

-- 查看列的字符集设置
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'your_table';

获取最后一个自动生成的ID值的函数

用于获取最后插入操作生成的自增ID值的函数：

函数/变量	描述	示例
LAST_INSERT_ID()	返回最后插入的记录的自增ID值	LAST_INSERT_ID() = 1001
@@IDENTITY	系统变量，返回最后插入的记录的自增ID值	@@IDENTITY = 1001

获取最后一个自动生成的ID值示例

-- 插入记录并获取自增ID
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
SELECT LAST_INSERT_ID();

-- 在事务中获取多个插入操作的ID
START TRANSACTION;
INSERT INTO users (username, email) VALUES ('mary', 'mary@example.com');
SELECT LAST_INSERT_ID() INTO @user1_id;
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');
SELECT LAST_INSERT_ID() INTO @user2_id;
COMMIT;
SELECT @user1_id, @user2_id;

-- 使用LAST_INSERT_ID()进行关联插入
INSERT INTO orders (customer_id, order_date)
VALUES (LAST_INSERT_ID(), NOW());

-- 在存储过程中使用LAST_INSERT_ID()
DELIMITER //
CREATE PROCEDURE insert_user_and_get_id(IN p_username VARCHAR(50), IN p_email VARCHAR(100), OUT p_user_id INT)
BEGIN
    INSERT INTO users (username, email) VALUES (p_username, p_email);
    SET p_user_id = LAST_INSERT_ID();
END //
DELIMITER ;

CALL insert_user_and_get_id('alice', 'alice@example.com', @new_user_id);
SELECT @new_user_id;

其他系统信息函数

MySQL还提供了许多其他系统信息函数：

函数/变量	描述	示例
GET_LOCK(str, timeout)	获取命名锁	GET_LOCK('my_lock', 10) = 1 (成功)
RELEASE_LOCK(str)	释放命名锁	RELEASE_LOCK('my_lock') = 1 (成功)
IS_FREE_LOCK(str)	检查命名锁是否可用	IS_FREE_LOCK('my_lock') = 1 (可用)
CONVERT_TZ(dt, from_tz, to_tz)	将日期时间从一个时区转换到另一个时区	CONVERT_TZ('2023-10-15 12:00:00', '+00:00', '+08:00') = '2023-10-15 20:00:00'
@@time_zone	系统变量，返回当前时区设置	@@time_zone = '+08:00'
@@hostname	系统变量，返回MySQL服务器的主机名	@@hostname = 'mysql-server'
@@port	系统变量，返回MySQL服务器的端口号	@@port = 3306
@@socket	系统变量，返回MySQL服务器的socket文件路径	@@socket = '/tmp/mysql.sock'
@@basedir	系统变量，返回MySQL安装目录的路径	@@basedir = '/usr/local/mysql'
@@datadir	系统变量，返回MySQL数据目录的路径	@@datadir = '/var/lib/mysql/'
@@tmpdir	系统变量，返回MySQL临时文件目录的路径	@@tmpdir = '/tmp'

其他系统信息函数示例

-- 使用命名锁实现简单的并发控制
IF GET_LOCK('my_resource', 5) = 1 THEN
    -- 执行需要并发控制的操作
    DO SLEEP(1);
    -- 释放锁
    SELECT RELEASE_LOCK('my_resource');
ELSE
    SELECT '无法获取锁，请稍后再试';
END IF;

-- 查看MySQL服务器的时区设置
SELECT @@time_zone;

-- 设置会话时区
SET time_zone = '+08:00';

-- 查看MySQL服务器的配置信息
SELECT 
    @@hostname AS hostname,
    @@port AS port,
    @@version AS version,
    @@basedir AS basedir,
    @@datadir AS datadir,
    @@tmpdir AS tmpdir;

-- 查看当前会话的字符集和校对规则设置
SHOW VARIABLES LIKE 'character\_set\_%';
SHOW VARIABLES LIKE 'collation\_%';

-- 查看InnoDB存储引擎的状态信息
SHOW ENGINE INNODB STATUS;

提示：系统信息函数在数据库管理、监控和调试中非常有用。特别是在编写存储过程、触发器或复杂查询时，这些函数可以帮助我们获取当前环境的各种信息，以便做出相应的决策。例如，使用LAST_INSERT_ID()可以获取刚插入的记录ID，用于后续的关联操作；使用USER()可以根据当前用户执行不同的逻辑等。

上一章：条件判断函数下一章：MySQL 8.0的新特性1——加密函数