PHP-MySQL-入门教程-十一-

布客飞龙
44 阅读35分钟

PHP MySQL 入门教程（十一）

原文：Beginning PHP and MySQL

协议：CC BY-NC-SA 4.0

三十四、事务

本章介绍了 MySQL 的事务处理能力，并演示了如何通过 MySQL 客户端和从 PHP 脚本中执行事务。根据本文的结论，您将对事务有一个大致的了解，它们是如何被 MySQL 实现的，以及如何将它们整合到您的 PHP 应用中。

什么是交易？

事务是一组有序的数据库操作，它们被视为一个单元。如果组中的所有操作都成功，则认为交易成功，如果甚至一个操作失败，则认为交易不成功。如果所有操作成功完成，该事务将被提交，并且它的更改将对所有其他数据库进程可用。如果某项操作失败，该事务将回滚，构成该事务的所有操作的效果将被取消。

在事务过程中实现的任何改变将仅对拥有该事务的线程可用，并且将保持如此，直到那些改变确实被提交。这可以防止其他线程潜在地利用可能由于回滚而很快被否定的数据，这将导致数据完整性的破坏。

事务处理能力是企业数据库的关键部分，因为许多业务流程由多个步骤组成。举个例子，一个客户试图执行在线购买。结账时，顾客的购物车将与现有库存进行比较，以确保可用性。接下来，客户必须提供他们的账单和运输信息，此时他们的信用卡将被检查是否有必要的可用资金，然后被记入借方。接下来，将相应地扣除产品库存，并将未决订单通知给运输部门。如果这些步骤中的任何一个失败了，那么它们都不应该发生。想象一下，当客户得知他们的信用卡已经被扣款，而产品却因为库存不足而从未到达时，他会多么沮丧。同样，如果信用卡无效或者没有提供足够的运输信息，您也不会想要扣除库存或者运输产品。数据的收集(购物车、信用卡信息等。)不应包含在完成销售的实际事务中，因为这将导致在事务发生时受影响的表和行被锁定以进行读写。

用更专业的术语来说，一项交易是由其遵循四个原则的能力来定义的，体现在缩写词 ACID 中。这里定义了交易流程的四个支柱:

  • **原子性:**交易的所有步骤必须成功完成；否则，不会提交任何步骤。

  • **一致性:**交易的所有步骤必须成功完成；否则，所有数据都将恢复到事务开始前的状态。

  • **隔离:**任何尚未完成的交易执行的步骤必须保持与系统隔离，直到交易被视为完成。

  • **持久性:**所有提交的数据必须由系统保存，以便在系统出现故障时，数据可以成功恢复到有效状态。

随着您在本章中对 MySQL 的事务支持了解得越来越多，您将会理解必须遵循这些原则来确保数据库的完整性。

MySQL 的事务处理能力

MySQL 的两个存储引擎支持事务:InnoDB 和 NDB。InnoDB 在第二十五章中介绍过，NDP 不在本书讨论范围之内。本节解释应用于 InnoDB 的事务。它首先讨论了 InnoDB 处理器可用的系统要求和配置参数，最后给出了一个详细的使用示例和一系列使用 InnoDB 事务时需要记住的技巧。这一节为本章的最后一部分打下了基础，在这一部分中，您将学习如何将事务处理能力整合到您的 PHP 应用中。

系统需求

本章重点介绍受流行的 InnoDB 存储引擎支持的事务。InnoDB 是启用的，并且是大多数系统上的默认存储引擎，除非您从源代码编译了 MySQL 并省略了它。您可以通过执行以下命令来验证 InnoDB 表是否可用:

mysql>show variables like '%have_inn%';

您应该看到以下内容:

+-----------------------+
| Variable_name | Value |
+-----------------------+
| have_innodb    | YES  |
+-----------------------+
1 row in set (0.00 sec)

或者，您可以使用SHOW ENGINES;命令查看 MySQL 服务器支持的所有存储引擎。

表格创建

创建 InnoDB 类型的表实际上与创建任何其他类型的表没有什么不同。事实上，这种表类型在所有平台上都是默认的，这意味着创建 InnoDB 表不需要任何特殊操作。您所需要做的就是使用CREATE TABLE语句创建您认为合适的表格。如果您希望在创建表时更加明确，可以像这样添加引擎关键字:

CREATE TABLE customers (
   id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255) NOT NULL
   ) ENGINE=InnoDB;

一旦被创建，一个*.frm文件(在这个例子中，是一个customers.frm文件)被存储在各自的数据库目录中，其位置由 MySQL 的datadir参数表示，并在守护进程启动时定义。该文件包含 MySQL 所需的数据字典信息。然而，与 MyISAM 表不同，InnoDB 引擎要求将所有 InnoDB 数据和索引信息存储在一个表空间中。这个表空间实际上可以由许多不同的文件(甚至是原始磁盘分区)组成，它们默认位于 MySQL 的datadir目录中。这是一个非常强大的特性——这意味着，只需根据需要将新文件连接到表空间，就可以创建远远超过许多操作系统所规定的最大允许文件大小的数据库。所有这些行为取决于您如何定义相关的 InnoDB 配置参数，接下来将介绍这些参数。

注意

您可以通过修改innodb_data_home_dir参数来更改表空间的默认位置。

一个示例项目

为了让您确切了解 InnoDB 表的行为，本节将通过一个从命令行执行的简单事务示例来指导您。这个例子展示了两个旧物交换参与者如何将一件物品兑换成现金。在检查代码之前，花点时间查看一下伪代码:

  1. 参与者 Jason 请求一个项目，比如位于参与者 Jon 的虚拟行李箱中的算盘。

  2. 参与者 Jason 向参与者 Jon 的账户转账 12.99 美元。这样做的结果是借记杰森的账户，贷记乔恩的账户。

  3. 算盘的所有权转移给杰森。

如你所见，这个过程的每一步对整个手术的成功都是至关重要的。您将把这个过程变成一个事务，以确保数据不会因为一个步骤的失败而被破坏。尽管在实际场景中还有其他步骤，比如确保购买参与者拥有足够的资金，但是在这个示例中，这个过程保持简单，以便不偏离主题。

创建表和添加示例数据

要跟进项目，请创建下表并添加后面的示例数据。

参与者表

该表存储了每个交换会议参与者的信息，包括他们的姓名、电子邮件地址和可用现金:

CREATE TABLE participants (
   id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(35) NOT NULL,
   email VARCHAR(45) NOT NULL,
   cash DECIMAL(5,2) NOT NULL
   ) ENGINE=InnoDB;
行李箱桌子

此表存储参与者拥有的每件物品的信息，包括所有者、名称、描述和价格:

CREATE TABLE trunks (
   id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   owner SMALLINT UNSIGNED NOT NULL REFERENCES participants(id),
   name VARCHAR(25) NOT NULL,
   price DECIMAL(5,2) NOT NULL,
   description MEDIUMTEXT NOT NULL
   ) ENGINE=InnoDB;
添加一些示例数据

接下来，向两个表中添加几行数据。为了简单起见，添加两个参与者 Jason 和 Jon，并为他们各自的行李箱添加一些物品:

mysql>INSERT INTO participants SET name="Jason", email="jason@example.com",
                                  cash="100.00";
mysql>INSERT INTO participants SET name="Jon", email="jon@example.com",
                                  cash="150.00";
mysql>INSERT INTO trunks SET owner=2, name="Abacus", price="12.99",
                            description="Low on computing power? Use an abacus!";
mysql>INSERT INTO trunks SET owner=2, name="Magazines", price="6.00",
                            description="Stack of computer magazines.";
mysql>INSERT INTO trunks SET owner=1, name="Used Lottery ticket", price="1.00",
                            description="Great gift for the eternal optimist.";

执行示例事务

通过发出START TRANSACTION命令开始交易过程:

mysql>START TRANSACTION;

注意

命令BEGIN是START TRANSACTION的别名。尽管两者完成的任务相同，但建议您使用后者，因为它符合 SQL-99 语法。

接下来，从 Jason 的账户中扣除 12.99 美元:

mysql>UPDATE participants SET cash=cash-12.99 WHERE id=1;

接下来，将 12.99 美元记入乔恩的账户:

mysql>UPDATE participants SET cash=cash+12.99 WHERE id=2;

接下来，将算盘的所有权转让给 Jason:

mysql>UPDATE trunks SET owner=1 WHERE name="Abacus" AND owner=2;

花点时间检查一下participants表，确保现金金额已经正确记入借方和贷方:

mysql>SELECT * FROM participants;

这将返回以下结果:

+-------+-------+-------------------+----------+
| id    | name  | email             | cash     |
+-------+-------+-------------------+----------+
|     1 | Jason | jason@example.com |  87.01   |
|     2 | Jon   | jon@example.com   | 162.99   |
+-------+-------+-------------------+----------+

还需要花点时间检查一下 trunks 表；你会发现算盘的所有权确实变了。但是，请记住，因为 InnoDB 表必须遵循 ACID 原则，所以这种更改目前只对执行事务的线程可用。为了说明这一点，启动第二个 mysql 客户端，再次登录并切换到corporate数据库。查看participants表。你会看到参与者各自的现金价值保持不变。检查trunks表也会显示算盘的所有权没有改变。这是因为酸性测试的隔离部分。在您COMMIT更改之前，事务处理过程中所做的任何更改都不会对其他线程可用。

尽管这些更新确实工作正常，但假设有一个或几个没有正常工作。返回第一个客户端窗口，通过发出命令ROLLBACK取消更改:

mysql>ROLLBACK;

现在再次执行SELECT命令:

mysql>SELECT * FROM participants;

这将返回:

+-------+-------+-------------------+--------+
| id    | name  | email             | cash   |
+-------+-------+-------------------+--------+
|     1 | Jason | jason@example.com | 100.00 |
|     2 | Jon   | jon@example.com   | 150.00 |
+-------+-------+-------------------+--------+

请注意，参与者的现金持有量已被重置为其原始值。检查trunks表也会显示算盘的所有权没有改变。尝试重新重复上述过程，这次使用COMMIT命令提交更改，而不是回滚。一旦事务被提交，再次返回到第二个客户机并检查这些表；您将看到提交的更改立即可用。

注意

您应该意识到，在发出COMMIT或ROLLBACK命令之前，事务序列中发生的任何数据更改都不会生效。这意味着，如果 MySQL 服务器在提交更改之前崩溃，更改将不会发生，您需要启动事务系列来使这些更改发生。

下一节“用 PHP 构建事务性应用”将使用 PHP 脚本重新创建这个过程。

使用技巧

以下是使用 MySQL 事务时需要记住的一些提示:

  • 发出START TRANSACTION命令等同于将AUTOCOMMIT变量设置为0。默认值是AUTOCOMMIT=1，这意味着每条语句一旦成功执行就会被提交。这就是用START TRANSACTION命令开始事务的原因——因为您不希望事务的每个组成部分都在执行时提交。

  • 只有当整个流程的成功执行至关重要时，才使用事务。例如，将产品添加到购物车的过程非常关键；浏览所有可用的产品不是。在设计表时要考虑这些问题，因为这无疑会影响性能。

  • 您不能回滚数据定义语言语句；也就是说，用于创建或删除数据库，或者创建、删除或更改表的任何语句。

  • 事务不能嵌套。在一个COMMIT或ROLLBACK之前发出多个START TRANSACTION命令将不起作用。

  • 如果在事务处理过程中更新非事务性表，然后通过发出 ROLLBACK 来结束该事务，将会返回一个错误，通知您非事务性表将不会回滚。

  • 通过备份二进制日志文件，定期拍摄 InnoDB 数据和日志的快照，并使用mysqldump拍摄每个表中数据的快照。二进制日志文件作为增量备份，可以应用于以前的备份，以便在必须从备份恢复数据库时将数据库前滚到给定点。

用 PHP 构建事务性应用

将 MySQL 的事务能力集成到 PHP 应用中真的不是什么大事；您只需要记住在适当的时候启动事务，然后在相关操作完成后提交或回滚事务。在本节中，您将了解这是如何实现的。学完本课程后，您应该熟悉将这一重要功能整合到您的应用中的一般过程。

旧地重游

在本例中，您将重新创建之前演示的交换会议场景，这次使用 PHP。尽量减少不相关的细节；该页面将显示一个产品，并向用户提供将该商品添加到购物车的方法；它可能看起来类似于图 34-1 中所示的截图。

img/314623_5_En_34_Fig1_HTML.jpg

图 34-1

典型的产品展示

点击购买！按钮会把用户带到一个purchase.php脚本。传递一个变量，即$_POST['itemid']。将该变量与一些用于检索适当的participants和trunks行主键的假设类方法结合使用，您可以使用 MySQL 事务将产品添加到数据库中，并相应地扣除和贷记参与者的账户。

为了执行这个任务，使用mysqli扩展的事务性方法，第二十七章第一次介绍了这些方法。清单 34-1 包含代码(purchase.php)。如果您不熟悉这些方法，请在继续之前花点时间参考第三章中的相关章节进行快速回顾。

<?php

   // Give the POSTed item ID a friendly variable name
   $itemID = filter_var($_POST['itemid'], FILTER_VALIDATE_INT);
   $participant = new Participant();
   $buyerID = $participant->getParticipantKey();

   // Retrieve the item seller and price using some fictitious item class
   $item = new Item();
   $sellerID = $item->getItemOwner($itemID);
   $price = $item->getPrice($itemID);

   // Instantiate the mysqli class
   $db = new mysqli("localhost","website","secret","chapter34");

   // Disable the autocommit feature
   $db->autocommit(FALSE);

   // Debit buyer's account

   $stmt = $db->prepare("UPDATE participants SET cash = cash - ? WHERE id = ?");

   $stmt->bind_param('di', $price, $buyerID);

   $stmt->execute();

   // Credit seller's account
   $query = $db->prepare("UPDATE participants SET cash = cash + ? WHERE id = ?");

   $stmt->bind_param('di', $price, $sellerID);

   $stmt->execute();

   // Update trunk item ownership. If it fails, set $success to FALSE
   $stmt = $db->prepare("UPDATE trunks SET owner = ? WHERE id = ?");

   $stmt->bind_param('ii', $buyerID, $itemID);

   $stmt->execute();

   if ($db->commit()) {
      echo "The swap took place! Congratulations!";
   } else {
      echo "There was a problem with the swap!";
   }

?>

Listing 34-1Swapping Items with purchase.php

如您所见，在事务的每一步执行之后，都会检查查询的状态和受影响的行。如果任何一次失败，$success被设置为FALSE,所有步骤在脚本结束时回滚。当然，您可以优化这个脚本，以锁步方式启动每个查询，每个查询只在确定前面的查询实际上已经正确执行之后才发生，但这留给您作为练习。

MySQL 也支持rollback命令。当在事务中发出时，数据库将撤消自事务开始以来的所有命令。如果在处理事务的过程中出现错误，通常会使用这种方法。与其提交不完整的值，不如回滚。

摘要

在对业务流程建模时，数据库事务非常有用，因为它们有助于确保组织最有价值的资产(信息)的完整性。如果您谨慎地使用数据库事务，在构建数据库驱动的应用时，它们是一笔巨大的资产。

在下一章也是最后一章，您将学习如何使用 MySQL 的默认实用程序导入和导出大量数据。此外，您将看到如何使用 PHP 脚本格式化基于表单的信息，以便通过电子表格应用(如 Microsoft Excel)进行查看。

三十五、导入和导出数据

回到石器时代，穴居人从未真正遇到过数据不兼容的问题——石头和自己的记忆是唯一的存储介质。复制数据需要拔出旧凿子，在一块新的花岗岩板上忙碌。当然，现在的情况大不相同了。存在数百种数据存储策略，其中最常见的包括电子表格和各种类型的关系数据库。以一种复杂甚至令人费解的方式工作，您经常需要将数据从一种存储类型转换为另一种存储类型，比如在电子表格和数据库之间，或者在 Oracle 数据库和 MySQL 之间。如果做得不好，您可能会花费数小时，甚至数天甚至数周的时间，将转换后的数据转换成可用的格式。本章试图通过介绍 MySQL 的数据导入和导出实用程序来解决这个难题，并介绍各种技术和概念来减轻执行这些任务的痛苦。

学完本章后，你将熟悉以下主题:

  • 大多数主流存储产品认可的通用数据格式标准

  • SELECT INTO OUTFILE SQL 语句

  • LOAD DATA INFILE SQL 语句

  • mysqlimport实用程序

  • 如何使用 PHP 模仿 MySQL 内置的导入工具

在深入研究核心主题之前，花点时间回顾一下作为本章示例基础的示例数据。之后，介绍了围绕 MySQL 的导入和导出策略的几个基本概念。

样本表

如果您想在继续学习本章的过程中执行这些示例，下面的sales表将是本章中几个示例的重点:

CREATE TABLE sales (
   id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   client_id SMALLINT UNSIGNED NOT NULL,
   order_time TIMESTAMP NOT NULL,
   sub_total DECIMAL(8,2) NOT NULL,
   shipping_cost DECIMAL(8,2) NOT NULL,
   total_cost DECIMAL(8,2) NOT NULL
);

此表用于跟踪基本销售信息。虽然它缺少许多您可能在实际实现中发现的列，但是为了将重点放在本章介绍的概念上，我们省略了额外的细节。

使用数据定界

即使你是一个初露头角的程序员，你可能已经非常熟悉软件对数据的严格要求。所有的 I 必须加点，所有的 t 必须加叉，一个错位的字符就足以产生意想不到的结果。因此，您可以想象在尝试将数据从一种格式转换为另一种格式时可能出现的问题。令人欣慰的是，一种特别方便的格式化策略已经变得司空见惯:定界。

像数据库表和电子表格这样的信息结构共享一个相似的概念组织。这些结构通常被概念化为由行和列组成，每一行和列又进一步细分为单元。因此，只要制定一套规则来确定如何识别列、行和单元格，就可以在格式之间进行转换。最重要的规则之一是建立一个字符或字符序列，用作分隔符，将一行中的每个单元格以及每一行与下一行分开。例如，sales表可能以一种格式分隔，用逗号分隔每个字段，用换行符分隔每个行:

12309,45633,2010-12-19 01:13:42,22.04,5.67,27.71\n
12310,942,2010-12-19 01:15:12,11.50,3.40,14.90\n
12311,7879,2010-12-19 01:15:22,95.99,15.00,110.99\n
12312,55521,2010-12-19 01:30:45,10.75,3.00,13.75\n

当然，在文本编辑器中查看文件时，换行符是不可见的；我在这里展示它只是为了说明。许多数据导入和导出实用程序，包括 MySQL 的，都围绕着数据定界的概念。

导入数据

在本节中，您将了解 MySQL 提供的两个内置工具，用于将分隔数据集导入到表中:LOAD DATA INFILE和mysqlimport。

小费

当您需要创建从cron作业执行的批量导入时，您可以考虑使用mysqlimport客户端来代替LOAD DATA INFILE。

使用加载文件中的数据导入数据

L OAD DATA INFILE语句是一个执行起来很像查询的命令，通常从mysql客户端执行，用于将带分隔符的文本文件导入到 MySQL 表中。其通用语法如下:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE table_name
[CHARACTER SET charset_name]
[FIELDS
   [TERMINATED BY 'character'] [[OPTIONALLY] ENCLOSED BY 'character']
   [ESCAPED BY 'character']
]
[LINES
   [STARTING BY 'character'] [TERMINATED BY 'character']
]
[IGNORE number lines]
[(column_name, ...)]
[SET column_name = expression, ...)]

当然，这是迄今为止最长的 MySQL 查询命令之一，不是吗？然而，正是这种广泛的选择使它如此强大。接下来介绍每个选项:

  • LOW PRIORITY:该选项强制命令延迟执行，直到没有其他客户端从表中读取数据。

  • CONCURRENT:与 MyISAM 表结合使用，该选项允许其他线程在命令执行时从目标表中检索数据。

  • LOCAL:该选项声明目标 infile 必须驻留在客户端。如果省略，目标 infile 必须驻留在托管 MySQL 数据库的同一服务器上。使用LOCAL时，根据当前位置，文件的路径可以是绝对路径，也可以是相对路径。省略时，路径可以是绝对的；本地；或者，如果不存在，假定驻留在 MySQL 的指定数据库目录或当前选择的数据库目录中。

  • REPLACE:该选项导致用拥有相同主键或唯一键的新行替换现有行。

  • IGNORE:包含此选项与REPLACE效果相反。具有与现有表行匹配的主键或唯一键的读入行将被忽略。

  • CHARACTER SET charset_name : MySQL 将假定输入文件包含与分配给系统变量character_set_database的字符集相匹配的字符。如果字符与此设置不匹配，请使用此选项来标识文件的字符集。

  • FIELDS TERMINATED BY ' character ':该选项表示字段将如何终止。因此，FIELDS TERMINATED BY ','意味着每个字段将以逗号结尾，就像这样:

12312,55521,2010-12-19 01:30:45,10.75,3.00,13.75

最后一个字段不以逗号结尾，因为它不是必需的，因为通常这个选项与LINES TERMINATED BY 'character'选项一起使用。默认情况下，遇到由这个选项指定的字符还会对文件中的最后一个字段进行定界，并向命令发出信号，表明新行(行)即将开始。

  • [OPTIONALLY] ENCLOSED BY ' character ':该选项表示每个字段将被一个特定的字符包围。这并没有消除对终止字符的需要。修改前面的例子，使用选项FIELDS TERMINATED BY ',' ENCLOSED BY '"'意味着每个字段用一对双引号括起来，并用逗号分隔，如下所示:
"12312","55521","2010-12-19 01:30:45","10.75","3.00","13.75"

可选的OPTIONALLY标志表示字符串只需要用指定的字符模式括起来。只包含整数、浮点数等的字段不需要括起来。

  • ESCAPED BY ' character ':如果ENCLOSED BY选项表示的字符出现在任何字段中，必须对其进行转义，以确保该字段不会被错误读入。然而，这个转义字符必须由ESCAPED BY定义，这样它才能被命令识别。例如，FIELDS TERMINATED BY ',' ENCLOSED BY "' ESCAPED BY '\\'将允许正确解析以下字段:

    'jason@example.com','Excellent product! I\'ll return soon!','2010-12-20'

  • 注意，因为反斜杠被 MySQL 视为特殊字符，所以您需要通过在ESCAPED BY子句中以另一个反斜杠作为前缀来转义它的任何实例。

  • LINES:以下两个选项分别与线路的开始和终止方式有关:

    • STARTING BY ' character ':该选项定义用于表示行首的字符，从而表示新的表格行。与下一个选项相比，通常会跳过此选项的使用。

    • TERMINATED BY ' character ':该选项定义了表示一行结束的字符，从而表示表格行的结束。虽然它可以是任何东西，但是这个字符通常是换行符(\n)。在许多基于 Windows 的文件中，换行符通常表示为\r\n。

  • IGNORE number LINES:该选项告诉命令忽略第一个x行。当目标文件包含头文件信息时，这很有用。

  • [(SET column_name = expression ,...)]:如果目标文件中的字段数量与目标表格中的字段数量不匹配，您需要准确指定文件数据要填充哪些列。例如，如果包含销售信息的目标文件只包含四个字段(id、client_id、order_time和total_cost)，而不是前面示例中使用的六个字段(id、client_id、order_time、sub_total、shipping_cost和total_cost)，但目标表中所有六个字段都保留，则该命令必须这样编写:

LOAD DATA INFILE "sales.txt"
INTO TABLE sales (id, client_id, order_time, total_cost);

请记住，如果在表模式中将一个或几个缺失的列指定为NOT NULL，这种尝试可能会失败。在这种情况下，您需要为缺失的列指定DEFAULT值，或者进一步将数据文件处理成可接受的格式。

您还可以将列设置为变量，如当前时间戳。例如，假设 sales 表被修改为包含一个名为added_to_table的附加列:

LOAD DATA INFILE "sales.txt"
INTO TABLE sales (id, client_id, order_time, total_cost)
SET added_to_table = CURRENT_TIMESTAMP;

小费

如果您想在读入目标文件并插入表格时重新排列目标文件中字段的顺序，您可以通过[(column_name, ...)]选项重新排列顺序。

一个简单的数据导入示例

这个例子基于正在进行的销售主题。假设您想要导入一个名为productreviews.txt的文件，该文件包含以下信息:

'43','jason@example.com','I love the new Website!'
'44','areader@example.com','Why don\'t you sell shoes?'
'45','anotherreader@example.com','The search engine works great!'

标题恰当的目标表 product_reviews 由三个字段组成，它们的顺序(comment_id、email、comment)与在productreviews.txt中找到的信息相同:

LOAD DATA INFILE 'productreviews.txt' INTO TABLE product_reviews FIELDS
   TERMINATED BY ',' ENCLOSED BY '\" ESCAPED BY '\\'
    LINES TERMINATED BY '\n';

一旦导入完成，product_reviews表将如下所示:

+------------+---------------------------+---------------------------------+
| comment_id | email                     | comment                        |
+------------+---------------------------+--------------------------------+
|        43  | jason@example.com         | I love the new Website!        |
|        44  | areader@example.com       | Why don't you sell shoes?      |
|        45  | anotherreader@example.com | The search engine works great! |
+------------+---------------------------+--------------------------------+
选择目标数据库

您可能已经注意到，前面的示例引用了目标表，但是没有明确定义目标数据库。原因是 LOAD DATA INFILE 假设目标表位于当前选择的数据库中。或者，您可以通过在目标数据库前面加上数据库名称来指定目标数据库，如下所示:

LOAD DATA INFILE 'productreviews.txt' into table corporate.product_reviews;

如果在选择数据库之前执行LOAD DATA INFILE，或者没有在查询语法中明确指定数据库，将会出现错误。

安全和加载数据文件

使用 LOCAL 关键字，可以加载驻留在客户机上的文件。这个关键字将导致 MySQL 从客户端计算机检索文件。因为恶意管理员或用户可能通过操纵目标文件路径来利用此功能，所以在使用此功能时，您应该记住一些安全问题:

  • 如果不使用LOCAL，执行用户必须拥有FILE权限。这是由于允许用户读取驻留在服务器上的文件的潜在影响，该文件必须驻留在数据库目录中或者是全球可读的。

  • 要禁用LOAD DATA LOCAL INFILE，使用--local-infile=0选项启动 MySQL 守护进程。您可以稍后在 MySQL 客户端通过传递--local-infile=1选项来启用它。

使用 mysqlimport 导入数据

mysqlimport客户端只是LOAD DATA INFILE语句的命令行版本。其一般语法如下:

mysqlimport [options] database textfile1 [textfile2 ... textfileN]
有用的选项

在回顾任何示例之前，花点时间回顾一下许多最常用的mysqlimport选项:

  • --columns , -c:当目标文件中的字段数量或顺序与表格中的不匹配时，应使用此选项。例如，假设您正在插入以下目标文件，该文件将字段排序为id、order_id、sub_total、shipping_cost、total_cost和order_time:

    45633,12309,22.04,5.67,27.71,2010-12-19 01:13:42
942,12310,11.50,3.40,14.90,2010-12-19 01:15:12
7879,12311,95.99,15.00,110.99,2010-12-19 01:15:22

  • 然而，本章开头给出的sales表按以下顺序列出了字段:id、client_id、order_time、sub_total、shipping_cost和total_cost。通过包含此选项，您可以在解析过程中重新排列输入字段，以便将数据插入到正确的位置:

    --columns=id,order_id,sub_total,shipping_cost,total_cost,and order_time

  • --compress , -C:包含此选项压缩客户端和服务器之间流动的数据，假设两者都支持压缩。如果加载的目标文件与数据库不在同一个服务器上，此选项最有效。

  • --debug , -# :该选项用于在调试时创建跟踪文件。

  • --delete , -d:该选项在导入目标文件的数据之前，删除目标表格的内容。

  • --fields-terminated-by= , --fields-enclosed-by= , --fields-optionally-enclosed-by= , --fields-escaped-by= :这四个选项决定了mysqlimport’在解析过程中如何识别字段和行的行为。有关完整的介绍，请参阅本章前面的“使用 LOAD DATA INFILE 导入数据”一节。

  • --force , -f:包含此选项会导致mysqlimport继续执行，即使执行过程中出现错误。

  • --help , -? :包含此选项会生成一个简短的帮助文件和本节讨论的选项的综合列表。

  • --host , -h:该选项指定目标数据库的服务器位置。默认值为 localhost。

  • --ignore , -i:该选项使mysqlimport忽略目标文件中与表中已有行共享相同主键或唯一键的任何行。

  • --ignore-lines=n:该选项告诉mysqlimport忽略目标文件的第一个n行。当目标文件包含应该忽略的头文件信息时，这很有用。

  • --lines-terminated-by= :该选项决定了mysqlimport将如何识别文件中的每一个单独的行。有关完整的介绍，请参阅本章前面的“使用 LOAD DATA INFILE 导入数据”一节。

  • --lock-tables , -l:该选项在mysqlimport执行期间写锁定目标数据库中的所有表。

  • --local , -L:该选项指定目标文件位于客户端。默认情况下，假设该文件位于数据库服务器上；因此，如果您正在远程执行这个命令，并且还没有将文件上传到服务器，那么您需要包含这个选项。

  • --low-priority:该选项延迟mysqlimport的执行，直到没有其他客户端从表中读取。

  • --password=your_password、-pyour_password:该选项用于指定您的认证凭证的密码部分。如果此选项的your_password部分被省略，您将被提示输入密码。

  • --port , -P:如果目标 MySQL 服务器运行在非标准端口上(MySQL 的标准端口是 3306)，需要用这个选项指定那个端口值。

  • --replace , -r:该选项使mysqlimport覆盖目标文件中与表中已有行共享相同主键或唯一键的任何行。

  • --silent , -s:该选项告诉mysqlimport只输出错误信息。

  • --socket , -S:如果在 MySQL 服务器启动时声明了一个非默认的 socket 文件，那么应该包含这个选项。

  • --ssl:该选项指定连接应该使用 SSL。这将与此处未列出的其他几个选项结合使用。参见第二十九章了解更多关于 SSL 和用于配置该功能的各种选项的信息。

  • --user , -u:默认情况下，mysqlimport将执行系统用户的名称/主机组合与mysql权限表进行比较，确保执行用户拥有足够的权限来执行所请求的操作。因为在另一个用户的伪装下执行这样的过程通常很有用，所以您可以用这个选项指定凭证的“用户”组件。

  • --verbose , -v:该选项使mysqlimport输出大量与其行为相关的潜在有用信息。

  • --version , -V:该选项使mysqlimport输出版本信息并退出。

考虑到这些选项中的一些，下面的mysqlimport示例说明了一个场景，该场景涉及驻留在公司会计工作站上的库存审计信息的更新:

%>mysqlimport -h intranet.example.com -u accounting -p --replace \
> --compress --local company c:\audit\inventory.txt

该命令将本地文本文件(c:\audit\inventory.txt)中的数据压缩并传输到位于company数据库中的表格inventory。注意，mysqlimport从每个文本文件中去掉扩展名，并使用结果名称作为导入文本文件内容的表。

编写 mysqlimport 脚本

几年前，我参与了一家制药公司的企业网站的创建，该网站允许买家浏览大约 10，000 种产品的描述和定价信息。这些信息保存在大型机上，数据定期同步到 web 服务器上的 MySQL 数据库。为了实现这一点，在机器之间创建了一个单向信任，以及两个 shell 脚本。第一个脚本位于大型机上，负责从大型机上转储数据(以分隔格式)，然后通过 sftp 将该数据文件推送到 web 服务器。第二个脚本位于 web 服务器上，负责执行mysqlimport，将这个文件加载到 MySQL 数据库。这个脚本创建起来很简单，如下所示:

#!/bin/sh
/usr/local/mysql/bin/mysqlimport --delete --silent \
--fields-terminated-by='\t' --lines-terminated-by='\n' \
products /ftp/uploads/products.txt

为了将所涉及的逻辑保持在最低限度，每天晚上都要对整个大型机数据库进行完全转储，并且在开始导入之前创建一个新的空 MySQL 表。该表可能有不同的名称，但定义相同。当导入完成并通过验证后，旧表被删除，新表在单个事务中被重命名。这确保了添加所有新产品，更新现有产品信息以反映变化，并且删除任何被删除的产品。为了防止通过命令行传递凭证，创建了一个名为productupdate的系统用户，并在用户的主目录中放置了一个my.cnf文件，如下所示:

[client]
host=localhost
user=productupdate
password=secret

该文件的权限和所有权被更改，将所有者设置为mysql，只允许mysql用户读取该文件。最后一步是向productupdate用户的crontab添加必要的信息，?? 会在每晚凌晨 2 点执行脚本。系统从第一天开始就完美运行。

用 PHP 加载表格数据

出于安全原因，ISP 通常不允许使用LOAD DATA INFILE，以及 MySQL 的许多打包客户端，如 mysqlimport。然而，这样的限制并不一定意味着您在导入数据时运气不好；您可以使用 PHP 脚本模仿LOAD DATA INFILE和mysqlimport功能。下面的脚本使用 PHP 的文件处理功能和一个名为fgetcsv()的便捷函数来打开和解析本章开头的分隔销售数据:

<?php
    // Connect to the MySQL server and select the corporate database
    $mysqli = new mysqli("localhost","someuser","secret","corporate");

    // Open and parse the sales.csv file
    $fh = fopen("sales.csv", "r");

    while ($fields = fgetcsv($fh, 1000, ","))
    {
        $id = $ fields[0];
        $client_id = $fields[1];
        $order_time = $fields[2];
        $sub_total = $fields[3];
        $shipping_cost = $fields[4];
        $total_cost = $fields[5];

        // Insert the data into the sales table
        $query = "INSERT INTO sales SET id='$id',
            client_id='$client_id', order_time='$order_time',
            sub_total='$sub_total', shipping_cost='$shipping_cost',
            total_cost='$total_cost'";

        $result = $mysqli->query($query);
    }

    fclose($fh);
    $mysqli->close();
?>

请记住，在完成插入特别大的数据集之前，此类脚本的执行可能会超时。如果您认为可能是这种情况，请在脚本的开头设置 PHP 的max_execution_time配置指令。或者，考虑使用 PHP、Perl 或其他解决方案从命令行完成这项工作。PHP-CLI 版本默认max_execution_time为 0，因此没有超时。来自文件的输入应被视为任何其他输入，并在使用前进行清理。

下一节将切换数据流的方向，解释如何将数据从 MySQL 导出到其他格式。

导出数据

随着您的计算环境变得越来越复杂，您可能需要在各种不同的系统和应用之间共享数据。有时你无法从一个中心来源中挑选这些信息；相反，它必须不断地从数据库中检索，为转换做准备，并最终转换成目标可识别的格式。本节向您展示如何使用 SQL 语句SELECT INTO OUTFILE轻松导出 MySQL 数据。

注意

另一个常用的数据导出工具是mysqldump.,虽然它的官方目的是用于数据备份，但它的第二个目的是作为一个创建数据导出文件的伟大工具。

选择到输出文件

SQL 语句实际上是查询的变体。当您希望将查询输出定向到文本文件时，可以使用它。然后，可以通过电子表格应用打开该文件，或者将其导入另一个数据库，如 Microsoft Access、Oracle 或任何其他支持划界的软件。其一般语法格式如下:

SELECT [SELECT OPTIONS] INTO OUTFILE filename
  EXPORT_OPTIONS
  FROM tables [ADDITIONAL SELECT OPTIONS]

以下列表总结了关键选项:

  • OUTFILE:选择该选项会将查询结果输出到文本文件中。查询结果的格式取决于导出选项的设置方式。这些选项介绍如下。

  • DUMPFILE:选择这个选项而不是OUTFILE会导致查询结果被写成单行，省略列或行的终止。这在导出图形或 Word 文件等二进制数据时非常有用。请记住，在导出二进制文件时不能选择 OUTFILE，否则文件将会损坏。另外，请注意，转储文件查询必须以单行为目标；组合两个二进制文件的输出没有任何意义，如果您尝试这样做，将会返回一个错误。具体来说，返回的错误是，“结果由多行组成。”

  • EXPORT OPTIONS:导出选项决定了如何在输出文件中分隔表格字段和行。它们的语法和规则与本章前面介绍的LOAD DATA INFILE中使用的完全匹配。与其重复这些信息，请参阅前面的“用加载数据导入文件导入数据”一节以获得完整的论文。

使用技巧

关于SELECT INTO OUTFILE的使用，有几个事项值得注意:

  • 如果未指定目标文件路径，则使用当前数据库的目录。

  • 执行用户必须拥有目标表的选择权限(SELECT_PRIV)。此外，用户必须拥有文件权限，因为该查询将导致文件被写入服务器。

  • 如果指定了目标文件路径，MySQL 守护进程所有者必须拥有足够的权限来写入目标目录。

  • 这个过程使目标文件完全可读和可写，这是一个意想不到的副作用。因此，如果您正在编写备份过程的脚本，您可能希望在查询完成后以编程方式更改文件权限。

  • 如果目标文本文件已经存在，查询将失败。

  • 如果目标文本文件是转储文件，则不能包含导出选项。

一个简单的数据导出示例

假设您要将 2017 年 12 月的销售数据导出到由换行符分隔的行组成的制表符分隔的文本文件中:

SELECT * INTO OUTFILE "/backup/corporate/sales/1217.txt"
  FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
  FROM corporate.sales
  WHERE MONTH(order_time) = '12' AND YEAR(order_time) = '2017';

这里使用的目录分隔符是 Linux/Unix stile。在基于 Windows 的系统上，应该使用反斜杠。此外，基于 Windows 的系统上的行结尾是\r\n，而不是上面示例中使用的\n。假设执行用户拥有在corporate数据库中找到的sales表的SELECT特权，并且 MySQL 守护进程所有者可以写入/backup/corporate/sales/目录，那么将创建文件1217.txt，其中写入以下数据:

12309  45633  2010-12-19  01:13:42  22.04  5.67    27.71
12310  942    2010-12-19  01:15:12  11.50  3.40    14.90
12311  7879   2010-12-19  01:15:22  95.99  15.00   110.99
12312  55521  2010-12-19  01:30:45  10.75  3.00    13.75

请注意，每列之间的间距不是由空格组成的，而是由制表符(\t)引起的。此外，每行的结尾都有一个不可见的换行符(\n)。

将 MySQL 数据导出到 Microsoft Excel

当然，将数据输出到文本文件本身除了将数据转换成不同的格式之外，并不能完成任何事情。那么你如何处理这些数据呢？例如，假设营销部门的员工想要在最近的假日销售活动和最近的销售增长之间进行比较。为此，他们需要 12 月份的销售数据。为了筛选数据，他们希望以 Excel 格式提供。因为 Excel 可以将带分隔符的文本文件转换为电子表格格式，所以您可以执行以下查询:

SELECT * INTO OUTFILE "/analysis/sales/1217.xls"
   FIELDS TERMINATED BY '\t', LINES TERMINATED BY '\n' FROM corporate.sales
   WHERE MONTH(order_time) = '12' YEAR(order_time) = '2017';

请注意，创建的文件是一个制表符分隔的值文件(TSV)。可以使用 tsv 或 xls 作为文件扩展名，Excel 可以同时打开这两种文件。然后，通过位于公司内部网的预定义文件夹检索该文件，并在 Microsoft Excel 中打开。

如第二十四章所述。MySQL 附带了两个用于导出数据的客户端。这些是mysqldump和mysqlhotcopy。Mysql dump 是一个数据库备份应用，可以将整个数据库转储到一个文件中。该文件的内容将是一系列 SQL 命令，可用于重新创建转储时的数据库。使用mysqldump命令的语法如下:

$ mysqldump -u <user> -p <database? >database.sql

或者，您可能想使用mysqlhotcopy命令。它只支持 MyISAM 和归档表，其工作方式是将表刷新到磁盘，并在文件系统中执行文件的复制。这是一种非常快速的复制表或数据库的方法，但是它只能在文件所在的服务器上完成。相比之下，mysqldump 可用于创建远程数据库的数据库转储。mysqlhotcopy 的语法是:

$ mysqlhotcopy db_name [/path/to/new_directory]

摘要

MySQL 的数据导入和导出实用程序提供了强大的解决方案，可以将数据导入和导出 MySQL 数据库。有效地使用它们意味着维护噩梦和琐事之间的区别。

这本书到此结束。如果，或者说，当你需要更多关于 PHP 和 MySQL 的信息或帮助时，你会发现自己在寻找答案和例子。PHP 和 MySQL 在线文档都是技术文档和示例的重要来源。许多现代文本编辑器包括代码完成和对函数和参数的快速引用。找一个适合你的风格和预算的编辑器。他们中的许多人提供免费版本和基于支持和升级的订阅版本。

如果你有问题，我强烈推荐你当地的 PHP Meetup 或者其他用户组。它们存在于世界各地，提供了一个分享知识的好机会。像 GitHub ( https://github.com )和 Packagist ( https://packagist.org )这样的在线代码共享服务是搜索示例代码和共享您自己的代码的好地方。

祝你好运！

avatar
文章
阅读
粉丝