MySQL Create Sample Data

mysql-sample-create-table.sql

CREATE TABLE house (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `yachin` INT UNSIGNED NOT NULL DEFAULT 0,
  `heibei` VARCHAR(191) NOT NULL,
  `madori` VARCHAR(191) NOT NULL,
  `chiku`  INT NOT NULL,
  PRIMARY KEY (id) )
ENGINE = InnoDB DEFAULT CHARACTER SET utf8;

CREATE TABLE money (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shikikin` INT UNSIGNED NOT NULL DEFAULT 0,
  `reikin` INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id) )
ENGINE = InnoDB DEFAULT CHARACTER SET utf8;

CREATE TABLE area (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `toho` INT UNSIGNED NOT NULL DEFAULT 0,
  `machi` VARCHAR(191) NOT NULL,
  PRIMARY KEY (id) )
ENGINE = InnoDB DEFAULT CHARACTER SET utf8;

mysql-sample-insert-script.pl

#!/usr/bin/env perl

use strict;
use warnings;
use feature 'say';

use DBI;
use Data::WeightedRoundRobin;

my @machi   = ('いろいろある', 'なにもない', '山の上', 'スーパーだけがない');

sub ShikiRei {
    my $arg = Data::WeightedRoundRobin->new([
      { value => '0', weight => 30 },
      { value => '1', weight => 60 },
      { value => '2', weight => 10 },
    ])->next;

    return $arg;
}

sub HeiBei {
    my $arg = Data::WeightedRoundRobin->new([
      { value => '35㎡', weight => 20 },
      { value => '38㎡', weight => 50 },
      { value => '40㎡', weight => 30 },
    ])->next;

    return $arg;
}

sub Madori {
    my $arg = Data::WeightedRoundRobin->new([
      { value => '1DK',  weight => 20 },
      { value => '2K',   weight => 20 },
      { value => '2DK',  weight => 40 },
      { value => '2LDK', weight => 20 },
    ])->next;

    return $arg;
}

my $dbh = DBI->connect(
    'dbi:mysql:database=bukken', 'root', '', {RaiseError => 1, PrintError => 0, AutoCommit => 0}
);

for (1..200) {

    say "yachin";

    for (1..10000) {
        my $yachin = int(rand(5)) + 11;
        print "家賃" . $yachin . "万" . "\t";

        my $hb = &HeiBei();
        print "平米:" . $hb. "\t";

        my $mado = &Madori();
        print "間取り:" . $mado. "\t";

        my $chiku = int(rand(60));
        print "築:" . $chiku . "年" . "\t";

        my $sth = $dbh->prepare(
            "INSERT INTO house (`yachin`, `heibei`, `madori`, `chiku`) VALUE (?, ?, ?, ?)"
        );
        $sth->execute($yachin,$hb,$mado,$chiku);
        $sth->finish();
    }
    say "done";

    say "money";
    for (1..100) {
        my $shikikin = &ShikiRei();
        print "敷金:" . $shikikin . "\t";

        my $reikin= &ShikiRei();
        print "礼金:" . $reikin . "\t";

        my $sth = $dbh->prepare(
            "INSERT INTO money (`shikikin`, `reikin`) VALUE (?, ?)"
        );
        $sth->execute($shikikin,$reikin);
        $sth->finish();
    }
    say "done";

    say "area";
    for (1..100) {
        my $toho = int(rand(30));
        print "徒歩" . $toho . "分" . " ";

        my $n = int(rand(4));
        print "街:" . $machi[$n];

        my $sth = $dbh->prepare(
            "INSERT INTO area (`toho`, `machi`) VALUE (?, ?)"
        );
        $sth->execute($toho,$machi[$n]);
        $sth->finish();
    }
    $dbh->commit;

    say "done";
}
$dbh->disconnect;

1;

prepare と finish は loop の外が良いかと。