18、MySQL 5.0与5.1新特性及phpMyAdmin支持详解

MySQL 5.0与5.1新特性及phpMyAdmin支持详解

1. MIME 转换示例：单元格内排序

在数据处理中，有时需要对单元格内的文本内容进行排序。以下是实现单元格内文本排序的具体步骤：
1. 修改脚本 ：修改 text_plain__external.inc.php 脚本，添加排序程序。具体代码如下：

$allowed_programs[0] = '/bin/sort';

这里新程序的索引编号为 0。
2. 添加字段和关键字 ：在 book 表中添加一个 TEXT 字段和关键字。
3. 填写 MIME 相关信息 ：输入 '0','-r' 作为转换选项。其中， 0 指排序程序的索引编号， -r 是排序参数，使程序按逆序排序。
4. 编辑行数据 ：编辑“ A hundred years of cinema (volume 1) ”这本书的行数据，输入一些无特定顺序的关键字，然后点击“ Go ”保存更改。
5. 测试效果 ：浏览表数据，可看到单元格内的关键字已按逆序排序。

2. MySQL 5.0 和 5.1 新特性概述

MySQL 5.0 引入了许多新特性，如视图、存储过程、触发器、标准 INFORMATION_SCHEMA 和分析机制等。这些特性的推出让开发者和行业观察者对 MySQL 有了新的认识。MySQL 5.1 中与 Web 界面相关的特性（如分区和事件）在 phpMyAdmin 中得到支持。

3. 视图的使用

视图是一种派生表，其定义存储在数据库中。可以对一个或多个表（甚至视图）执行 SELECT 语句，并将其存储为视图，之后还能对视图进行查询。视图的主要用途如下：
- 限制列的可见性 ：例如，不显示员工的工资列。
- 限制行的可见性 ：例如，不显示特定地区的数据行。
- 隐藏表结构的变化 ：使旧应用程序能够继续正常工作。

由于视图本身附带一些权限，因此授予视图整体的权限比为底层表定义繁琐的列特定权限更为便捷。

3.1 激活视图支持

从 MySQL 5.0 之前的版本升级后，管理员需执行 mysql_fix_privileges_tables 脚本，以激活服务器对视图的支持。每个用户必须具备相应的 SHOW_VIEW 或 CREATE_VIEW 权限，才能查看或操作视图。这些权限存在于全局（服务器）、数据库和表级别。

3.2 创建视图的方法

• 手动创建视图 ：使用查询框输入相应语句。例如，创建一个名为 book_author 的视图：

CREATE VIEW book_author AS 
SELECT book.isbn, book.title, author.name FROM book
LEFT JOIN author ON author.id = book.author_id;

创建视图意味着用户必须对涉及的表具有权限，或者至少对视图中提及的所有列具有 SELECT 或 UPDATE 等权限。创建完成后，左侧面板会更新以显示视图已创建。
- 从查询结果创建视图 ：可以利用 phpMyAdmin 的搜索（表级别）或查询（数据库级别）功能构建复杂查询，执行查询后，通过结果页面上的“ CREATE VIEW ”链接创建视图。例如，若书籍的页数是高度机密信息，可打开 book 表，点击“ Search ”，选择部分列，点击“ Go ”后，在结果页面点击“ CREATE VIEW ”链接，进入视图创建面板，输入视图名称（如 book_public_info ），还可选择设置不同的列名（如 number 、 title ），最后点击“ Go ”生成视图。

3.3 视图的操作

• 浏览视图 ：点击视图名称，会显示与表类似的面板，但菜单标签较少。在生成的 SQL 查询中，看不到原始的 CREATE VIEW 语句，因为是使用 SELECT 语句从视图中选择数据。导出视图结构可查看 MySQL 内部存储视图的方式。
• 删除视图 ：点击“ Drop ”会提示确认是否删除视图。
• 重命名视图 ： phpMyAdmin 不支持直接从界面重命名视图，但自 MySQL 5.0.14 起，可在 phpMyAdmin 的查询框中使用 RENAME TABLE 语句在同一数据库内重命名视图。
• 性能提示 ： phpMyAdmin 有一个配置参数 $cfg['MaxExactCountViews'] ，用于控制视图的行数计数阶段。默认值为 0，确保不对视图进行行数计数，以避免服务器性能下降。若希望查看更精确的行数计数，可设置一个较大的值作为上限。

以下是创建视图的流程图：

graph TD;
    A[开始] --> B[手动创建视图或从结果创建视图];
    B --> C{是否创建成功};
    C -- 是 --> D[视图创建完成];
    C -- 否 --> E[检查权限和语法];
    E --> B;
    D --> F[进行视图操作（浏览、删除、重命名等）];
    F --> G[结束];

4. 存储过程和函数

存储过程和函数是存储在数据库中的代码块。 phpMyAdmin 支持创建、保存、修改和删除这些例程。

4.1 存储过程

• 创建存储过程 ：以创建一个用于更改特定书籍页面计数的存储过程为例，具体步骤如下：
  1. 更改分隔符 ：将查询框中的分隔符从默认的分号改为 // 。
  2. 输入存储过程代码 ：

CREATE PROCEDURE `add_page`(IN param_isbn VARCHAR(25), IN param_pages INT, OUT param_message VARCHAR(100))
BEGIN
    IF param_pages > 100 THEN
        SET param_message = 'the number of pages is too big';
    ELSE
        UPDATE book SET page_count = page_count + param_pages WHERE isbn = param_isbn;
        SET param_message = 'success';
    END IF;
END
//

3. **测试存储过程**：在查询框中输入以下语句进行测试：

call add_page('1-234567-22-0', 4, @message);
SELECT @message;

还需测试异常情况：

call add_page('1-234567-22-0', 101, @message);
SELECT @message;

• 操作存储过程 ：存储过程存储在数据库中，与特定表无关。可在数据库的“ Structure ”页面的“ Routines ”滑块下找到操作界面。编辑存储过程时，会先显示 DROP PROCEDURE 语句，因为 MySQL 没有直接修改存储过程主体的语句。修改代码后点击“ Go ”保存。

4.2 函数

函数与存储过程类似，但函数只能返回一个值。使用存储函数在 SELECT 语句中更自然，可避免使用中间 SQL 变量。例如，创建一个根据国家代码检索国家名称的函数：

CREATE FUNCTION get_country_name(param_country_code CHAR(2))
RETURNS VARCHAR(50)
BEGIN
    DECLARE var_country_name VARCHAR(50) DEFAULT 'not found';
    SELECT description 
        INTO var_country_name 
        FROM country 
        WHERE code = param_country_code;
    RETURN var_country_name;
END
//

测试函数：

SELECT CONCAT('ca->', get_country_name('ca'), ', zz->', get_country_name('zz')) as test;

结果为： ca->Canada, zz->not found 。

4.3 导出存储过程和函数

导出数据库时，默认情况下存储过程和函数不会出现在 SQL 导出文件中。在导出页面的“ Structure ”对话框中有一个复选框“ Add CREATE PROCEDURE / FUNCTION / EVENT ”，勾选后可将它们包含在 SQL 导出文件中。导出结果示例如下：

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`marc`@`%` PROCEDURE `add_page`(IN param_isbn VARCHAR(25), IN param_pages INT, OUT param_message VARCHAR(100))
BEGIN
    IF param_pages > 100 THEN
        SET param_message = 'the number of pages is too big';
    ELSE
        UPDATE book SET page_count = page_count + param_pages WHERE isbn = param_isbn;
        SET param_message = 'success';
    END IF;
END$$
--
-- Functions
--
CREATE DEFINER=`marc`@`%` FUNCTION `get_country_name`(param_country_code CHAR(2)) RETURNS varchar(50) CHARSET latin1
BEGIN
    DECLARE var_country_name VARCHAR(50) DEFAULT 'not found';
    SELECT description into var_country_name FROM country WHERE code = param_country_code;
    RETURN var_country_name; 
END$$
DELIMITER ;

5. 触发器

触发器是与表关联的代码，在特定操作发生时执行，例如在 book 表插入新记录后执行。与存储过程和函数不同，触发器与特定表相关，可从表的“ Structure ”页面访问。

• 创建触发器的权限 ：在 MySQL 5.1.6 之前，创建和删除触发器需要 SUPER 权限；在 5.1.6 版本中，权限系统增加了 TRIGGER 表级权限，用户不再需要强大的 SUPER 权限。
• 创建触发器示例 ：假设在 author 表中添加一个 total_page_count 列，每次创建新书时，将其页数添加到作者的总页数中。手动创建触发器的步骤如下：
  1. 在查询框中输入触发器定义，注意将分隔符设置为 // ：

CREATE TRIGGER after_book_insert AFTER INSERT ON book
FOR EACH ROW
BEGIN
    UPDATE author 
        SET total_page_count = total_page_count + NEW.page_count
        WHERE id = NEW.author_id;
END

2. 创建完成后，在 `book` 表的“`Structure`”页面的“`Details`”滑块下会显示新的 `Triggers` 部分，可用于编辑或删除触发器。

• 测试触发器 ：插入新书籍时，触发器代码会自动执行，更新 author 表中的 total_page_count 列。为实现该列的完全自动化管理，还需在 book 表上创建 AFTER UPDATE 和 AFTER DELETE 触发器。

综上所述，MySQL 5.0 和 5.1 的新特性以及 phpMyAdmin 的支持为数据库开发和管理提供了更多的灵活性和功能。通过合理使用视图、存储过程、函数和触发器，可以优化数据库的性能和可维护性。

MySQL 5.0与5.1新特性及phpMyAdmin支持详解

6. 各项功能对比总结

为了更清晰地了解视图、存储过程、函数和触发器的特点和使用场景，下面通过表格进行对比总结：
| 功能 | 定义 | 用途 | 创建方式 | 权限要求 | 操作特点 |
| — | — | — | — | — | — |
| 视图 | 派生表，定义存储在数据库中 | 限制列和行的可见性，隐藏表结构变化 | 手动输入 CREATE VIEW 语句或从查询结果创建 | SHOW_VIEW 或 CREATE_VIEW 权限 | 浏览、删除、重命名等操作 |
| 存储过程 | 存储在数据库中的代码块 | 执行特定的数据库操作，如更改书籍页面计数 | 输入存储过程代码，更改分隔符 | CREATE ROUTINE 、 ALTER ROUTINE 和 EXECUTE 权限 | 可调用执行，编辑时需先删除再重新创建 |
| 函数 | 存储在数据库中的代码块，返回一个值 | 计算特定的值，如根据国家代码获取国家名称 | 输入函数代码，更改分隔符 | CREATE ROUTINE 、 ALTER ROUTINE 和 EXECUTE 权限 | 可在 SELECT 等语句中调用 |
| 触发器 | 与表关联的代码，特定操作时执行 | 实现表数据变化时的自动操作，如更新作者总页数 | 在查询框输入触发器定义，设置分隔符 | MySQL 5.1.6 前需 SUPER 权限，之后需 TRIGGER 表级权限 | 随表操作自动触发 |

7. 实际应用流程示例

假设我们要开发一个图书管理系统，需要综合运用上述功能，以下是具体的应用流程：
1. 创建视图 ：为了保护某些敏感信息，如书籍的成本等，创建一个只包含公共信息的视图。
- 手动创建视图 book_public_info ：

CREATE VIEW book_public_info AS 
SELECT isbn, title, author_name FROM book;

2. 创建存储过程 ：实现更新书籍库存的功能。
   • 更改分隔符为 // 。
   • 输入存储过程代码：

CREATE PROCEDURE `update_book_stock`(IN param_isbn VARCHAR(25), IN param_quantity INT)
BEGIN
    UPDATE book SET stock = stock + param_quantity WHERE isbn = param_isbn;
END
//

3. 创建函数 ：计算书籍的总价值（单价 * 库存）。
   • 更改分隔符为 // 。
   • 输入函数代码：

CREATE FUNCTION get_book_total_value(param_isbn VARCHAR(25))
RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE var_price DECIMAL(10, 2);
    DECLARE var_stock INT;
    SELECT price, stock INTO var_price, var_stock FROM book WHERE isbn = param_isbn;
    RETURN var_price * var_stock;
END
//

4. 创建触发器 ：当有新书籍入库时，自动更新库存总量。
   • 在查询框输入触发器定义，分隔符设置为 // ：

CREATE TRIGGER after_book_incoming AFTER INSERT ON book_incoming_record
FOR EACH ROW
BEGIN
    UPDATE book SET stock = stock + NEW.quantity WHERE isbn = NEW.isbn;
END

以下是这个应用流程的 mermaid 流程图：

graph LR;
    A[开始] --> B[创建视图];
    B --> C[创建存储过程];
    C --> D[创建函数];
    D --> E[创建触发器];
    E --> F[系统投入使用];
    F --> G{有新操作?};
    G -- 是 --> H[视图、存储过程、函数或触发器起作用];
    H --> G;
    G -- 否 --> I[结束];

8. 注意事项和技巧

• 权限管理 ：在使用视图、存储过程、函数和触发器时，要严格控制用户的权限，避免不必要的安全风险。例如，只给用户授予执行存储过程的权限，而不授予直接操作表的权限。
• 性能优化 ：对于视图的行数计数，合理设置 $cfg['MaxExactCountViews'] 参数，避免因不必要的计数导致服务器性能下降。在创建存储过程和函数时，尽量减少复杂的嵌套和循环，提高执行效率。
• 错误处理 ：在存储过程和函数中，要考虑各种可能的错误情况，如输入参数不合法等，通过 IF 语句等进行错误处理，避免程序崩溃。

9. 总结

MySQL 5.0 和 5.1 引入的新特性，如视图、存储过程、函数和触发器，以及 phpMyAdmin 对这些特性的支持，极大地丰富了数据库的功能和管理方式。通过合理运用这些功能，可以实现数据库的高效操作、数据安全保护和自动化处理。在实际应用中，要根据具体的需求和场景，选择合适的功能，并注意权限管理、性能优化和错误处理等方面的问题，以充分发挥 MySQL 数据库的优势。

希望本文能帮助读者更好地理解和使用 MySQL 5.0 和 5.1 的新特性以及 phpMyAdmin 的相关功能，在数据库开发和管理中取得更好的效果。