List:Internals« Previous MessageNext Message »
From:丁奇 Date:November 15 2011 11:54am
Subject:add full_query param to mysqldump
View as plain text  
Hi,



  When I need to dump  records of table A which needs to join table B, I have to run it like this

"bin/mysqldump  -uuser -S run/mysql.sock test A --tables --where='id IN (select A.id from A,B where A.f1=B.f1 and B.f2=xxx)'  --skip-lock-tables"



  But this make the real query_command is "where id in (the_join_query)",  it is slow.



 The best query must be  "select A.* from A,B where A.f1=B.f1 and B.f2=xxx"



  So I think the tool mysqldump can add a param --full_query,  to enable  user to write the query himself, so he can make the query in the  best form.



   The patch file is in the attachment.



   btw: We have already signed the OCA.





   The mysql-test case and result is as follow:



cat t/mysqldump-fq.test



# Embedded server doesn't support external clients
--source include/not_embedded.inc

--let $file = $MYSQLTEST_VARDIR/tmp/fullquery.sql

CREATE DATABASE mysqldump_fq;
USE mysqldump_fq;
CREATE TABLE t1 (c1 int, c2 int);
CREATE TABLE t2 (c1 int, c2 int);
insert into t1 values(1,2);
insert into t1 values(2,4);
insert into t2 values(10,2);
insert into t2 values(20,4);
--exec $MYSQL_DUMP mysqldump_fq t1 --skip-lock-tables --full_query='t1.* from t1,t2 where t1.c2=t2.c2 and t2.c1=10' > $file
--exec $MYSQL mysqldump_fq < $file
select * from t1;
DROP DATABASE mysqldump_fq;
--remove_file $file



cat r/mysqldump-fq.result

CREATE DATABASE mysqldump_fq;
USE mysqldump_fq;
CREATE TABLE t1 (c1 int, c2 int);
CREATE TABLE t2 (c1 int, c2 int);
insert into t1 values(1,2);
insert into t1 values(2,4);
insert into t2 values(10,2);
insert into t2 values(20,4);
select * from t1;
c1 c2
1 2
DROP DATABASE mysqldump_fq;



Best regards,

Xiaobin Lin






________________________________
This email (including any attachments) is confidential and may be legally privileged. If you received this email in error, please delete it immediately and do not copy it or use it for any purpose or disclose its contents to any other person. Thank you.

本电邮(包括任何附件)可能含有机密资料并受法杀;ぁH缒本电邮进行复制并用作任何其他用途、或透露本邮件之内容。谢弧Attachment: [text/html]
Attachment: [text/html]
--- mysql-5.1.48/client/mysqldump.c 2011-10-28 10:50:36.000000000 +0800 +++ client/mysqldump.c 2011-11-09 17:45:27.000000000 +0800 @@ -106,8 +106,8 @@ static DYNAMIC_STRING insert_pat; static char *opt_password=0,*current_user=0, *current_host=0,*path=0,*fields_terminated=0, - *lines_terminated=0, *enclosed=0, *opt_enclosed=0, *escaped=0, - *where=0, *order_by=0, + *lines_terminated=0, *enclosed=0, *opt_enclosed=0, *escaped=0, *full_query=0, + *where=0, *order_by=0, *opt_compatible_mode_str= 0, *err_ptr= 0, *log_error_file= NULL; @@ -475,6 +475,8 @@ GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}, {"where", 'w', "Dump only selected records. Quotes are mandatory.", (uchar**) &where, (uchar**) &where, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, + {"full_query", 'w', "The full query write by user. If it is set, ignore where and order by param. Quotes are mandatory.", + (uchar**) &full_query, (uchar**) &full_query, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"xml", 'X', "Dump a database as well formed XML.", 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0} @@ -3174,19 +3176,33 @@ add_load_option(&query_string, " ESCAPED BY ", escaped); add_load_option(&query_string, " LINES TERMINATED BY ", lines_terminated); - dynstr_append_checked(&query_string, " FROM "); - dynstr_append_checked(&query_string, result_table); - - if (where) + if (full_query) { - dynstr_append_checked(&query_string, " WHERE "); - dynstr_append_checked(&query_string, where); - } + if (!opt_xml && opt_comments) + { + fprintf(md_result_file, "-- FULL_QUERY: %s\n", full_query); + check_io(md_result_file); + } - if (order_by) + dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ "); + dynstr_append_checked(&query_string, full_query); + } + else { - dynstr_append_checked(&query_string, " ORDER BY "); - dynstr_append_checked(&query_string, order_by); + dynstr_append_checked(&query_string, " FROM "); + dynstr_append_checked(&query_string, result_table); + + if (where) + { + dynstr_append_checked(&query_string, " WHERE "); + dynstr_append_checked(&query_string, where); + } + + if (order_by) + { + dynstr_append_checked(&query_string, " ORDER BY "); + dynstr_append_checked(&query_string, order_by); + } } if (mysql_real_query(mysql, query_string.str, query_string.length)) @@ -3204,30 +3220,44 @@ result_table); check_io(md_result_file); } - - dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ * FROM "); - dynstr_append_checked(&query_string, result_table); - if (where) + if (full_query) { if (!opt_xml && opt_comments) { - fprintf(md_result_file, "-- WHERE: %s\n", where); + fprintf(md_result_file, "-- FULL_QUERY: %s\n", full_query); check_io(md_result_file); } - - dynstr_append_checked(&query_string, " WHERE "); - dynstr_append_checked(&query_string, where); + + dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ "); + dynstr_append_checked(&query_string, full_query); } - if (order_by) + else { - if (!opt_xml && opt_comments) + dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ * FROM "); + dynstr_append_checked(&query_string, result_table); + + if (where) { - fprintf(md_result_file, "-- ORDER BY: %s\n", order_by); - check_io(md_result_file); + if (!opt_xml && opt_comments) + { + fprintf(md_result_file, "-- WHERE: %s\n", where); + check_io(md_result_file); + } + + dynstr_append_checked(&query_string, " WHERE "); + dynstr_append_checked(&query_string, where); + } + if (order_by) + { + if (!opt_xml && opt_comments) + { + fprintf(md_result_file, "-- ORDER BY: %s\n", order_by); + check_io(md_result_file); + } + dynstr_append_checked(&query_string, " ORDER BY "); + dynstr_append_checked(&query_string, order_by); } - dynstr_append_checked(&query_string, " ORDER BY "); - dynstr_append_checked(&query_string, order_by); } if (!opt_xml && !opt_compact)
Thread
add full_query param to mysqldump丁奇15 Nov