Mysql入门系列：在MYSQL结果集上执行计算

Mysql入门系列：在MYSQL结果集上执行计算6.8其他主题本节包括几个主题，这些主题不完全适合于本章从client 1到client5的开发中的任一小 节的内容：在使用结果集元数据帮助验证这些数据适合于计算之后，使用结果集数据计算结果。如何处理很难插入到查询中的数据。如何处理图形数据。如何获得表结构的信息。常见的MySQL程序设计错误及如何避免。在结果集上执行计算迄今为止，我们集中而主要地使用了结果集元数据来打印行数据，但很明显，除打印之 外，还有需要使用数据做其他事情的时候。例如，计算基于数据值的统计信息，应用元数据 确保数据适合它们要满足的需求。哪种类型的需求？对于启动程序来说，可能要校验一下正要执行数字计算的列实际上是否包含着数字！下面的列表显示了一个简单函数summary _ stats (),它获取结果集和列索引，并产生列 值的汇总统计。该函数还列出缺少数值的数量，它是通过检查NULL来检测的。这些计算 包括两个数据所必须满足的需求，summary_stats()用结果集元数据来校验：指定的列必须存在(也就是说，列索引必须在结果集列值的范围内)。此列必须包括数字值。如果这些条件不满足，则summary_stats()只打印出错误消息并返回。代码如下：voidsummary stats (MYSQL_F%ES *res_seti unsigned int col nuuO亠jMYSQLFIELD*field；mysqlZhowrow;unsigned int n, missingj double vbIt sumsquares, van;/* verify data re quire flints *iif (Jtiysql num fields (res_set) type) f prirtt_error (ULLj column is not nunteric*); return;严 calculate suiwary statistics */ n = 0；missing * B;UI!I - 0；$um_square$ =尉mysqldatasaek (ressett ;while (ro# 二 ysql_fetcft_row (res_&etj I- NULL) 一if (rowcol_ruin WULLmissing+;elsevai = atof rowcol_numj; /* convert string to nufter sum +- vai;susi_squares += val * val; if (n -= Oprintf (uNo obaervatlonsn);elseprintf (Numbar of cbservatiofts： %luns a n);prifttf (MiSGing observations: %lun*, stiSing) printf (Sum:, sum);printf (Mean: %gnj $餘 f n);printf (Sum of squares %gnt suH_$quarG$); var = (n * sum_square5)、(sw * sum) f fn * (n - 1); printf (EVariance:切5爲 var);printf fStandard deviation: %gn sqrt (var);请注意在mysql_fetch_row()循环前面调用的mysql_ data _ seek()。为获得同样的结果 集，它允许多次调用summary _ stats()(假设要计算几列的统计值的话)。每次调用summary _ stats()都要“重新回到”到结果集的开始(这里假设mysql_store_result()创建结果集，如果 用mysql_use_result()创建结果集就只能按顺序处理行，而且只能处理一次)summary_stats() 是个相对简单的函数，但它给我们一个提示，就是如何编写一个比较复杂的计算程序，如两 个列的最小二乘回归或者标准统计，如t -检验。对查询中有疑问的数据进行编码包括引号、空值和反斜线的数据值，如果把它们插入到查询中，在执行查询时就会产生 一些问题。下面的讨论论述了这些难点，并介绍了解决的办法。假设要建造一个SELECT查 询，它基于由name指向的空终结串的内容：char queryf1024j;sprintf (qtiary BSELECT * FROM HFytbl WHERE name-s name);如果name的值类似于“OMalley, Brian，这时进行的查询就是非法的，因为引号在引 用的字符串里出现：SELECT * FROM jrayjbl WHERE naFie=:OMalley, Brianr需要特别注意这个引号，以便使服务器不将它解释为name的结尾。一种方法是在字符 串内使用双引号，这就是ANSI SQL约定。SQL支持这个约定，也允许引号在反斜线后使 用：SELECT * FflOM iay_tbl WHERE name=J1 Malley, BrianH SELECT * FROMWHEREMalley, Brian *另一个有问题之处是查询中任意二进制数据的使用，例如，在把图形存储到数据库这样 的应用程序中会发生这种情况。因为二进制数值含有一些字符，把它放到查询中是不安全的。 为了解决这个问题，可使用mysql_ escape _ string()，它可以对特殊字符进行编码，使 其在引用的字符串中可以使用。mysql_escape_string()认为的特殊字符是指空字符、单引号、双引号、反斜线、换行符、回车符和C ontrol - Z (最后一个在Windows语言环境中 出现)。什么时候使用mysql_escape_string()呢？最保险的回答是“始终”。然而，如果 确信数据的形式并且知道它是正确的一可能因为预先执行了确认检查一就不必编码了。例 如,如果处理电话号码的字符串，它完全由数字和短线组成，那么就不必调用mysql_ escape _ string( )了，否则还是要调用。mysql_escape_string()对有问题的字符进行编码是将它们转换为以反斜线开头的2个字符的序列。例如，空字符转换为 0：这里的0是可打印 的ASCII码0,而不是空。反斜线、单引号和双引号分别转换为 、和”。调用 mysql_escape_string()的过程如下：to l&n = ipysql escap string (to_str, from str, frollers);mysql_escape_string()对from_str进行编码，并把结果写入to _ str中，还添加了空终结 值，这样很方便，因为可以利用像strcpy()和strlen()这样的函数使用该结果串。from_str指 向包括将要编码的字符串的char缓冲区，这个字符串可能包含任何内容，其中包括二进制 数据。to_str指向一个存在的char缓冲区，在这个缓冲区里，可以写入编码的字符串；不 要传递未初始化的指针或NULL指针，希望由mysql_escape_string()分配空间。由to_str指 向的缓冲区的长度至少是(from_len*2)+1个字节(很可能from_str中的每个字符都需要用2 个字符来编码；额外的字节是空终结值)。from_len 和 to_len 都是 unsigned int 值，from_len 表示 from_str 中数据的长度；提供 这个长度是非常必要的，因为from_str可能包含空值字节，不能把它当作空终结串。从 mysql_escape_string()返回的to_len值是作为结果的编码字符串的实际长度，没有对空终结 值进行计数。当mysql_escape_string()返回时，to _ str中编码的结果就可看作是空终结串，因为 from_str中的空值都被编码为 05。为了重新编写构造SELECT的代码，使名称的值即使包含引号也能工作，我们进行下 面的操作：char query!104* *p；p = strcpy queryT SELECT * FROM my_tbl 删ERE name-r);p += strlen (p);p += mysql escape str讪gname, strlen (name);P = strq)y p, 齡:;打是的，这很难右如要简化一点，就要使用第二个缓冲区，如替换成下列内容:char(void) mysql_escape_string (buf 帕砒】strlen nanfe); sprintf (qu&ryj select * Hl伽WHERE 帕砒=爲訐笃 buf)；图像数据的处理 mysql_escape_string()的基本功能之一就是把图像数据加载到一个表中。本节介绍如何 进行这项工作(这个讨论也适用于二进制数据的其他形式)。假设想从文件中读取图像，并 将它们连同唯一的标识符存储到表中。BLOB类型对二进制数据来讲是个很好的选择，因 此可以使用下面的表说明：CREATE TABLE images(imaged I NT NOT NULL PRIMARY KEY,imagedata BLOB) 实际上，要想从文件中获取图像并放入images表，利用下面的函数load_image()可以 实现，给出一个标识符号码和一个指向包括这个图像数据的打开文件的指针：intloadimage (MYSQL *connT int idt FILE )charqueryf1024*100a1024*10; *p；unsigned intfrofltlen;intstatus;sprintf (query, INSERT INTO iinag&s VALUESid;p = quer + strln (quary):while (from_len - tread (buf, 1, sireof (buf), f) 0 /* dont overrun mxJ of quer/ biifferl */if (P + (2*fro_len) + 3 query + sizeof (query)print_arrDr (NULL, 新咖 tco big*); return (1);P +s mysql_escape string buft fromlen;(void) strcpy (p,-);status = mysql_query (conn, query J;return (status；load_image()不会分配非常大的查询缓冲区(10 0 K)，因此它只能处理相对较小的 图形。 在实际的应用程序中，可以根据图形文件的大小动态地分配缓冲区。处理从数据 库中恢复的图形数据(或任何二进制数据)并不像开始把它放入时那样问题重重，因为在变 量MYSQL_ROW中数据值的原始形式是有效的，通过调用mysql_ fetchength ()，这个长 度也是有效的。必须将值看作是计数串，而不是空终结串。获取表信息MySQL允许使用下面的查询获取有关表结构的信息(下面两者是等价的)：describe也L朴師总SHOW FIELDS FROM tbl rtflffle与SELECT相类似，两个语句都返回结果集。为了在表中找出有关列，所需做的就是 处理结果集中的行，从中获取有用的信息。例如，如果从mysql客户机上发布DESCRIBE images语句，就会返回这样的信息： Field ； Type Null ； Key ； Default ； Extra ；ijnage id : int(H)；:PFU :iuiafle_data blobVES |轉ULL丰 -.a Mi H * +*1!*4亠8斗 *如果从自己的客户机上执行同样的查询，可以得到相同的信息(没有边框)。如果只 想要单个列的信息，则使用如下这个查询：SHOW FIELDS FROM tbl _ name LIKE “col _ name”此查询会返回相同的列，但只是一行(如果列不存在就不返回行)。需要避免的客户机程序设计错误本节讨论一些常见的MySQLC API程序设计错误，以及如何避免其发生(这些问题 在MySQL邮件清单中会周期性地突然出现)。1错误1 使用未初始化的连接处理程序指针在本章的样例中，我们已经通过传 递NULL参数调用了 mysql_ in i t ()，这就是让它分配并且初始化MYSQL结构，然后返 回一个指针。另外一种方法是将指针传递到一个已有的MYSQL结构中。在这种情况下， mysql_init()会将结构初始化并返回一个指针，而不必自己分配结构。如果要使用第二种方 法,则要小心会出现一些微妙的问题。下面的讨论指出了需要注意的一些问题。如果将一个 指针传递给mysql_init()，它应该实际指向某些东西。看下面的代码段：main ()MYSQL *conn；isql_init (conn);A 这个问题是，mysql_init()得到了一个指针，但指针没有指向所知的任何地方。conn是 一个局部变量，因此在main()开始执行时它是一个能指向任何地方的未初始化的存储器， 这就是说mysql_init()将使用指针，并可在内存的一些任意区域滥写。如果幸运的话，conn 将指向您的程序地址空间的外部，这样，系统将立即终止，使您能尽早意识到代码中出现的 问题。如果不幸的话，conn将指向程序中以后才使用的一些数据的内部，直到再次使用那个 数据时才发现问题。因此实际出现问题的地方远比执行程序时出现的问题多，也更难捕捉到。 下面是一段有问题的代码：MYSQL *conn;main (mysal_init (conn):my$ql_real_connect (conn, mysqlqueryfconn, *SHOW DATABASESp;此时，conn是一个全局变量，因此在程序启动前，将它初始化为0 (就是NULL)。 mysql_init()遇到NULL参数，因此初始化并分配一个新的连接处理程序。只要将conn传递给需要非NULL连接处理程序的MySQLCAPI函数，系统就会崩溃。这些代码段的修改 就是确保conn有一个可知的值。例如，可以将它初始化到已经分配的MYSQL结构地址中 去：MYSQL connatructp *conn = &conn struct;9 V i|mysql_init (conn);然而，推荐的(较容易的！)解决方案仅仅是将NULL显式地传递给mysql_ init()， 让该函数分配MYSQL结构，并将返回值赋值给conn:MYSQL *conn;BAIccnn r(NULL);无论如何不要忘记检验mysql_init()的返回值，以确保它不是NULL。2错误2有效结果集检验的失败请记住检查希望得到的结果集的调用状态。下面的代码没有做到这一点：IIYSQL RES *res Set；VWMYSQl ROW row:*res set - mysqesult (conn); while (row = raysrow (res_wtH - NULL) /* process row */ 不幸地是，如果mysql_store_result()失败，res_set为NULL， while循环也不执行了， 应测试返回结果集函数的返回值，以确保实际上在进行工作。3错误3NULL列值引起的失败不要忘记检查mysql_fetch_row()返回的数组MYSQL_ROW中列值是否为NULL指 针。如果rowi为NULL，则在一些机器上，下面的代码就会引起崩溃：for (1 0; i 0)fputc td, stdout)!printf C1, rowEiJJ； fputc (W* stdout);该错误危害最大的部分是，有些printf()的版本很宽容地对NULL指针输出了“(null )”， 这就使错误很容易逃脱而没有把错误定位。如果把程序给了朋友，而他只有不太宽容printf() 版本，程序就会崩溃，您的朋友会认为您是个无用的程序员。循环应该写成下面这样：for i =明 1 ) fputc (*t1 J stdout);printf (%$*, rewi I- NULL 7 rowi:NULL/);fputc (*n stdout); -不需要检查列值是否为NULL的惟次是当已经从列信息结构确定IS _ NOT _NULL()为真时。4错误4 传递无意义的结果缓冲区需要您提供缓冲区的客户机库函数通常要使这些缓冲区真正存在，下面的代码违反了这 个规则：char= *gome st ring;char unsigned intlen - mysql_escape_strinQ (tostr, fromstr! strlen问题是什么呢？ to_str必须指向一个存在的缓冲区，而在这个样例中没有，因此，它指 向了随意的位置。不要向mysql_escape_string传递无意义的指针作为to_str参数，否则它 会恣意践踏内存。