Mysql入门系列：如何处理MYSQL查询

; ; 6.6 处理查询 ; 我们已经知道了如何开始和结束与服务器的会话，现在应该看看如何控制会话。本节介绍了如何与服务器通信以处理查询。执行的每个查询应包括以下几步： ; 1) 构造查询。查询的构造取决于查询的内容—特别要看是否含有二进制数据。 ; 2) 通过将查询发送到服务器执行来发布查询。 ; 3) 处理查询结果。这取决于发布查询的类型。例如， SELECT 语句返回数据行等待处理，INSERT 语句就不这样。构造查询的一个要素就是使用哪个函数将查询发送到服务器。较通用的发布查询例程是mysql_ real _ query ( )。该例程给查询提供了一个计数串（字符串加上长度）。必须了解查询串的长度，并将它们连同串本身一起传递给mysql_real_query() 。因为查询是一个计数的字符串，所以它的内容可能是任何东西，其中包括二进制数据或者空字节。查询不能是空终结串。另一个发布查询的函数， mysql_ query ( )，在查询字符串允许的内容上有更多的限制，但更容易使用一些。传递到mysql_query() 的查询应该是空终结串，这说明查询内部不能含有空字节（查询里含有空字节会导致错误地中断，这比实际的查询内容要短）。一般说来，如果查询包含任意的二进制数据，就可能包含空字节，因此不要使用mysql_ query( )。另一方面，当处理空终结串时，使用熟悉的标准C 库字符串函数构造查询是很耗费资源的，例如strcpy ( )和sprintf( )。 ; 构造查询的另一个要素就是是否要执行溢出字符的操作。如果在构造查询时使用含有二进制数据或者其他复杂字符的值时，如引号、反斜线等，就需要使用这个操作。这些将在 ; 6.8.2节“对查询中有疑问的数据进行编码”中讨论。 ; 下面是处理查询的简单轮廓： mysql_query() 和mysql_real_query() 的查询成功都会返回零值，查询失败返回非零值。查询成功指服务器认为该查询有效并接受，而且能够执行，并不是指有关该查询结果。例如，它不是指SELECT 查询所选择的行，或DELETE 语句所删除的行。检查查询的实际结果要包括其他的处理。 ; 查询失败可能有多种原因，有一些常见的原因如下： ; ■ 含有语法错误。 ; ■ 语义上是非法的—例如涉及对表中不存在的列的查询。 ; ■ 没有足够的权利访问查询所引用的数据。 ; 查询可以分成两大类：不返回结果的查询和返回结果的查询。INSERT、DELETE和UPDATE等语句属于“不返回结果”类的查询，即使对修改数据库的查询，它们也不返回任何行。可返回的唯一信息就是有关受作用的行数。SELECT 语句和SHOW 语句属于“返回结果”类的查询；发布这些语句的目的就是要返回某些信息。返回数据的查询所生成的行集合称为结果集，在MySQL中表示为MYSQL_RES 数据类型，这是一个包含行的数据值及有关这些值的元数据（如列名和数据值的长度）的结构。空的结果集（就是包含零行的结果）要与“没有结果”区分开。 ; 6.6.1处理不返回结果集的查询 ; 处理不返回结果集的查询，用mysql_query() 或mysql_real_query() 发布查询。如果查询成功，可以通过调用mysql_ a ffected_rows() 找出有多少行需要插入、删除或修改。下面的样例说明如何处理不返回结果集的查询： 请注意在打印时mysql_ a ffected_rows() 的结果是如何转换为unsigned long 类型的，这个函数返回一个my_ulonglong 类型的值，但在一些系统上无法直接打印这个类型的值（例如，笔者观察到它可在FreeBSD 下工作，但不能在Solaris 下工作）。把值转换为unsigned long 类型并使用‘% l u’打印格式可以解决这个问题。同样也要考虑返回my_ulonglong 值的其他函数，如mysql_num_rows() 和mysql_ insert _ id ( )。如果想使客户机程序能跨系统地移植，就要谨记这一点。 ; mysql_ rows _ affected() 返回查询所作用的行数，但是“受作用的行”的含义取决于查询的类型。对于INSERT、DELETE 和UPDATE，是指插入、删除或者更新的行数，也就是MySQL实际修改的行数。如果行的内容与所要更新的内容相同，则MySQL就不再更新行。这就是说虽然可能选择行来更新（通过UPDATE 语句的WHERE 子句），但实际上该行可能并未改变。 ; 对于UPDATE，“受作用的行”的意义实际上是个争论点，因为人们想把它当成“被匹配的行”—即选择要更新的行数，即使更新操作实际上并未改变其中的值也是如此。如果应用程序需要这个信息， 则当与服务器连接时可以用它来请求以实现这个功能。将CLIENT_FOUND_ROWS 的flags 值传递给mysql_ real _ connect( )。也可以将CLIENT _ FOUND _ROWS 作为flags 参数传递给do _ connect ( )；它将把值传递给mysql_ real _ connect( )。 ; 6.6.2 处理返回结果集的查询 ; 通过调用mysql_query() 和mysql_real_query() 发布查询之后，返回数据的查询以结果集形式进行。在MySQL中实现它非常重要， SELECT 不是返回行的唯一语句， SHOW、DESCRIBE 和EXPLAIN 都需要返回行。对所有这些语句，都必须在发布查询后执行另外的处理行操作。 ; 处理结果集包括下面几个步骤： ; ■ 通过调用mysql_store_result() 或mysql_use_result() 产生结果集。这些函数如果成功则返回MYSQL_RES 指针，失败则返回N U LL。稍后我们将查看mysql_store_result() 与mysql_use_result() 的不同，以及选择其中一个而不选另一个时的情况。我们的样例使用mysql_ store _ result( )，它能立即从服务器返回行，并将它们存储到客户机中。 ; ■ 对结果集的每一行调用mysql_ fetch _ rows ( )。这个函数返回MYSQL_ROW 值，它是一个指向字符串数组的指针，字符串数组表示行中每列的值。要根据应用程序对行进行操作。可以只打印出列值，执行有关的统计计算，或者做些其他操作。当结果集中不再有行时， mysql_fetch_rows() 返回NULL。 ; ■ 处理结果集时，调用mysql_free_result() 释放所使用的内存。如果忽略了这一点，则应用程序就会泄露出内存（对于长期运行的应用程序，适当地解决结果集是极其重要的；否则，会注意到系统将由一些过程所取代，这些过程消耗着经常增长的系统资源量）。 ; 下面的样例轮廓介绍了如何处理返回结果集的查询： 我们通过调用函数process_result_set() 来处理每一行，这里有个窍门，因为我们并没有定义这个函数，所以需要这样做。通常，结果的处理集函数是基于下面的循环： 从mysql_fetch_row() 返回的MYSQL_ROW 值是一个指向数值数组的指针，因此，访问每个值就是访问row[i]，这里i 的范围是从0到该行的列数减1。这里有几个关于MYSQL_ROW 数据类型的要点需要注意： ; ■ MYSQL_ROW 是一个指针类型，因此，必须声明类型变量为MYSQL_ROW row，而不是MYSQL_ROW *row。 ; ■ MYSQL_ROW 数组中的字符串是空终结的。但是，列可能含有二进制数据，这样，数据中就可能含有空字节，因此，不应该把值看成是空终结的。由列的长度可知列值有多长。 ; ■ 所有数据类型的值都是作为字符串返回的，即使是数字型的也是如此。如果需要该值为数字型，就必须自己对该字符串进行转换。 ; ■ 在MYSQL_ROW 数组中，NULL 指针代表NULL，除非声明列为NOT NULL，否则应该经常检查列值是否为NULL 指针。 ; 应用程序可以利用每行的内容做任何想做的事，为了举例说明这一点，我们只打印由制表符隔开列值的行，为此还需要另外一个函数， mysql_num_fields() ,它来自于客户机库；这个函数告知我们该行包括多少个值（列）。 ; 下面就是process_result_set() 的代码： process_result_set() 以制表符分隔的形式打印每一行（将NULL值显示为单词“NULL”），它跟在被检索的行计数的后面， 该计数通过调用mysql_num_rows() 来计算。像mysql_ affected_rows() 一样，mysql_num_rows() 返回my_ulonglong 值，因此，将值转换为unsigned long 型，并用‘% l u’ 格式打印。提取行的循环紧接在一个错误检验的后面，如果要用mysql_store_result() 创建结果集， ; mysql_fetch_row() 返回的NULL值通常意味着“不再有行”。然而，如果用mysql_ use _ result( )创建结果集，则mysql_fetch_row() 返回的NULL 值通常意味着“不再有行”或者发生了错误。无论怎样创建结果集，这个测试只允许process_result_set() 检测错误。 ; process_result_set() 的这个版本是打印列值要求条件最低的方法，每种方法都有一定的缺点，例如假设执行下面的查询： 我们可以通过提供一些信息如列标签，及通过使这些值垂直排列，而使输出结果漂亮一点。为此，我们需要标签和每列所需的最宽的值。这个信息是有效的，但不是列数据值的一部分，而是结果集的元数据的一部分（有关数据的数据）。简单归纳了一下查询处理程序后，我们将在6 . 6 . 6节“使用结果集元数据”中给出较漂亮的显示格式。 ; 打印二进制数据 ; 对包含可能含有空字节的二进制数据的列值，使用‘ % s’printf() 格式标识符不能将它正确地打印； printf() 希望一个空终结串，并且直到第一个空字节才打印列值。对于二进制数据，最好用列的长度，以便打印完整的值，如可以用fwrite() 或putc( )。 ; 6.6.3 通用目标查询处理程序 ; 前面介绍的处理查询样例应用了语句是否应该返回一些数据的知识来编写的。这是可能的，因为查询固定在代码内部：使用INSERT 语句时，它不返回结果，使用SHOW TABLES语句时，才返回结果。 ; 然而，不可能始终知道查询用的是哪一种语句，例如，如果执行一个从键盘键入或来源于文件的查询，则它可能是任何的语句。不可能提前知道它是否会返回行。当然不想对查询做语法分析来决定它是哪类语句，总之，并不像看上去那样简单。只看第一个单词是不够的，因为查询也可能以注释语句开始，例如： ; /* comment * / SELECT ; 幸运的是不必过早地知道查询类型就能够正确地处理它。用MySQLC API 可编写一个能很好地处理任何类型语句的通用目标查询处理程序，无论它是否会返回结果。在编写查询处理程序的代码之前，让我们简述一下它是如何工作的： ; ■ 发布查询，如果失败，则结束。 ; ■ 如果查询成功，调用mysql_store_result() 从服务器检索行，并创建结果集。 ; ■ 如果mysql_store_result() 失败，则查询不返回结果集，或者在检索这个结果集时发生错误。可以通过把连接处理程序传递到mysql_field_count() 中，并检测其值来区别这两种情况，如下： ; ■ 如果mysql_field_count() 非零，说明有错误，因为查询应该返回结果集，但却没有。这种情况发生有多种原因。例如：结果集可能太大，内存分配失败，或者在提取行时客户机和服务器之间发生网络中断。 ; 这种过程稍微有点复杂之处就在于， MySQL3.22.24 之前的早期版本中不存在mysql_ field _ count( )，它们使用的是mysql_ num _ fields ( )。为编写MySQL任何版本都能运行的程序，在调用mysql_field_count() 的文件中都包含下面的代码块： 这就将对mysql_field_count() 的一些调用看作是比MySQL3.22.24 更早版本中的mysql_num_fields() 的调用。 ; ■ 如果mysql_field_count() 返回0，就意味着查询不返回结果（这说明查询是类似于INSERT、DELETE、或UPDATE 的语句）。 ; ■ 如果mysql_store_result() 成功，查询返回一个结果集，通过调用mysql_fetch_row() 来处理行，直到它返回NULL 为止。 ; 下面的列表说明了处理任意查询的函数，给出了连接处理程序和空终结查询字符串： 6.6.4 可选择的查询处理方法 ; process_query() 的这个版本有三个特性： ; ■ 用mysql_query() 发布查询。 ; ■ 用mysql_store_query() 检索结果集。 ; ■ 没有得到结果集时，用mysql_field_count() 把错误事件和不需要的结果集区别开来。针对查询处理的这些特点，有如下三种方法： ; ■ 可以用计数查询字符串和mysql_ real _ query( )，而不使用空终结查询字符串和mysql_ query( )。 ; ■ 可以通过调用mysql_use_result() 而不是调用mysql_store_result() 来创建结果集。 ; ■ 可以调用mysql_error() 而不是调用mysql_field_count() 来确定结果集是检索失败还是仅仅没有设置检索。 ; 可用以上部分或全部方法代替process _ query( )。以下是一个process_real_query() 函数，它与process_query() 类似，但使用了所有三种方法： 6.6.5 mysql_store_result() 与mysql_use_result() 的比较 ; 函数mysql_store_result() 与mysql_use_result() 类似，它们都有连接处理程序参数，并返回结果集。但实际上两者间的区别还是很大的。两个函数之间首要的区别在于从服务器上检索结果集的行。当调用时， mysql_store_result() 立即检索所有的行，而mysql_use_result() 启动查询，但实际上并未获取任何行， mysql_store_result() 假设随后会调用mysql_ fetch _ row( )检索记录。这些行检索的不同方法引起两者在其他方面的不同。本节加以比较，以便了解如何选择最适合应用程序的方法。 ; 当mysql_store_result() 从服务器上检索结果集时，就提取了行，并为之分配内存，存储到客户机中，随后调用mysql_fetch_row() 就再也不会返回错误，因为它仅仅是把行脱离了已经保留结果集的数据结构。mysql_fetch_row() 返回NULL 始终表示已经到达结果集的末端。相反，mysql_use_result() 本身不检索任何行，而只是启动一个逐行的检索，就是说必须对每行调用mysql_fetch_row() 来自己完成。既然如此，虽然正常情况下， mysql_ fetch _ row( )返回NULL 仍然表示此时已到达结果集的末端，但也可能表示在与服务器通信时发生错误。可通过调用mysql_errno() 和mysql_error() 将两者区分开来。 ; 与mysql_use_result() 相比，mysql_store_result() 有着较高的内存和处理需求，因为是在客户机上维护整个结果集，所以内存分配和创建数据结构的耗费是非常巨大的，要冒着溢出内存的危险来检索大型结果集，如果想一次检索多个行，可用mysql_ use _result( )。mysql_use_result() 有着较低的内存需求，因为只需给每次处理的单行分配足够的空间。这样速度就较快，因为不必为结果集建立复杂的数据结构。另一方面， mysql_use_result() 把较大的负载加到了服务器上，它必须保留结果集中的行，直到客户机看起来适合检索所有的行。这就使某些类型的客户机程序不适用mysql_ use _ result( )： ; ■ 在用户的请求下提前逐行进行的交互式客户机程序（不必仅仅因为用户需要喝杯咖啡而让服务器等待发送下一行）。 ; ■ 在行检索之间做了许多处理的客户机程序。在所有这些情况下，客户机程序都不能很快检索结果集的所有行，它限制了服务器，并对其他客户机程序产生负面的影响，因为检索数据的表在查询过程中是读锁定的。要更新表的客户机或要插入行的任何客户机程序都被阻塞。 ; 偏移由mysql_store_result() 引起的额外内存需求对一次访问整个结果集带来相当的好处。结果集中的所有行都是有效的，因此，可以任意访问： mysql_ data _ seek( )、mysql_ rowseek( )和mysql_row_tell() 函数允许以任意次序访问行。而mysql_use_result() 只能以mysql_fetch_row() 检索的顺序访问行。如果想要以任意次序而不是从服务器返回的次序来处理行，就必须使用mysql_ store _ result( )。例如，如果允许用户来回地浏览查询所选的行，最好使用mysql_ store _ result( )。 ; 使用mysql_store_result() 可以获得在使用mysql_use_result() 时是无效的某些类型的列信息。通过调用mysql_num_rows() 来获得结果集的行数，每列中的这些值的最大宽度值存储在MYSQL_FIELD 列信息结构的max_width 成员中。使用mysql_ use _ result( )，直到提取完所有的行，mysql_num_rows() 才会返回正确值，而且max_width 无效，因为只有在每行的数据都显示后才能计算。 ; 由于mysql_use_result() 比mysql_store_result() 执行更少的操作，所以mysql_ use _ result( )就强加了一个mysql_store_result() 没有的需求：即客户机对结果集中的每一行都必须调用mysql_ fetch _ row( )，否则，结果集中剩余的记录就会成为下一个查询结果集中的一部分，并且发生“不同步”的错误。这种情形在使用mysql_store_result() 时不会发生，因为当函数返回时，所有的行就已被获取。事实上，使用mysql_store_result() 就不必再自己调用mysql_ fetch _ row( )。对于所有感兴趣的事情就是是否得到一个非空的结果，而不是结果所包含的内容的查询来说，它是很有用的。例如，要知道表my_tbl 是否存在，可以执行下面的查询： ; SHOW TABLES LIKE 'my_tb1' ; 如果在调用mysql_store_result() 之后，mysql_num_rows() 的值为非零，这个表就存在，就不必再调用mysql_fetch_row() （当然仍需调用mysql_ free _ result( )）。如果要提供最大的灵活性，就给用户选择使用任一结果集处理方法的选项。mysql和mysqldump 是执行这个操作的两个程序，缺省时，使用mysql_ store _ result( )，但是如果指定--quick 选项，则使用mysql_ use _ result( )。 ; 6.6.6 使用结果集元数据 ; 结果集不仅包括数据行的列值，而且还包括数据信息，这些信息成为元数据结果集，包括： ; ■ 结果集中的行数和列数，通过调用mysql_num_rows() 和mysql_num_fields() 实现。 ; ■ 行中每列值的长度，通过调用mysql_fetch_lengths() 实现。 ; ■ 有关每列的信息， 例如列名和类型、每列值的最大宽度和列来源的表等。 ; MYSQL_FIELD 结构存储这些信息，通过调用mysql_fetch_fields() 来获得它。附录F详细地描述了MYSQL_FIELD 结构，并列出了提供访问列信息的所有函数。元数据的有效性部分决定于结果集的处理方法，如在上节中提到的，如果要使用行计数或者列长度的最大值，就必须用mysql_store_result() 而不是mysql_use_result() 创建结果集。结果集元数据对确定有关如何处理结果集非常有帮助： ; ■ 列名和宽度信息对漂亮地生成带有列标题并垂直排列的格式化输出是非常有用的。 ; ■ 使用列计数来确定处理数据行的连续列值的循环所迭代的次数。如果要分配取决于结果集中已知的行数或列数的数据结构，就可以使用行或列计数。 ; ■ 可以确定列的数据类型。可以看出列是否是数字的，是否可能包括二进制数据等等。在前面的6.6.1节“处理返回结果集的查询”中，我们编写了从结果集的行中以制表符分隔的形式打印出结果的process_result_set() 程序。这对某些目的是很好的（例如要把数据输入到电子制表软件中），但对于可视化检查或打印输出，就不是一个漂亮的显示格式。回忆前面的process_result_set() 版本，产生过这样的输出： 让我们在每列加上标题和边框来对process_result_set() 做些修改，以生成表格式的输出。这种修正版看上去更美观，输出的结果是相同的，如下所示： 显示算法的基本要点是这样的： ; 1) 确定每列的显示宽度。 ; 2) 打印一列带有边框的列标题（由垂直竖线和前后的虚线分隔）。 ; 3) 打印结果集每行的值、带边框的列（由垂直竖线分隔），并垂直排列，除此之外，打印 ; 正确的数字，将NULL 值打印为单词“NULL”。 ; 4) 最后，打印检索的行的计数。该练习为结果集元数据的使用提供了一个很好的示范。为了显示所描述的输出，除了行所包含的数据值之外，我们还需了解许多有关结果集的内容。您可能想，“这个描述听起来与mysql显示的输出惊人地相似”。是的，欢迎把mysql源代码和修正版的process_result_set() 代码比较一下，它们是不同的，可以发现对同一问题使用两种方法是有指导作用的。 ; 首先，我们需要确定每列的显示宽度，下面列出如何做这件事情。可观察到这些计算完全基于结果集元数据，无论行值是什么，它们都没有引用： 列宽度通过结果集中列的MYSQL_FIELD 结构的迭代来计算，调用mysql_ fetch _ seek( )定位第一个结构，后续的mysql_fetch_field() 调用返回指向连续列的结构的指针。显示出来的列宽度是下面三个值中的最大值，其中每一个都取决于列信息结构中的元数据： ; ■ field - > name的长度，也就是列标题的长度。 ; ■ field - > max _ length，列中最长的数据值的长度。 ; ■ 如果列中可能包括NULL值，则为字符串“ NULL”的长度，field->flag 表明列是否包含NULL。请注意，已知要显示的列的宽度后，我们将这个值赋给max _ length，max_length 是从客户机库获取的结构中的一个成员。这种获取是允许的吗？或者MYSQL_FIELD 结构的内容应该为只读？一般来说，是“只读的”，但是MySQL分发包中的一些客户机程序以同样的方式改变了max_length 的值，因此，假设这也是正确的（如果更喜欢不改变max_length 值的方法，则分配一个unsigned int 值的数组，将计算的宽度存储到这个数组中）。显示宽度的计算包括一个说明，回想当使用mysql_use_result() 创建结果集时，max_length 没有意义。因为我们需要max_length 来确定列值的显示宽度，所以该算法的正确操作需要使用mysql_store_result() 产生的结果集( MYSQL _ FIELD结构的length 成员告知列值可以取得的最大值，如果使用mysql_store_result() 而不是mysql_ use _ result( )的话，这可能是个有用的工作环境)。 ; 一旦知道了列的宽度，就可以准备打印，处理标题很容易；对于给定的列，只需使用由field 指向的列信息结构，用已计算过的宽度打印出name 成员。 对于数据，我们对结果集中的行进行循环，在每次迭代时打印当前行的列值。从行中打印列值有些技巧，因为值可能是NULL，也可能代表一个数（无论哪种情况都如实打印）。列值的打印如下，这里row[i] 包括数据值和指向列信息的field 指针： 如果field->type 指明的列类型是数字型，如INT、FLOAT或者DECIMAL，那么宏IS _ NUM的值为真。显示该结果集的最终的代码如下所示。注意，因为我们需要多次打印虚线，所以这段代码封装在它自己的函数中，函数print_dashes() 是这样的: MySQL客户机库提供了访问列信息结构的几种方法，例如，前面样例的代码多次使用如下形式的循环访问这些结构： 然而，mysql_field_seek() 与mysql_fetch_field() 的结合是获得MYSQL_FIELD 结构的唯一途径，可在附录F 中查看mysql_fetch_field() 函数和mysql_fetch_field_direct() 函数，寻找其他获得列信息结构的方法。