截断 MySQL 数据库中与名称模式匹配的所有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1575419/
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
Truncate all tables in MySQL database that match a name pattern
提问by Luiscencio
I need to clear all my inventory tables.
我需要清除我所有的库存表。
I've tried this:
我试过这个：
SELECT 'TRUNCATE TABLE ' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'
But I get this error:
但我收到此错误：
Truncated incorrect DOUBLE value: 'TRUNCATE TABLE ' Error Code 1292
if this is the correct way, then what am I doing wrong?
如果这是正确的方法，那么我做错了什么？
回答by ???u
Use concat:
使用连接：
SELECT concat('TRUNCATE TABLE `', TABLE_NAME, '`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'
This will of course only generate SQL which you need to copy and run yourself.
这当然只会生成您需要自己复制和运行的 SQL。
回答by Matthias
I know it's an older post already but maybe the following example is helpful for someone who needs to truncate multiple tables from linux command line or from within a shell script:
我知道这已经是一篇较旧的帖子了，但也许以下示例对需要从 linux 命令行或 shell 脚本中截断多个表的人有所帮助：
mysql -p<secret> --execute="SELECT concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '<database>' AND FIND_IN_SET(TABLE_NAME,'your_table_a,your_table_b,your_table_c')" | sed 1d | mysql -p<secret> <database>
Given that you need to replace the strings in brackets with your own values it was working for me. Also replace the 'your_table_a,your_table_b,your_table_c' with a comma-separated list of your tables. ;)
鉴于您需要用您自己的值替换括号中的字符串，它对我有用。还要用逗号分隔的表格列表替换“your_table_a,your_table_b,your_table_c”。;)
回答by falperez
SELECT 'SET FOREIGN_KEY_CHECKS = 0;'
UNION
SELECT DISTINCT concat( "TRUNCATE TABLE ", TABLE_NAME, ";" )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydbname'
UNION
SELECT 'SET FOREIGN_KEY_CHECKS = 1;'
回答by bichonfrise74
If you are using command line, you might want to try something like this.
如果您使用命令行，您可能想尝试这样的操作。
mysql -u [user] -p[password] -e 'use [database]; show tables' | perl -lane 'print "truncate table $F[0]" if /^inventory/i' > [database].sql
回答by Elias
Here is a little improvement on the above SQL statement.
这里是对上述SQL语句的一点改进。
SELECT DISTINCT concat("TRUNCATE TABLE ", TABLE_NAME, ";")
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE "cache%"
Notice the DISTINCT command in the beginning. This will make the SELECT result show only once the the tables that match your LIKE criteria.
注意开头的 DISTINCT 命令。这将使 SELECT 结果仅显示一次与您的 LIKE 条件匹配的表。
回答by chim
If you have more than one database on your server, you may want to specify the db as well.
如果您的服务器上有多个数据库，您可能还需要指定 db。
e.g. to clear Drupal cache tables
例如清除 Drupal 缓存表
SELECT DISTINCT CONCAT(
"TRUNCATE TABLE ",
TABLE_SCHEMA,
".",
TABLE_NAME,
";" )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE "cache_%"
AND TABLE_SCHEMA = 'dbname';
This results in sql like...
这会导致 sql 像...
TRUNCATE TABLE dbname.cache_admin_menu;
TRUNCATE TABLE dbname.cache_block;
etc.
Giving two advantages,
提供两个优势，
- You can run this sql anywhere regardless of the currently selected database.
- You'll be sure that you're truncating the tables on the correct database.
- 无论当前选择的数据库如何，您都可以在任何地方运行此 sql。
- 您将确定您正在截断正确数据库中的表。
See @falperez answer if foreign key checks get in the way of your mass truncate, (although of course they won't for drupal cache clearing)
如果外键检查妨碍了您的大规模截断，请参阅@falperez 的答案（尽管它们当然不会用于清除 drupal 缓存）
回答by Ashwin Parmar
Try this statement, It will give you single line of all tables and you can copy that statements and run to execute all:
试试这个语句，它会给你所有表的一行，你可以复制该语句并运行以执行所有：
SELECT DISTINCT REPLACE(GROUP_CONCAT("TRUNCATE TABLE ", TABLE_NAME, ";"), ',', '')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE "cache%";
回答by Dave Cohen
SELECT REPLACE(GROUP_CONCAT("TRUNCATE TABLE ", TABLE_NAME, ";"), ',', '')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE "cache%";
This is based on AshwinP's answer. I had to remove DISTINCT and add WHERE TABLE_SCHEMA=DATABASE() for it to work for me.
这是基于 AshwinP 的回答。我必须删除 DISTINCT 并添加 WHERE TABLE_SCHEMA=DATABASE() 才能为我工作。
The command does not truncate tables, but returns a one-liner that will. Copy and paste the one-liner into the mysql client.
该命令不会截断表，但会返回一个单行。将单行复制粘贴到mysql客户端。
回答by Lukasz Lysik
SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'
You should then take every row of the result set and call it as the SQL statement. You can do it by coping and pasting the separated results to the command window.
然后，您应该获取结果集的每一行并将其作为 SQL 语句调用。您可以通过将分离的结果复制并粘贴到命令窗口来实现。
But much better solution is to write some program which will make the query above and loop through the results and make every query.
但是更好的解决方案是编写一些程序来进行上面的查询并遍历结果并进行每个查询。
Use PHP for that or any other scripting language. Many examples even here on SO.
使用 PHP 或任何其他脚本语言。许多例子甚至在这里。
回答by Frelling
Late answer... But better later, than never. To avoid unnecessary copying and pasting or additional shell scripting, the most agnostic approach -- at least with respect to MySQL and MariaDB -- to truncating a set of tables in a database or matching a pattern is via a stored procedure.
迟到的答案......但迟到总比没有好。为了避免不必要的复制和粘贴或额外的 shell 脚本，最不可知的方法——至少对于 MySQL 和 MariaDB——截断数据库中的一组表或匹配模式是通过存储过程。
The following is a script used regularly to truncate all tables in the current database. The SELECTstatement can be tailored to match patterns if more precise control is required.
下面是一个经常用来截断当前数据库中所有表的脚本。该SELECT声明可以被定制，如果需要更精确的控制，以匹配模式。
DROP PROCEDURE IF EXISTS truncate_tables;
DELIMITER $$
CREATE PROCEDURE truncate_tables()
BEGIN
DECLARE tblName CHAR(64);
DECLARE done INT DEFAULT FALSE;
DECLARE dbTables CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN dbTables;
SET FOREIGN_KEY_CHECKS = 0;
read_loop: LOOP
FETCH dbTables INTO tblName;
IF done THEN
LEAVE read_loop;
END IF;
PREPARE stmt FROM CONCAT('TRUNCATE ', tblName);
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP read_loop;
CLOSE dbTables;
SET FOREIGN_KEY_CHECKS = 1;
END
$$
CALL truncate_tables();
DROP PROCEDURE IF EXISTS truncate_tables;
This example drops the store procedure after it is used. However, if this is a regular task, then it is better to just add it once by running everything before the $$delimiter and executing
此示例在使用后删除存储过程。但是，如果这是一个常规任务，那么最好通过在$$分隔符之前运行所有内容并执行来添加它一次
CALL truncate_tables();
on the target database.
在目标数据库上。
