19、MySQL与phpMyAdmin功能详解

MySQL与phpMyAdmin功能详解

1. Information_schema

在SQL:2003标准中，通过名为 information_schema 的结构来访问数据字典（即数据库元数据）。由于这是标准的一部分，且在其他数据库系统中已存在，因此将此功能集成到MySQL是个明智之举。

MySQL还添加了一些非标准信息，例如 INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE 。若计划在软件项目中使用这些信息，需确保其能在其他SQL实现中保持可移植性。

对于phpMyAdmin用户而言， information_schema 就像一个普通数据库，其中包含描述该服务器上托管数据库结构各方面的视图。实际上，对这个数据库唯一可行的操作就是 SELECT 。

在内部，phpMyAdmin在某些操作中会调用 information_schema 来检索元数据，而非使用相应的 SHOW 语句。不过，phpMyAdmin开发者发现，当服务器托管数百个数据库或表时，涉及 information_schema 的 WHERE 子句的一些 SELECT 操作会非常缓慢（可能需要等待数分钟）。因此，在当前的MySQL环境下，在编程项目中使用此功能时需谨慎。

2. MySQL 5.0和5.1支持

• 隐藏数据库 ： $cfg['Servers'][$i]['hide_db'] 参数可用于对那些可能会被突然出现的陌生数据库搞糊涂的用户隐藏该“数据库”，这可能取决于用户对MySQL的熟练程度。在多用户的phpMyAdmin安装中，很难让所有人都对该参数的值满意。
• 分析功能 ：MySQL 5.0.37和5.1.28版本添加了分析支持。在查询结果中会出现“Profiling”复选框，勾选该框后，phpMyAdmin会分析每个查询（包括当前查询），并显示每个MySQL内部操作的执行时间报告。不过，目前phpMyAdmin仅显示操作名称及其持续时间，尽管分析系统可以报告更多操作信息，如CPU时间和服务器内部函数名。

3. 分区功能

MySQL 5.1提供了用户定义的分区功能（详情可参考 此处 ）。在phpMyAdmin中使用此功能需要了解其语法，因为分区类型众多。而且，对于每种分区类型，分区数量和每个分区关联的值过于随机，难以在Web界面上轻松呈现。

表创建示例 ：我们尝试创建一个名为 test 的单字段表。在连接到MySQL 5.1服务器时，在表创建面板中，phpMyAdmin会显示一个 PARTITION 定义对话框。我们可以输入 PARTITION BY RANGE 子句，以在 id 列上创建分区。示例代码如下：

CREATE TABLE `test` (
  `id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
/*!50100 PARTITION BY RANGE (id) 
(PARTITION p0 VALUES LESS THAN (1000) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = MyISAM, 
 PARTITION p2 VALUES LESS THAN (3000) ENGINE = MyISAM) */;

分区表操作 ：在已定义分区的表上，“Operations”子页面会显示一个“Partition maintenance”对话框，我们可以在此进行以下操作：
- 选择一个分区并执行诸如重建之类的操作。
- 移除所有分区。

导出分区表 ：以SQL模式导出此测试表时，会生成带有嵌入式注释的语句，MySQL 5.1服务器可以识别并解释这些注释以重新创建相同的分区。

4. 事件调度器

MySQL 5.1的另一个新特性是事件调度器（详情可参考 此处 ），它允许根据时间表自动创建任务。调度非常灵活，例如可以从2011年5月18日午夜开始，每十秒执行一次语句。事件可以是一次性的，也可以是重复的。

激活调度器 ：
1. 首先在查询框中输入 SHOW VARIABLES LIKE 'event%'; 。
2. 查找名为 event_scheduler 的变量，如果该变量设置为 OFF ，则需要请求系统管理员（或具有 SUPER 权限的用户）执行 SET GLOBAL event_scheduler = ON; 语句。

授予事件权限 ：每个想要创建或删除事件的用户都需要 EVENT 权限，可以是全局权限，也可以是在计划添加事件的数据库上的权限。

创建事件 ：当前的3.1.x版本的phpMyAdmin没有用于选择 CREATE EVENT 语句各部分的Web界面，因此只能使用SQL查询框输入语句并了解其语法。以下是一个虚构的示例：

CREATE EVENT add_page_count
    ON SCHEDULE 
      EVERY 1 MINUTE
    DO 
      UPDATE author set total_page_count = total_page_count + 1 WHERE 
id = 1;

我们可以偶尔浏览 author 表，观察作者1的计数器递增。

事件操作 ：事件与一个数据库相关，因此在 marc_book 数据库的“Structure”页面上会看到一个“Events”滑块。激活它会显示相应面板，其中可以使用第一个图标编辑事件（相当于删除并重新创建事件），使用“X”图标移除事件。

导出事件 ：在SQL数据库导出的末尾，可以通过勾选“Add CREATE PROCEDURE / FUNCTION / EVENT”选项来生成与事件相关的语句。需要注意的是，有些事件可能有结束时间，因此在创建和尝试导出之间可能已经消失。

5. BLOB流式传输

从3.1.0版本开始，phpMyAdmin支持MySQL的可扩展BLOB流式传输基础设施。相关项目目标可参考 http://blobstreaming.org 网站。不过，其部分文档涉及命令行实用程序，在phpMyAdmin这样的Web界面中用处不大。在进一步阅读之前，需要熟悉BLOB流式传输术语，如存储库和字段引用。

系统要求 ：phpMyAdmin中选择的实现方法依赖于以下组件：
- MySQL 5.1
- PBXT（PrimeBase XT存储引擎）
- PBMS（PrimeBase Media Streaming）

PBXT是支持流式传输的存储引擎。虽然通过PBMS UDFs也支持其他非流式传输的存储引擎，但此机制在phpMyAdmin中未实现。可以通过点击phpMyAdmin主页上的“Engines”链接，查找以下行来验证所需组件是否存在：
- PBXT / High performance, multi - versioning transactional engine
- PBMS / The Media Stream engine for MySQL

若这些行不存在，需按照 http://www.primebase.org 上的说明安装PBXT，或按照BLOB Streaming网站上的说明安装PBMS。

配置 ：一些PBMS系统变量会影响BLOB流式传输行为，可以在 config.inc.php 中定义。推荐在服务器定义中设置以下值：

$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';

实现限制 ：
- 在当前的3.1.1版本的phpMyAdmin中，使用BLOB流式传输存在一些限制。首先，刚启动的MySQL服务器上，全局变量 pbms_field_references 可能设置为 off ，由于字段引用在BLOB引用中广泛使用，phpMyAdmin会尝试将该参数设置为 on ，但这只能由具有 SUPER 权限的用户完成。因此，此类用户必须通过phpMyAdmin登录一次来设置该参数，并且每次重启MySQL服务器都需要重复此操作。
- 数据上传到BLOB存储库的机制也有限制。数据需要先从工作站传输到Web服务器，再从Web服务器传输到MySQL服务器，第一次传输通过普通的HTTP上传完成。为避免受到此限制的影响，可以使用Web服务器上传目录。此外，在 config.inc.php 中， $cfg['Servers'][$i]['host'] 中的服务器名称应是完全限定域名（FQDN），不能是“localhost”，否则用于检索流式数据的链接将无法工作。

创建PBMS系统表 ：若满足所有要求，可进入 marc_book 数据库的“Operations”子页面，会看到一个“BLOB Repository”对话框、“Status: Disabled”消息和一个“Enable”按钮。点击“Enable”可在该数据库中创建PBMS系统表，PBMS需要两个系统表（ pbms_repository 和 pbms_reference ），phpMyAdmin内部还需要一个表（ pbms_custom_content_type ）。创建后，该页面会显示“Status: Enabled”，但新创建的系统表通常在phpMyAdmin中不可见，因为用户不应直接操作它们。点击“Disable”按钮会显示警告，确认后PBMS会忘记该数据库中的所有BLOB引用，最终删除之前从该数据库引用的所有BLOB流式数据（如果没有其他数据库引用这些数据）。

表准备 ：以 book.cover_photo 列为例，使用 LONGBLOB 来保存BLOB引用（注意流数据本身位于BLOB存储库中， LONGBLOB 列仅用于包含引用）。确保 book 表使用PBXT存储引擎，如果不是，可以在该表的“Operations”中更改引擎。

上传到BLOB存储库 ：在 book 表中，访问“Insert”面板（或编辑现有行），在 cover_photo 列的对话框中会看到“Upload to BLOB repository”选项。由于 LONGBLOB 列也可保存常规数据，因此有两种选择：
- 点击“Upload to BLOB repository”选项，然后浏览文件或从Web服务器上传目录中选择一个文件，这会将文件发送到存储库以便后续流式传输。
- 不点击该选项，将数据直接发送到该列（不用于后续流式传输）。

若上传成功，会看到类似以下的查询：

UPDATE `marc_book`.`book` 
SET `cover_photo` = '~*marc_book/~1-150-15591197-0*3907219' 
WHERE `book`.`isbn` = '1-234567-22-0' LIMIT 1;

这里， cover_photo 被PBMS存储引擎生成的BLOB引用更新。

从存储库流式传输数据 ：浏览表并查看上传到BLOB存储库的行时，在 cover_photo 列下，根据数据格式会看到以下消息之一：
- View image
- Play audio
- View video
- Download file

点击显示的消息会直接从服务器的8080端口将数据流式传输到浏览器，并发送适当的MIME类型以告知浏览器预期的格式。同时还会显示上传数据的MIME类型（如 video/mp4 ）。

更改存储库数据 ：若上传数据的MIME类型未被正确解释，可以点击链接并输入正确的类型进行修改。在编辑该行时，会看到“Remove BLOB Repository Reference”选项，勾选该选项并保存行后，PBMS引擎会忘记该引用，最终从存储库中删除BLOB数据。

6. 用户和权限管理

对于系统管理员而言，可通过phpMyAdmin的服务器管理功能进行日常用户账户维护、服务器验证和保护。非管理员也能从phpMyAdmin获取服务器信息。服务器管理主要通过phpMyAdmin主页上的菜单选项访问“Server view”来完成。

用户和权限管理界面 ：“Privileges”子页面（仅在以特权用户登录时可见）包含用于管理MySQL用户账户的对话框，以及管理全局、数据库和表级权限的对话框。该子页面是分层的，例如在编辑用户权限时，可以看到全局权限以及特定数据库的权限，还能进一步查看该数据库 - 用户组合的特定表权限。

用户概览 ：进入“Privileges”子页面时显示的第一个页面是“User overview”，它显示所有用户账户及其全局权限摘要。从该页面可以进行以下操作：
- 通过用户行上的“Edit”链接编辑用户权限。
- 使用复选框选择用户，然后通过“Remove selected users”对话框删除用户。
- 访问“Add a new User”对话框所在的页面。

显示的用户列表各列具有以下特点：
| 列名 | 说明 |
| ---- | ---- |
| User | 定义的用户账户 |
| Host | 用户账户连接到MySQL服务器的机器名或IP地址， % 表示所有主机 |
| Password | 若定义了密码显示“Yes”，否则显示“No”，密码本身在phpMyAdmin界面或直接查看 mysql.user 表时不可见，因为使用单向哈希算法进行了混淆 |
| Global privileges | 列出每个用户的全局权限 |
| Grant | 若用户可以将其权限授予他人则显示“Yes” |

权限重新加载 ：在“User Overview”页面底部会显示消息：“Note: phpMyAdmin gets the users’ privileges directly from MySQL’s privilege tables. The content of these tables may differ from the privileges the server uses, if they have been changed manually. In this case, you should reload the privileges before you continue.”其中“reload the privileges”是可点击的。服务器的有效权限（即服务器用于访问决策的权限）位于服务器内存中。从“User overview”页面进行的权限修改会同时在内存和 mysql 数据库中进行，但直接对 mysql 数据库进行的修改不会立即生效。“reload the privileges”操作会从数据库读取权限并使其在内存中生效。

添加用户 ：点击“Add a new User”链接会弹出用户账户创建对话框。
- 用户名 ：“User name”菜单提供两种选择，可选择“Use text field”并在框中输入用户名，或选择“Any user”创建匿名用户。例如，我们选择“Use text field”并输入“bill”。
- 主机 ：默认情况下，该菜单设置为“Any host”，主机值为 % 。“Local”表示“localhost”，“Use host table”（在主机字段中创建空白值）表示在 mysql.hosts 表中查找特定数据库的权限，选择“Use text field”可输入所需的精确主机值。这里我们选择“Local”。
- 密码 ：虽然可以选择“无密码”创建用户，但最好设置密码。需要输入两次密码以确认，例如输入“bingo”。有些管理员为提高安全性，会让phpMyAdmin自动生成密码。在“Generate Password”对话框中，点击“Generate”会在屏幕上显示一个随机密码，此时应记录该密码以便传递给用户，然后点击“Copy”将密码复制到“Password”和“Re - Type”字段。
- 数据库创建和权限 ：常见的做法是为用户分配与其用户名同名的数据库权限。“Database for user”部分提供“Create database with same name and grant all privileges;”复选框，点击该复选框会自动创建数据库并授予相应权限，但每个用户只能有一个数据库。另一种选择是“Grant all privileges on wildcard name (username_%)”，它允许用户创建与用户名具有相同前缀的数据库，例如用户“bill”可以创建 bill_test 、 bill_2 、 bill_payroll 等数据库，在这种情况下，phpMyAdmin不会预先创建这些数据库。

综上所述，MySQL和phpMyAdmin提供了丰富的功能来满足不同用户的需求，无论是数据库的管理、分区设置、事件调度，还是用户和权限的管理，都有相应的工具和方法。但在使用过程中，需要注意各种功能的限制和要求，以确保系统的稳定和安全。

MySQL与phpMyAdmin功能详解

7. 功能总结与注意事项

MySQL 5.0的新特性助力产品符合标准，尽管phpMyAdmin对这些特性的支持有限（特别是缺乏面向语法的编辑器），但它具备一套基础功能，可用于处理视图、例程、触发器和 information_schema ，并显示分析信息。同时，phpMyAdmin还支持MySQL 5.1的分区和事件、PBXT存储引擎以及BLOB流式传输。

在使用这些功能时，需要注意以下几点：
- information_schema 使用 ：在编程项目中使用 information_schema 时要谨慎，尤其是在服务器托管大量数据库或表的情况下，涉及 WHERE 子句的 SELECT 操作可能会非常缓慢。
- BLOB流式传输 ：使用BLOB流式传输时，要确保满足系统要求，包括MySQL 5.1、PBXT和PBMS组件。同时，注意服务器名称的设置应为完全限定域名（FQDN），避免使用“localhost”，以免影响流式数据的检索。
- 事件调度器 ：创建和管理事件时，要确保事件调度器已激活，并且用户具有相应的 EVENT 权限。另外，在导出事件时，要注意事件可能有结束时间，可能在创建和导出之间已经消失。
- 用户和权限管理 ：进行权限修改后，若发现权限与服务器使用的不一致，应及时重新加载权限，以确保服务器使用的是最新的权限设置。

8. 操作流程梳理

为了更清晰地展示各项功能的操作流程，下面通过mermaid流程图进行梳理。

graph LR
    classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px;
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px;
    classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px;

    A([开始]):::startend --> B{选择操作类型}:::decision
    B -->|Information_schema查询| C(编写SELECT语句):::process
    C --> D(执行查询并获取元数据):::process
    B -->|分区表操作| E(进入分区表的Operations子页面):::process
    E --> F{选择操作}:::decision
    F -->|重建分区| G(选择分区并执行重建操作):::process
    F -->|移除分区| H(移除所有分区):::process
    B -->|事件调度器操作| I(检查事件调度器状态):::process
    I --> J{事件调度器是否开启}:::decision
    J -->|否| K(请求管理员开启):::process
    J -->|是| L(创建或管理事件):::process
    B -->|BLOB流式传输| M(检查系统要求):::process
    M --> N{是否满足要求}:::decision
    N -->|是| O(创建PBMS系统表):::process
    O --> P(准备表并上传数据):::process
    N -->|否| Q(安装缺失组件):::process
    Q --> M
    B -->|用户和权限管理| R(进入Privileges子页面):::process
    R --> S{选择操作}:::decision
    S -->|添加用户| T(填写用户信息和权限):::process
    S -->|编辑权限| U(选择用户并编辑权限):::process
    S -->|删除用户| V(选择用户并删除):::process
    D --> Z([结束]):::startend
    G --> Z
    H --> Z
    L --> Z
    P --> Z
    T --> Z
    U --> Z
    V --> Z

这个流程图涵盖了 information_schema 查询、分区表操作、事件调度器操作、BLOB流式传输以及用户和权限管理等主要功能的操作流程，帮助用户更直观地了解各项操作的步骤和决策点。

9. 常见问题及解决方法

在使用MySQL和phpMyAdmin的过程中，可能会遇到一些常见问题，以下是一些问题及相应的解决方法：
| 问题 | 描述 | 解决方法 |
| ---- | ---- | ---- |
| information_schema 查询缓慢 | 当服务器托管大量数据库或表时，涉及 information_schema 的 WHERE 子句的 SELECT 操作可能非常缓慢 | 谨慎使用该功能，避免在高负载情况下进行复杂查询 |
| BLOB流式传输失败 | 上传数据到BLOB存储库失败，或无法正常流式传输数据 | 检查系统要求是否满足，确保服务器名称为完全限定域名（FQDN），尝试使用Web服务器上传目录 |
| 事件调度器未激活 | 无法创建或执行事件 | 在查询框中输入 SHOW VARIABLES LIKE 'event%'; ，检查 event_scheduler 变量状态，若为 OFF ，请求系统管理员执行 SET GLOBAL event_scheduler = ON; |
| 用户权限修改未生效 | 对用户权限进行修改后，服务器仍使用旧的权限 | 点击“User Overview”页面底部的“reload the privileges”，重新加载权限 |

10. 未来功能展望

随着技术的不断发展，MySQL和phpMyAdmin可能会不断完善和扩展其功能。未来可能会有以下方面的改进：
- 增强的用户界面 ：phpMyAdmin可能会开发更友好、更强大的用户界面，例如为 CREATE EVENT 语句提供可视化的创建界面，方便用户更轻松地创建和管理事件。
- 性能优化 ：针对 information_schema 查询缓慢等性能问题，可能会进行优化，提高查询效率。
- 更多存储引擎支持 ：除了现有的PBXT存储引擎，可能会支持更多的存储引擎用于BLOB流式传输，提供更多的选择。
- 安全增强 ：进一步加强用户和权限管理的安全性，例如提供更精细的权限控制和更严格的密码策略。

总之，MySQL和phpMyAdmin在数据库管理领域有着广泛的应用，通过不断学习和掌握其各项功能，用户可以更高效地管理和操作数据库。同时，关注其未来的发展动态，也有助于我们更好地利用这些工具，满足不断变化的业务需求。