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.
本电邮(包括任何附件)可能含有机密资料并受法杀；ぁＨ缒本电邮进行复制并用作任何其他用途、或透露本邮件之内容。谢弧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 |
