php 如何从php脚本中使用where子句导出mysql表的某些行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15287295/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export some rows of a mysql table with where clause from a php script?
提问by ehp
I have a mysql table say test and I want to create a importable .sql file for rows where id is between 10 and 100 using php script.
我有一个 mysql 表，比如 test，我想使用 php 脚本为 id 在 10 到 100 之间的行创建一个可导入的 .sql 文件。
I want to create a sql file say test.sql which can be imported to mysql database.
我想创建一个 sql 文件 test.sql 可以导入到 mysql 数据库。
Mycode:
我的代码：
$con=mysqli_connect("localhost", "root","","mydatabase");
$tableName = 'test';
$backupFile = '/opt/lampp/htdocs/practices/phpTest/test.sql';
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName WHERE id BETWEEN 10 AND 500";
$result = mysqli_query($con,$query);
This create a test.sql file but when I try to import it gives error #1064 My script only creates a file with rows with columns name and table sturcute or insert query.
这会创建一个 test.sql 文件，但是当我尝试导入它时会出现错误 #1064 我的脚本只创建一个文件，其中包含带有列名和表 sturcute 或插入查询的行。
回答by Chipmunk
As mentioned in the comments you can use mysqldump the following way.
如评论中所述，您可以通过以下方式使用 mysqldump。
mysqldump --user=... --password=... --host=... DB_NAME --where=<YOUR CLAUSE> > /path/to/output/file.sql
If you want this to be in your php file you can do the following
如果您希望它在您的 php 文件中，您可以执行以下操作
exec('mysqldump --user=... --password=... --host=... DB_NAME --where=<YOUR CLAUSE> > /path/to/output/file.sql');
回答by Abhinav bhardwaj
In very simply way go to your phpMyAdmin select you database whose particular rows you want to export click on "SQL" (To Run SQL query/queries on database) Write sql query and execute it Like select * from test table limit 500 now what ever result come Just at the bottom see "Query results operations" just click on Export
以非常简单的方式转到您的 phpMyAdmin 选择您要导出其特定行的数据库，单击“SQL”（在数据库上运行 SQL 查询/查询）编写 sql 查询并执行它就像 select * from test table limit 500 now what ever结果来就在底部看到“查询结果操作”只需点击 导出
All done :-)
全部完成 ：-）
回答by alwaysLearn
If you have exported data using OUTFILE then you have to import it using INFILE COMMAND
如果您使用 OUTFILE 导出数据，则必须使用 INFILE COMMAND 导入它
$con=mysqli_connect("localhost", "root","","mydatabase");
$tableName = 'test';
$backupFile = '/opt/lampp/htdocs/practices/phpTest/test.sql';
$query = "LOAD DATA INFILE '$backupFile' INTO TABLE $tableName";
$result = mysqli_query($con,$query);
OR you can make csv file using
或者你可以使用
$con=mysqli_connect("localhost", "root","","mydatabase");
$tableName = 'test';
$backupFile = '/opt/lampp/htdocs/practices/phpTest/test.sql';
$query = 'SELECT * INTO OUTFILE '."'$backupFile'".'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '.'\'"\'
LINES TERMINATED BY \'\n\'
FROM '.$tableName.' WHERE id BETWEEN 10 AND 500';
$result = mysqli_query($con,$query);
and then import this file.
然后导入这个文件。
Hope this works...
希望这有效...
回答by Surojit Paul
Mysql Shell command
Mysql Shell 命令
mysqldump -u user -p password -h host Database Table --where="Condition"> /path/exportFile.sql
Example
例子
mysqldump -u user -p 123456 -h host Database Student --where="age > 10"> /path/exportFile.sql
