{"id":420,"date":"2013-09-04T19:14:49","date_gmt":"2013-09-04T11:14:49","guid":{"rendered":"http:\/\/www.gpfeng.com\/?p=420"},"modified":"2013-09-04T19:17:35","modified_gmt":"2013-09-04T11:17:35","slug":"mysql-embedded-server-prepared-statement%e6%89%a7%e8%a1%8c%e5%88%86%e6%9e%90","status":"publish","type":"post","link":"http:\/\/www.gpfeng.com\/?p=420","title":{"rendered":"MySQL embedded server prepared statement\u6267\u884c\u5206\u6790"},"content":{"rendered":"

MySQL embedded server\u7b80\u4ecb<\/h3>\n

MySQL\u53ef\u4ee5\u4f5c\u4e3a\u4e00\u4e2aembedded server\u6267\u884c\uff0c\u8fd9\u65f6\u5b83\u5c31\u5145\u5f53\u4e86\u4e00\u4e2a\u4e0e\u5e94\u7528\u7d27\u5bc6\u8026\u5408\u7684\u8f7b\u91cf\u7ea7\u6570\u636e\u4e2d\u5fc3\uff0c\u9002\u5408\u8d44\u6e90\u6709\u9650\u3001\u5e94\u7528\u9700\u8981\u8fde\u63a5\u5f88\u5c11\uff08\u901a\u5e381\u4e2a\uff09\u7684\u573a\u666f\uff0c\u4f7f\u7528\u65b9\u4fbf\uff0c\u7f16\u8bd1\u540e\u5c31\u53ef\u4ee5\u968f\u5e94\u7528\u4e00\u8d77\u542f\u52a8\uff0c\u5177\u6709\u90e8\u7f72\u65b9\u4fbf\uff0c\u901f\u5ea6\u66f4\u5feb\u7684\u4f18\u70b9\uff0c\u5f53\u7136\u5b83\u6709\u5f88\u591a\u9650\u5236\uff0c\u5982\uff1a\u4e0d\u652f\u6301UDF\uff0c\u4e0d\u652f\u6301\u4e3b\u5907\u590d\u5236\uff0cInnoDB\u5c42\u4e0d\u652f\u6301\u591a\u4e2a\u8fde\u63a5\u7b49\uff0c\u66f4\u591a\u8bf7\u53c2\u8003 libmysqld, the Embedded MySQL Server Library<\/a>.<\/p>\n

\u56e0\u4e3aDRC\u56e2\u961f\u5728\u4f7f\u7528embedded server\u7684\u8fc7\u7a0b\u4e2d\u9047\u5230\u4e86\u6267\u884cprepared statment\u65f6\u5185\u5b58\u6cc4\u9732\u7684\u95ee\u9898\uff0c\u4e3a\u4e86\u5b9a\u4f4d\u5e76fix\u95ee\u9898\uff0c\u5206\u6790\u4e86\u8fd9\u90e8\u5206\u903b\u8f91
\n<\/p>\n

prepared statement API<\/h3>\n
\n\r\n\r\n
\u51fd\u6570<\/th>\n\u8bf4\u660e<\/th>\n<\/tr>\n<\/thead>\n
mysql_stmt_init<\/td>\n\u521d\u59cb\u5316statement<\/td>\n<\/tr>\n\r\n
mysql_stmt_prepare<\/td>\nprepare\u4e00\u6761SQL\u8bed\u53e5<\/td>\n<\/tr>\n\r\n
mysql_stmt_bind_param<\/td>\n\u7ed1\u5b9a\u4f20\u5165\u53d8\u91cf<\/td>\n<\/tr>\n\r\n
mysql_stmt_bind_result<\/td>\n\u7ed1\u5b9a\u7ed3\u679c\u53d8\u91cf<\/td>\n<\/tr>\n\r\n
mysql_stmt_execute<\/td>\n\u8bbe\u7f6e\u4e0d\u540c\u7684\u7ed1\u5b9a\u53d8\u91cf\u7684\u503c\uff0c\u53ef\u4ee5\u591a\u6b21\u6267\u884c<\/td>\n<\/tr>\n\r\n
mysql_stmt_restore_result<\/td>\n\u5b58\u50a8\u6267\u884c\u7ed3\u679c<\/td>\n<\/tr>\n\r\n
mysql_stmt_fetch<\/td>\n\u8bfb\u53d6\u4e00\u884c\u7ed3\u679c\u5230\u7ed1\u5b9a\u7ed3\u679c\u53d8\u91cf<\/td>\n<\/tr>\n\r\n
mysql_stmt_affected_rows<\/td>\n\u5bf9\u4e8einsert\/update\/delete\u8fd4\u56de\u5f71\u54cd\u7684\u6570\u636e\u6761\u6570<\/td>\n<\/tr>\n\r\n
mysql_stmt_num_rows<\/td>\n\u5bf9\u4e8eselect\u8fd4\u56de\u7ed3\u679c\u96c6\u5927\u5c0f<\/td>\n<\/tr>\n\r\n
mysql_stmt_free_result<\/td>\n\u6e05\u7a7a\u7ed3\u679c\u96c6<\/td>\n<\/tr>\n\r\n
mysql_stmt_close<\/td>\n\u5173\u95edstatement<\/td>\n<\/tr>\n<\/tbody><\/table><\/div>\n

embedded server\u6570\u636e\u4ea4\u4e92<\/h3>\n

\u76f8\u5bf9\u4e8eC\/S\u67b6\u6784\u7684MySQL server\uff0cembedded server\u662f\u8fd0\u884c\u4e8e\u5e94\u7528\u5185\u90e8\u7684\uff0c\u56e0\u6b64\u6570\u636e\u4f20\u8f93\u4e0d\u9700\u8981\u8d70\u7f51\u7edc\uff0c\u901a\u8fc7\u5185\u5b58\u62f7\u8d1d\u5c31\u53ef\u4ee5\u5b8c\u6210\uff0c\u4f46\u662f\u5728\u903b\u8f91\u4e0a\u8fd8\u662f\u7ef4\u6301C\/S\u7ed3\u6784\uff0clibmysqld\u4e2d\u91cd\u65b0\u5b9e\u73b0\u4e86Protocol\u7c7b\u7684\u4e00\u4e9b\u65b9\u6cd5\u5982\uff1aProtocol::send_fields\uff0c\u4ee5\u53canet_send_ok\uff0cnet_send_eof\uff0cnet_send_error_packet\uff0c\u6240\u6709\u8fd9\u4e9b\u64cd\u4f5c\u90fd\u662f\u4e9b\u5185\u5b58\u64cd\u4f5c\uff0c\u7ed3\u679c\u96c6\u6570\u636e\u5199\u5230THD\u4e2d\u4e3aembedded server\u589e\u52a0\u7684st_mysql_data\u7c7b\u578b\u7684\u94fe\u8868\u4e2d\uff1a(sql\/sql_class.h)<\/p>\n

\r\n#ifdef EMBEDDED_LIBRARY\r\n  struct st_mysql  *mysql;\r\n  unsigned long\t client_stmt_id;\r\n  unsigned long  client_param_count;\r\n  struct st_mysql_bind *client_params;\r\n  char *extra_data;\r\n  ulong extra_length;\r\n  struct st_mysql_data *cur_data;\r\n  struct st_mysql_data *first_data;\r\n  struct st_mysql_data **data_tail;\r\n  void clear_data_list();\r\n  struct st_mysql_data *alloc_new_dataset();\r\n  \/*\r\n    In embedded server it points to the statement that is processed\r\n    in the current query. We store some results directly in statement\r\n    fields then.\r\n  *\/\r\n  struct st_mysql_stmt *current_stmt;\r\n#endif\r\n<\/pre>\n
\u91cd\u8981\u6570\u636e\u7ed3\u6784<\/h3>\n
MYSQL_DATA\u662f\u4e00\u4e2a\u6570\u636e\u96c6\uff0c\u5176\u4e2dalloc\u8d1f\u8d23\u4e3aembedded_info\u4e2d\u7684fields_list\u5206\u914d\u5185\u5b58\uff0cdata\u4e3a\u7ed3\u679c\u6570\u636e\u96c6\uff08\u94fe\u8868\uff09\uff0crows\u4e3a\u7ed3\u679c\u96c6\u6570\u636e\u884c\u6570<\/p>\n
\r\ntypedef struct st_mysql_data {\r\n  MYSQL_ROWS *data;\r\n  struct embedded_query_result *embedded_info;\r\n  MEM_ROOT alloc;\r\n  my_ulonglong rows;\r\n  unsigned int fields;\r\n  \/* extra info for embedded library *\/\r\n  void *extension;\r\n} MYSQL_DATA;\r\n<\/pre>\n
embedded_query_result\u4e3b\u8981\u5305\u542b\u7ed3\u679c\u96c6\u7684\u72b6\u6001\u4fe1\u606f\uff0c\u7ed3\u679c\u96c6\u7684\u5217\u96c6\u5408field_list(\u7528\u4e8e\u89e3\u6790MYSQL_ROWS)\u4ee5\u53ca\u6307\u5411\u4e0b\u4e00\u4e2a\u7ed3\u679c\u96c6\u7684\u6307\u9488<\/p>\n
\r\n\/* This one is used by embedded library to gather returning data *\/\r\ntypedef struct embedded_query_result\r\n{\r\n  MYSQL_ROWS **prev_ptr;\r\n  unsigned int warning_count, server_status;\r\n  struct st_mysql_data *next;\r\n  my_ulonglong affected_rows, insert_id;\r\n  char info[MYSQL_ERRMSG_SIZE];\r\n  MYSQL_FIELD *fields_list;\r\n  unsigned int last_errno;\r\n  char sqlstate[SQLSTATE_LENGTH+1];\r\n} EQR;\r\n<\/pre>\n
MYSQL_ROWS\u662f\u4e00\u4e2a\u94fe\u8868\u8282\u70b9\uff0c\u4fdd\u5b58\u4e00\u884c\u6570\u636e\u548c\u6307\u5411\u4e0b\u884c\u6570\u636e\u7684\u6307\u9488<\/p>\n
\r\ntypedef struct st_mysql_rows {\r\n  struct st_mysql_rows *next;\t\t\/* list of rows *\/\r\n  MYSQL_ROW data;\r\n  unsigned long length;\r\n} MYSQL_ROWS;\r\n<\/pre>\n
\u53e6\u5916\u4e24\u4e2a\u91cd\u8981\u6570\u636e\u7ed3\u6784st_mysql_stmt\u548cst_mysql\uff0c\u5728\u6b64\u4e0d\u5217\u4e3e\u4e86<\/p>\n
\u51fd\u6570\u8c03\u7528\u5206\u6790<\/h3>\n
mysql_stmt_init<\/h4>\n
\u4e3astmt\u5206\u914d\u5185\u5b58\uff0c\u521d\u59cb\u5316\u53d8\u91cf\uff0c\u4e3astmt->mem_root\u548cstmt->result.alloc\u5206\u914d\u5185\u5b58<\/p>\n
\r\ninit_alloc_root(&stmt->mem_root, 2048, 2048);\r\ninit_alloc_root(&stmt->result.alloc, 4096, 4096);\r\n<\/pre>\n
mysql_stmt_prepare<\/h4>\n
1\u3001stmt_command\u53d1\u9001COM_STMT_PREPARE\u547d\u4ee4\uff0c\u6700\u7ec8\u901a\u8fc7emb_advanced_command\u8c03\u7528dispatch_command\u6267\u884cmysqld_stmt_prepare
\n2\u3001read_prepare_resut\u8c03\u7528emb_read_prepare_result\u8bfb\u53d6prepare\u8bed\u53e5\u6267\u884c\u7ed3\u679c
\n3\u3001\u4e3astmt->param\uff08\u7ed1\u5b9a\u53d8\u91cf\u53c2\u6570\uff09\u5206\u914d\u5185\u5b58\uff08\u5728stmt->mem_root\u4e0a\uff09<\/p>\n
mysql_stmt_bind_param<\/h4>\n
\u7ed1\u5b9aprepare\u8bed\u53e5\u53d8\u91cf\uff0c\u5bf9\u53c2\u6570\u505a\u4e00\u4e9b\u5224\u65ad\uff0c\u521d\u59cb\u5316stmt->param\uff0c\u6839\u636e\u6570\u636e\u7c7b\u578b\u8bbe\u7f6eparam->store_param_func<\/p>\n
mysql_stmt_bind_result<\/h4>\n
\u7ed1\u5b9a\u7ed3\u679c\u96c6\u53d8\u91cf\uff0c\u5bf9\u53c2\u6570\u505a\u4e00\u4e9b\u5224\u65ad\uff0c\u521d\u59cb\u5316stmt->bind\uff0c\u8c03\u7528setup_one_fetch_function\u6839\u636e\u6570\u636e\u7c7b\u578b\u8bbe\u7f6ebind->fetch_result<\/p>\n
mysql_stmt_execute<\/h4>\n
1\u3001reset_stmt_handle\uff0c\u91ca\u653estmt->result.alloc\u5185\u5b58\uff08MY_KEEP_PREALLOC\u65b9\u5f0f\uff09
\n2\u3001emb_stmt_execute\uff0c\u901a\u8fc7emb_advanced_command\u4ee5COM_STMT_EXECUTE\u4f5c\u4e3a\u53c2\u6570\u8c03\u7528dispatch_command\u6267\u884cmysqld_stmt_execute
\n3\u3001emb_read_query_result\u8bbe\u7f6e\u7ed3\u679c\u8bfb\u53d6\u4f4d\u7f6e\uff08thd->cur_data\uff09\uff0c\u4e3b\u8981\u6267\u884c\u4e00\u4e0b\u4ee3\u7801\uff1a<\/p>\n
\r\nstatic my_bool emb_read_query_result(MYSQL *mysql)\r\n{\r\n  THD *thd= (THD*) mysql->thd;\r\n  MYSQL_DATA *res= thd->first_data;\r\n  thd->first_data= res->embedded_info->next;\r\n  ..\r\n  if (res->embedded_info->fields_list)\r\n  {\r\n    mysql->status=MYSQL_STATUS_GET_RESULT;\r\n    thd->cur_data= res;\r\n  }\r\n  ..\r\n<\/pre>\n
mysql_stmt_restore_result<\/h4>\n
1\u3001emb_read_binary_rows\uff1a\u8c03\u7528emb_read_rows\u83b7\u5f97\u7ed3\u679c\u96c6\u8bfb\u53d6\u4f4d\u7f6e\uff08thd->cur_data\uff09\u4e4b\u540e\u8d4b\u503c\uff1astmt->result= *data\uff0c\u5230\u6b64\u6b69\uff0cstmt\u7ed3\u679c\u96c6\u53ef\u4ee5\u901a\u8fc7stmt->result\u8fd4\u56de\u4e86
\n2\u3001\u4e3astmt\u8bbe\u7f6efetch\u7ed3\u679c\u96c6\u7684\u51fd\u6570\u6307\u9488\u548c\u6e38\u6807\uff1a<\/p>\n
\r\nstmt->read_row_func= stmt_read_row_buffered;\r\nstmt->data_cursor= stmt->result->data\r\n<\/pre>\n
mysql_stmt_fetch<\/h4>\n
1\u3001stmt_read_row_buffered\uff0c\u4ee3\u7801\u5982\u4e0b\uff1a<\/p>\n
\r\nstatic int stmt_read_row_buffered(MYSQL_STMT *stmt, unsigned char **row)\r\n{\r\n  if (stmt->data_cursor)\r\n  {\r\n    *row= (uchar *) stmt->data_cursor->data;\r\n    stmt->data_cursor= stmt->data_cursor->next;\r\n    return 0;\r\n  }\r\n  *row= 0;\r\n  return MYSQL_NO_DATA;\r\n}\r\n<\/pre>\n
2\u3001stmt_fetch_row\uff0c\u6839\u636estmt_read_row_buffered\u5f97\u5230\u7684\u884c\u6570\u636e\uff0c\u5c06\u7ed3\u679c\u89e3\u6790\u5230\u7ed1\u5b9a\u7ed3\u679c\u53d8\u91cf\u4e2d<\/p>\n
mysql_stmt_affected_rows, mysql_stmt_num_rows<\/h4>\n
\u8fd4\u56destmt->affected_rows\u548cstmt->result.rows<\/p>\n
mysql_stmt_close<\/h4>\n
\u5173\u95edstmt\uff0c\u91ca\u653estmt->mem_root\u548cstmt->result.alloc\u5185\u5b58<\/p>\n
\u4e24\u4e2aMemeory leak\u7684bug<\/h3>\n
\u4ee3\u7801\u8c03\u8bd5\u8fc7\u7a0b\u4e2d\u901a\u8fc7valgrind\u53d1\u73b0\u4e86\u4e24\u5904\u5185\u5b58\u6cc4\u9732\uff08mysql-5.6.11\uff09<\/p>\n
\r\n==29639== 2,064 bytes in 1 blocks are definitely lost in loss record 3 of 4\r\n==29639==    at 0x4A05FDE: malloc (vg_replace_malloc.c:236)\r\n==29639==    by 0x4635E1: my_malloc (my_malloc.c:38)\r\n==29639==    by 0x462C72: init_alloc_root (my_alloc.c:63)\r\n==29639==    by 0x448935: mysql_stmt_init (libmysql.c:1512)\r\n==29639==    by 0x444277: main (in \/u01\/mysql5611\/bug62136)\r\n==29639== \r\n==29639== 4,112 bytes in 1 blocks are definitely lost in loss record 4 of 4\r\n==29639==    at 0x4A05FDE: malloc (vg_replace_malloc.c:236)\r\n==29639==    by 0x4635E1: my_malloc (my_malloc.c:38)\r\n==29639==    by 0x462C72: init_alloc_root (my_alloc.c:63)\r\n==29639==    by 0x44894B: mysql_stmt_init (libmysql.c:1513)\r\n==29639==    by 0x444277: main (in \/u01\/mysql5611\/bug62136)\r\n==29639== \r\n==29639== LEAK SUMMARY:\r\n==29639==    definitely lost: 6,176 bytes in 2 blocks\r\n==29639==    indirectly lost: 0 bytes in 0 blocks\r\n==29639==      possibly lost: 0 bytes in 0 blocks\r\n==29639==    still reachable: 568 bytes in 2 blocks\r\n==29639==         suppressed: 0 bytes in 0 blocks\r\n==29639== \r\n==29639== For counts of detected and suppressed errors, rerun with: -v\r\n==29639== ERROR SUMMARY: 2 errors from 2 contexts (suppressed: 6 from 6)\r\n<\/pre>\n
\u7ecf\u8fc7\u5206\u6790\uff0c\u6700\u7ec8\u5b9a\u4f4d\u5230\u95ee\u9898\u539f\u56e0\u5e76fix\uff0c\u5411\u5b98\u65b9\u63d0\u4ea4\u4e86\u4e00\u4e2abug report\uff0c\u8be6\u60c5\uff1amysql bug#70238<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
MySQL embedded server\u7b80\u4ecb MySQL\u53ef\u4ee5\u4f5c\u4e3a\u4e00\u4e2aembedded server\u6267\u884c\uff0c\u8fd9\u65f6 … \u7ee7\u7eed\u9605\u8bfbMySQL embedded server prepared statement\u6267\u884c\u5206\u6790<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[1],"tags":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3tPZp-6M","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/420"}],"collection":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=420"}],"version-history":[{"count":3,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/420\/revisions"}],"predecessor-version":[{"id":423,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/420\/revisions\/423"}],"wp:attachment":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=420"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}