禁用外键检查删除InnoDB表Perl脚本

Am fairly new to using MySQL and a total novice at Perl but am trying to hack someone elses script to help me out. I got the script from here. It looks great so far but it fails as the tables have some foreign key check going on. I could go through phpmyadmin and try and delete them all one by one but this takes for ever and is my third time of having to do this :( My query is, can this script be ammended to include:

在Perl上使用MySQL和一个新手是相当新的，但我试图破解别人的脚本来帮助我。我从这里得到了剧本。它到目前为止看起来很棒，但它失败了，因为表有一些外键检查正在进行。我可以通过phpmyadmin尝试逐个删除它们，但这需要永远，这是我第三次不得不这样做:(我的查询是，可以修改此脚本包括：

`SET FOREIGN_KEY_CHECKS = 0;

before it runs the drop table command? I tried to follow the script through but could not find a definitive command part of the script (probably due to ignorance/lack of understanding). Any help greatly appreciated.

在运行drop table命令之前？我试图遵循脚本，但找不到脚本的确定命令部分（可能是由于无知/缺乏理解）。任何帮助非常感谢。

#!/usr/bin/perl

use strict;
use DBI;

my $hostname = '';
my $database = '';
my $username = '';
my $password = '';

my $dbh = DBI->connect("dbi:mysql:${database}:$hostname",
  $username, $password) or die "Error: $DBI::errstr\n";

my $sth = $dbh->prepare("SHOW TABLES");
$sth->execute or die "SQL Error: $DBI::errstr\n";
my $i = 0;
my @all_tables = ();
while(my $table = $sth->fetchrow_array)
{
  $i++;
  print "table $i: $table\n";
  push @all_tables, $table;
}
my $total_table_count = $i;

print "Enter string or regex to match tables to "
  . "delete (won't delete yet): ";
my $regex = <STDIN>;
chomp $regex;

$i = 0;
my @matching_tables = ();
foreach my $table (@all_tables)
{
  if($table =~ /$regex/i)
  {
    $i++;
    print "matching table $i: $table\n";
    push @matching_tables, $table;
  }
}
my $matching_table_count = $i;

if($matching_table_count)
{
  print "$matching_table_count out of $total_table_count "
    . "tables match, and will be deleted.\n";
  print "Delete tables now? [y/n] ";
  my $decision = <STDIN>;
  chomp $decision;

  $i = 0;
  if($decision =~ /y/i)
  {
    foreach my $table (@matching_tables)
    {
      $i++;
      print "deleting table $i: $table\n";
      my $sth = $dbh->prepare("DROP TABLE $table");
      $sth->execute or die "SQL Error: $DBI::errstr\n";
    }
  }
  else
  {
    print "Not deleting any tables.\n";
  }
}
else
{
  print "No matching tables.\n";
}

1 个解决方案

#1

Setting the FOREIGN_KEY_CHECKS value to zero:

将FOREIGN_KEY_CHECKS值设置为零：

SET FOREIGN_KEY_CHECKS = 0;

...prior to the drop scripts will disable foreign key constraints instance wide. Because you can have more than one catalog/database on a MySQL instance, this risks impacting anything else database-wise.

...在删除脚本之前将禁用实例范围内的外键约束。因为您可以在MySQL实例上拥有多个目录/数据库，所以这可能会影响数据库方面的任何其他内容。

The general habit is to script these in order of key dependency, deleting/truncating data from parent tables before children.

一般的习惯是按照密钥依赖的顺序编写这些脚本，在子代之前从父表中删除/截断数据。

#1