MySQL MySQL按查询转储

MySQL dump by query

提问by Jakub Arnold

Is it possible to do mysqldumpby single SQL query?

是否有可能做mysqldump单SQL query？

I mean to dump the wholedatabase, like phpmyadmindoes when you do export to SQL

我的意思是转储整个数据库，就像phpmyadmin你导出到SQL

回答by Zak

not mysqldump, but mysql cli...

不是mysqldump，而是mysql cli...

mysql -e "select * from myTable" -u myuser -pxxxxxxxxx mydatabase

you can redirect it out to a file if you want :

如果需要，您可以将其重定向到文件：

mysql -e "select * from myTable" -u myuser -pxxxxxxxx mydatabase > mydumpfile.txt

Update: Original post asked if he could dump from the database by query. What he asked and what he meant were different. He really wanted to just mysqldump all tables.

更新：原始帖子询问他是否可以通过查询从数据库中转储。他问的和他的意思是不同的。他真的很想只是 mysqldump 所有表。

mysqldump --tables myTable --where="id < 1000"

回答by Thomas Ahle

This should work

这应该工作

mysqldump --databases X --tables Y --where="1 limit 1000000"

回答by Guy

You can dump a query as csv like this:

您可以像这样将查询转储为 csv：

SELECT * from myTable
INTO OUTFILE '/tmp/querydump.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

回答by Gary

Dump a table using a where query:

使用 where 查询转储表：

mysqldump mydatabase mytable --where="mycolumn = myvalue" --no-create-info > data.sql

Dump an entire table:

转储整个表：

mysqldump mydatabase mytable > data.sql

Notes:

笔记：

• Replace mydatabase, mytable, and the where statement with your desired values.
• By default, mysqldumpwill include DROP TABLEand CREATE TABLEstatements in its output. Therefore, if you wish to not delete all the data in your tablewhen restoring from the saved data file, make sure you use the --no-create-infooption.
• You may need to add the appropriate -h, -u, and -poptions to the example commands above in order to specify your desired database host, user, and password, respectively.

• 用您想要的值替换mydatabase、mytable和 where 语句。
• 默认情况下，mysqldump将在其输出中包含DROP TABLE和CREATE TABLE语句。因此，如果您希望在从保存的数据文件恢复时不删除表中的所有数据，请确保使用该--no-create-info选项。
• 您可能需要向上面的示例命令添加适当的-h、-u和-p选项，以便分别指定所需的数据库主机、用户和密码。

回答by Wagner Bianchi

You could use --where option on mysqldump to produce an output that you are waiting for:

您可以在 mysqldump 上使用 --where 选项来生成您正在等待的输出：

mysqldump -u root -p test t1 --where="1=1 limit 100" > arquivo.sql

At most 100 rows from test.t1 will be dumped from database table.

test.t1 中最多 100 行将从数据库表中转储。

Cheers, WB

干杯，WB

回答by aullah

If you want to export your last n amount of records into a file, you can run the following:

如果要将最后 n 条记录导出到文件中，可以运行以下命令：

mysqldump -u user -p -h localhost --where "1=1 ORDER BY id DESC LIMIT 100" database table > export_file.sql

The above will save the last 100 records into export_file.sql, assuming the table you're exporting from has an auto-incremented id column.

以上将把最后 100 条记录保存到 export_file.sql 中，假设您从中导出的表有一个自动递增的 id 列。

You will need to alter the user, localhost, database and table values. You may optionally alter the id column and export file name.

您将需要更改用户、本地主机、数据库和表值。您可以选择更改 id 列和导出文件名。

回答by MPelletier

MySQL Workbench also has this feature neatly in the GUI. Simply run a query, click the save icon next to Export/Import:

MySQL Workbench 在 GUI 中也有这个功能。只需运行查询，单击导出/导入旁边的保存图标：

Then choose "SQL INSERT statements (*.sql)" in the list.

然后在列表中选择“SQL INSERT statements (*.sql)”。

Enter a name, click save, confirm the table name and you will have your dump file.

输入名称，单击保存，确认表名称，您将拥有转储文件。

回答by zzapper

Combining much of above here is my real practical example, selecting records based on both meterid & timestamp. I have needed this command for years. Executes really quickly.

结合以上大部分内容是我真实的实际示例，根据meterid 和timestamp 选择记录。多年来我一直需要这个命令。执行速度非常快。

mysqldump -uuser -ppassword main_dbo trHourly --where="MeterID =5406 AND TIMESTAMP<'2014-10-13 05:00:00'" --no-create-info --skip-extended-insert | grep  '^INSERT' > 5406.sql

回答by lanni654321

mysql Export the query results command line：

mysql 导出查询结果命令行：

mysql -h120.26.133.63 -umiyadb -proot123 miya -e "select * from user where id=1" > mydumpfile.txt