select

DBI Perl

fetchとselect

selectrow_array

は

prepare
excute
fetchrow_array

の処理をまとめたメソッド

DBIの使い方

#!/usr/bin/perl

use DBI;

my $data_source = 'DBI:mysql:test:localhost:3306';
my $user = 'user';
my $pass = 'pass';

my $dbh = DBI->connect($data_source,$user,$pass);

# いろいろ

$dbh->disconnect;

select*

col1 col2 col3
a A testA
b B testB
c C testC
d D testD
e E testE

selectrow_array

SELECTした１行目のレコードの配列を返す

my $stm = "select * from test";
my @row_array=$dbh->selectrow_array($stm);
print join (",",@row_array) . "\n";  
---
a,A,testA

selectrow_arrayref

SELECTした１行目のレコードを配列のリファレンスで返す

my $stm = "select * from test";
my $row_arrayref=$dbh->selectrow_arrayref($stm);
print join (",",@$row_arrayref) . "\n";
---
a,A,testA

selectrow_hashref

取得した1行目のレコードをハッシュのリファレンスで返す

my $stm = "select * from test";
my $row_hashref=$dbh->selectrow_hashref($stm);
for my $name (sort keys %$row_hashref) {
    my $value = $row_hashref->{$name};
    print "$name:$value\n";
}  
---
col1:a
col2:A
col3:testA

selectcol_arrayref

SELECTしたレコードの1列目のカラムを配列のリファレンスで返す

my $stm = "select * from test";
my $col_arrayref=$dbh->selectcol_arrayref($stm);
print join (",",@$col_arrayref) . "\n";  
---
a,b,c,d,e

selectall_arrayref

取得したすべてのレコードを二次元配列のリファレンスで返す

my $stm = "select * from test";
my $all_arrayref=$dbh->selectall_arrayref($stm);
for my $aar (@$all_arrayref){
print join (",",@$aar) . "\n";
}  
---
a,A,testA
b,B,testB
c,C,testC
d,D,testD
e,E,testE

selectall_hashref

指定カラムの値をキーにしたハッシュリファレンスを返す
バリューはまたハッシュリファレンスになっていて、カラム名のキーとカラム値のバリューを持つ

my $stm = "select * from test";
my $all_hashref=$dbh->selectall_hashref($stm,'col1');
for my $hashref (sort keys %$all_hashref) {
  print "$hashref\n";

  for my $name (sort keys %{$all_hashref->{$hashref}}) {
    my $value = $all_hashref->{$hashref}{$name};
    print "$name:$value\n";
  }
}
---
a
col1:a
col2:A
col3:testA
b
col1:b
col2:B
col3:testB
c
col1:c
col2:C
col3:testC
d
col1:d
col2:D
col3:testD
e
col1:e
col2:E
col3:testE

参考

Perl DBI - dbi.perl.org
DBI - search.cpan.org
Perlの配列とハッシュを自由に扱う - サンプルコードによるPerl入門
データベースハンドル | Smart

HIVEでgroup by

HIVE

selectするカラムをgroup byしないといけない

select
a,b,c
from test
group by a

する場合

select
a,b,c
from test
group by a,b,c

みたいにする

select
a
b
count(c)
from test
group by a,b

countやsum等はgroup byしなくてもOK

LanguageManual GroupBy - Apache Hive - Apache Software Foundation

InnoDBでTRUNCATEした時のibdファイル

MySQL InnoDB

古いInnoDBではテーブルをTRUNCATEしてもibdファイルは再利用用にのこるらしい

古いバージョンのInnoDBだとTRUNCATE TABLEしてもデータファイルのサイズが減らない件 - shimxmemo

  • 検証

テーブル作成して適当にデータを入れる

CREATE TABLE test (hoge VARCHAR(32),foo VARCHAR(32)) ENGINE=INNODB;

-rw-rw---- 1 mysql mysql     8588 Jan  9 12:20 test.frm
-rw-rw---- 1 mysql mysql 50331648 Jan 16 15:35 test.ibd

TRUNCATE

TRUNCATE test;

-rw-rw---- 1 mysql mysql     8588 Jan  9 12:20 test.frm
-rw-rw---- 1 mysql mysql 50331648 Jan 16 15:36 test.ibd

ibdのサイズは変わらず

ドロップして再作成

DROP TABLE test;
CREATE TABLE test (hoge VARCHAR(32),foo VARCHAR(32)) ENGINE=INNODB;

-rw-rw---- 1 mysql mysql  8588 Jan 16 15:40 test.frm
-rw-rw---- 1 mysql mysql 98304 Jan 16 15:40 test.ibd

これで再作成されてる

故、ディスク容量を減らしたいのならTRUNCATEの代わりにDROP&CREATE

  • TRUNCATEの代用

InnoDBを新しくすればいんだけど
取り敢えずLIKEしてRENAMEで代用

CREATE TABLE test_tmp LIKE test;
DROP TABLE test;
RENAME test_tmp TO test;

漢(オトコ)のコンピュータ道: InnoDB Pluginことはじめ。快適ストレージエンジン生活はじまる！

テーブルが存在するか確認する方法

DBI Perl

perl,DBIでテーブルが存在するか確認する方法

# table_nameというテーブルが存在するかどうか
$sql = qq{ show tables like 'table_name'; };
$sth = $db->prepare ($sql);
$tbl = $sth->execute ();
$sth->finish;
$db->commit ();

if ($tbl != '0E0'){
    # テーブルが存在する場合の処理
}

テーブルが存在するかどうかをshow tableで取得

select文でないステートメントを実行した場合、executeは影響を受けたレコード数を返します。また、レコード数が0の場合は0E0、レコード数が不明の場合は-1を返します。 DBI/DBDについて

らしいので返り値が'0E0'の時、テーブルは存在しないという判定にしてやればOK

補足

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.1.3 GRANT 構文

別所で指摘してもらったんですがSHOW TABLESは権限の問題があるみたいです

select * 
from information_schema.TABLES 
where TABLE_NAME = 'table' 
and TABLE_SCHEMA = 'database';

テーブル作成時InnoDBが勝手にMyISAMになる

MySQL

InnoDBが有効か確認

mysql> show variables like 'have_innodb'

+---------------+-------+
Variable_name    Value
+---------------+-------+   
have_innodb  NO
+---------------+-------+
1 row in set (0.00 sec)

なってない
ログを見てみる

InnoDB: Error: log file ./ib_logfile0 is of different size 0 268435456 bytes
InnoDB: than specified in the .cnf file 0 5242880  bytes!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

logfileのサイズが設定値をオーバーしていたためInnoDBがエラーになっていました
logfileを削除して再起動したらちゃんとInnoDBが有効になりました
logfileを削除したくない場合は、my.cnfのinnodb-log-file-sizeを大きくすれば良いらしい

PRACTICE

  • テーブルのステータス確認

    show table status like 'hoge'

参考

InnoDBログファイル - MySQL Practice Wiki
MySQL :: MySQL 4.1 リファレンスマニュアル :: 7 MySQL のテーブル型