MySQL 데이터베이스에서 이름 패턴과 일치하는 모든 테이블 잘라내기

MySQL 데이터베이스에서 이름 패턴과 일치하는 모든 테이블 잘라내기

모든 재고 테이블을 정리해야 합니다.

시도해 본 적이 있습니다.

SELECT 'TRUNCATE TABLE ' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'

하지만 다음과 같은 오류가 발생합니다.

Truncated incorrect DOUBLE value: 'TRUNCATE TABLE ' Error Code 1292

이것이 올바른 방법이라면, 내가 무엇을 잘못하고 있습니까?

concat 사용:

SELECT concat('TRUNCATE TABLE `', TABLE_NAME, '`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'

이렇게 하면 물론 직접 복사하여 실행해야 하는 SQL만 생성됩니다.

이미 오래된 게시물인 것은 알지만 다음 예제는 Linux 명령줄 또는 셸 스크립트 내에서 여러 테이블을 잘라야 하는 사용자에게 유용할 수 있습니다.

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>

괄호 안의 문자열을 자신의 값으로 바꿔야 한다는 것을 고려하면, 그것은 저에게 효과가 있었습니다.또한 'your_table_a,your_table_b,your_table_c'를 쉼표로 구분된 테이블 목록으로 바꿉니다. ;)

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;'

명령줄을 사용하는 경우 다음과 같은 작업을 수행할 수 있습니다.

mysql -u [user] -p[password] -e 'use [database]; show tables' | perl -lane 'print "truncate table $F[0]" if /^inventory/i' > [database].sql

위의 SQL 문에 대한 약간의 개선 사항이 있습니다.

SELECT DISTINCT concat("TRUNCATE TABLE ", TABLE_NAME, ";") 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE "cache%"

처음에 DISTINCT 명령을 주의하십시오.이렇게 하면 LIKE 기준과 일치하는 테이블이 한 번만 SELECT 결과가 표시됩니다.

서버에 둘 이상의 데이터베이스가 있는 경우, db도 지정할 수 있습니다.

예: Drupal 캐시 테이블 지우기

SELECT DISTINCT CONCAT(  
    "TRUNCATE TABLE ", 
    TABLE_SCHEMA,  
    ".",
    TABLE_NAME, 
    ";" ) 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE  "cache_%"
AND TABLE_SCHEMA = 'dbname';

이로 인해 SQL이 다음과 같이 생성됩니다.

TRUNCATE TABLE dbname.cache_admin_menu;
TRUNCATE TABLE dbname.cache_block;
etc.

두 가지 이점을 고려하면,

1. 이 SQL은 현재 선택한 데이터베이스에 관계없이 어디서나 실행할 수 있습니다.
2. 올바른 데이터베이스의 테이블을 잘라내고 있음을 확인할 수 있습니다.

외부 키 검사가 대량 절단에 방해가 된다면 @falpez 답변을 참조하십시오(물론 드러팔 캐시 삭제에는 해당되지 않습니다).

이 문을 사용해 보십시오. 모든 테이블이 한 줄로 표시되고 해당 문을 복사한 후 실행하여 다음을 모두 실행할 수 있습니다.

SELECT DISTINCT REPLACE(GROUP_CONCAT("TRUNCATE TABLE ", TABLE_NAME, ";"), ',', '')
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE "cache%";

SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'

그런 다음 결과 집합의 모든 행을 SQL 문으로 지정해야 합니다.별도의 결과를 처리하여 명령 창에 붙여넣으면 됩니다.

그러나 훨씬 더 나은 해결책은 위의 쿼리를 만들고 결과를 반복하여 모든 쿼리를 수행하는 프로그램을 작성하는 것입니다.

해당 언어 또는 다른 스크립트 언어에는 PHP를 사용합니다.SO에 대해서도 많은 예를 들 수 있습니다.

SELECT REPLACE(GROUP_CONCAT("TRUNCATE TABLE ", TABLE_NAME, ";"), ',', '') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE "cache%";

이것은 AshwinP의 답변에 근거한 것입니다.DISTINCT를 제거하고 WHERE TABLE_SCHEMA=SCHEMA()를 추가해야 작동합니다.

이 명령은 테이블을 잘라내지 않고 한 줄로 잘라냅니다.하나의 행을 복사하여 mysql 클라이언트에 붙여넣습니다.

늦은 답변...하지만 안 하는 것보다는 나중에 하는 게 낫습니다.불필요한 복사 및 붙여넣기 또는 추가 셸 스크립팅을 방지하기 위해 적어도 MySQL 및 MariaDB와 관련하여 데이터베이스의 테이블 집합을 잘라내거나 패턴을 일치시키는 가장 불가지론적인 접근 방식은 저장 프로시저를 통해 수행됩니다.

다음은 현재 데이터베이스의 모든 테이블을 자르기 위해 정기적으로 사용되는 스크립트입니다.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;

이 예에서는 저장 프로시저를 사용한 후 삭제합니다.그러나 이 작업이 일반적인 작업인 경우에는 다음 작업 전에 모든 작업을 실행하여 한 번만 추가하는 것이 좋습니다.$$구분 기호 및 실행

CALL truncate_tables();

대상 데이터베이스에 있습니다.

mysql -uuser -ppass --execute="SELECT concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME LIKE 'cache%'" | sed 1d | mysql -uuser -ppass db 

..이것은 나에게 효과가 있었습니다.user, pass 및 db를 사용자의 것으로 바꿉니다.

조금 전에 읽은 concat을 사용하여 문을 생성하고 실행하는 매우 좋은 예:

http://www.mysqltutorial.org/mysql-drop-table

-- set table schema and pattern matching for tables

SET @schema = 'classicmodels';
SET @pattern = 'test%';

-- build dynamic sql (DROP TABLE tbl1, tbl2...;)

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';')
INTO @droplike FROM information_schema.tables WHERE @schema = database()
AND table_name LIKE @pattern;

-- display the dynamic sql statement

SELECT @droplike;  

-- execute dynamic sql

PREPARE stmt FROM @dropcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

언급URL : https://stackoverflow.com/questions/1575419/truncate-all-tables-in-mysql-database-that-match-a-name-pattern