php MySQL和PHP中的空数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/725879/
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
Empty database in MySQL and PHP?
提问by Jaka Jan?ar
Is it possible to empty an entire database by connecting to it with PHP and giving a command?
是否可以通过使用 PHP 连接到整个数据库并发出命令来清空整个数据库？
回答by Jaka Jan?ar
The simplest way to do it, if you have the privileges, is:
如果您有权限，最简单的方法是：
DROP DATABASE dbName; CREATE DATABASE dbName; USE DATABASE dbName;
The alternative is to query the information_schemadatabase for triggers, stored routines (procedures and functions), tables, views and possibly something else, and drop them individually.
另一种方法是查询information_schema数据库中的触发器、存储例程（过程和函数）、表、视图和其他可能的东西，然后单独删除它们。
Even after this, your database might still not be in the same state as a newly created one, since it might have a custom default character set and collation set. Use ALTER DATABASEto change that.
即使在此之后，您的数据库可能仍处于与新创建的数据库不同的状态，因为它可能具有自定义的默认字符集和排序规则集。使用ALTER DATABASE来改变这种状况。
As features keep getting added (events...) you'll have more and more work this way. So really, the only way to completely empty the database is to drop it and re-create it.
随着功能的不断添加（事件...），您将有越来越多的工作以这种方式进行。所以真的，完全清空数据库的唯一方法是删除它并重新创建它。
回答by Jhonny D. Cano -Leftware-
You can get table names with
你可以得到表名
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your db'
And make the truncate,
并截断，
even, you can make
甚至，你可以做
SELECT 'TRUNCATE TABLE ' + table_name + ';' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your db'
And then iterate the resultset executing the string query for each record.
然后迭代结果集，为每条记录执行字符串查询。
回答by Varun P V
Yes. You can connect the database and Truncate the table by assigning as a variable.
是的。您可以通过分配为变量来连接数据库并截断表。
$conn = new mysqli($servername, $username, $password, $dbname);
$conn = new mysqli($servername, $username, $password, $dbname);
$variableName = $conn->query($que);
$variableName = $conn->query($que);
if ($conn->connect_error) {
如果（$conn->connect_error）{
die("Connection failed: " . $conn->connect_error);
die("连接失败：" . $conn->connect_error);
}
}
$result = $conn->query('TRUNCATE TABLE yourtablename;');
$result = $conn->query('TRUNCATE TABLE yourtablename;');
if ($result) {
echo "Request ID table has been truncated";
}
else echo "Something went wrong: " . mysqli_error();
回答by user3226750
You can get a list of all tables in a database ($databaseName), loop through them all, and apply TRUNCATE TABLEto each.
您可以获取数据库 ( $databaseName)中所有表的列表，遍历所有表，然后应用TRUNCATE TABLE到每个表。
$result = mysqli_query($link, "SHOW TABLES IN `$databaseName`");
while ($table = mysqli_fetch_array($result)) {
$tableName = $table[0];
mysqli_query($link, "TRUNCATE TABLE `$databaseName`.`$tableName`");
if (mysqli_errno($link)) echo mysqli_errno($link) . ' ' . mysqli_error($link);
else echo "$tableName was cleared<br>";
}
This clears the contents of all the tables in a database, keeping the structure.
这会清除数据库中所有表的内容，保留结构。
回答by user997932
This worked for me.
这对我有用。
mysql -u < DB-USER > -p<DB-PASS> --silent --skip-column-names -e "SHOW TABLES" <DB-NAME> | xargs -L1 -I% echo 'DROP TABLE `%`;' | mysql -u < DB-USER > -p< DB-PASS > -v < DB-NAME >
回答by Will Green
Yes. Basically, you need to get a list of all Tables in the Database, then iterate over that list and issue a TRUNCATE TABLE $tablenamefor each entry.
是的。基本上，您需要获取数据库中所有表的列表，然后遍历该列表并TRUNCATE TABLE $tablename为每个条目发出一个。
This looks like a decent enough implementation: Truncate all tables in a MySQL database
这看起来是一个不错的实现：Truncate all tables in a MySQL database
回答by Prashant Rijal
<?php
$connection = new mysqli($host, $user, $pass, $db);
$sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE '".$db."'";
$result = $connection->query($sql);
$tables = $result->fetch_all(MYSQLI_ASSOC);
foreach($tables as $table) {
$sql = "TRUNCATE TABLE `".$table['TABLE_NAME']."`";
$result = $connection->query($sql);
}
?>
