MySQL BIT型

概要

  • DBI から BIT 型にバインドする際には pack('C*',$x) してからバインドするべし。
  • BIT は TINYINT として実装されている。
  • 格納できる数値より大きな値を格納しようとした場合は全ビットが1になった値が格納される。

ソース

testBit.pl

すべてを展開すべてを収束
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
-
|
|
|
|
|
!
-
|
|
|
!
 
 
 
 
 
 
 
 
 
 
 
-
!
!
|
|
|
|
|
|
|
|
|
-
-
|
-
-
|
-
|
-
|
!
!
|
|
!
!
|
|
|
|
|
-
 
 
!
|
|
|
|
|
|
|
|
|
-
 
 
-
-
|
|
|
!
-
|
!
 
!
|
|
#!/usr/bin/perl
# testBit.pl
# MySQL の Bit 型の取り扱いテスト
 
use strict;
use warnings;
use utf8;
use Encode;
use YAML::Syck;
use JSON::Syck;
use lib qw( /home/Shared/lib/ );
use DBIx::NamedParams;
 
$YAML::Syck::ImplicitUnicode = 1;
$YAML::Syck::ImplicitTyping = 1;
$YAML::Syck::Headless = 1;
 
my $charsetConsole    = 'UTF-8';
#my $charsetConsole	= 'CP932';
my $charsetFile        = 'UTF-8';
 
binmode( STDIN,  ":encoding($charsetConsole)" );
binmode( STDOUT, ":encoding($charsetConsole)" );
binmode( STDERR, ":encoding($charsetConsole)" );
 
my $config_file = 'config.yaml';
 
my $sql_insert = qq{
    INSERT INTO 
        `test_bit`
        ( `packtype`, `value`, `bit1`, `bit4`, `bit8`, `bit12`, `bit16` )
    VALUES
        ( :packtype-VARCHAR, :value-VARCHAR, :bit1-BIT, :bit4-BIT, :bit8-BIT, :bit12-BIT, :bit16-BIT );
};
my $sql_select = qq{
    SELECT    `id`, `packtype`, `value`, `bit1`, `bit4`, `bit8`, `bit12`, `bit16` 
    FROM    `test_bit`
    ORDER BY    `id`
};
 
my @input_types = qw( N B C );
my @input_values = qw(
    0 1 10 100 1000 10000 100000000 100000000000 1000000000000 10000000000000000
    01 001 0001 00001 000000001 000000000001 0000000000001 00000000000000001
    010 0010 00010 000010 0000000010 0000000000010 00000000000010 000000000000000010
    2 3 4 5 6 7 8 10 12 15 16 17 255 256 512 513
);
 
my $config = YAML::Syck::LoadFile( $config_file ) 
    or die( "$config_file: $!\n" );
foreach( keys( %{$config} ) ){
    $config->{'DSN'} =~ s/_${_}_/$config->{$_}/;
}
 
my $dbh = DBI->connect( 
    'DBI:' . $config->{'DSN'}, 
    $config->{'User'}, 
    $config->{'Password'}, 
    $config->{'Options'}
) or die( "$DBI::errstr\n" );
 
my $sth_insert = $dbh->prepare_ex( $sql_insert ) or die( "$DBI::errstr\n" );
foreach my $type ( @input_types ){
    foreach my $value ( @input_values ){
        my $params = { packtype => $type, value => $value };
        foreach my $bit ( qw( 1 4 8 12 16 ) ){
            if ( $type eq 'B' ){
                $params->{ "bit${bit}" } = pack( "B${bit}", $value );
            } elsif( $type eq 'C' ){
                $params->{ "bit${bit}" } = pack( 'C*', int2array( $value ) );
            } else {
                $params->{ "bit${bit}" } = $value;
            }
        }
        $sth_insert->bind_param_ex( $params );
        $sth_insert->execute() or die( "$DBI::errstr\n" );
    }
}
$sth_insert->finish;
 
my $sth_select = $dbh->prepare_ex( $sql_select ) or die( "$DBI::errstr\n" );
$sth_select->execute() or die( "$DBI::errstr\n" );
my @result = ();
while( my $array_ref = $sth_select->fetchrow_arrayref ){
    my @result2 = split( "\n", Dump( $array_ref ) );
    push( @result, join( "\t", map{ /^\s*-\s*(.*)$/msx; $1; } @result2 ) );
}
$sth_select->finish;
 
$dbh->disconnect;
 
print join( "\n", @result ) . "\n";
 
exit;
 
sub int2array
{
    my $arg = shift || 0;
    my @ret = ();
    if ( !!$arg ){
        while( !!$arg ){
            my $m = $arg & 0xff;
            unshift( @ret, $m );
            $arg >>= 8;
        }
    } else {
        @ret = ( 0 );
    }
    return @ret;
}
 
# EOF

結果

無変換

idTypebit1bit4bit8bit12bit16
1'N'0"\x01""\x0F"0"\00""\00"
2'N'1"\x01""\x0F"1"\01""\01"
3'N'10"\x01""\x0F""\xFF""\x0F\xFF"10
4'N'100"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
5'N'1000"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
6'N'10000"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
7'N'100000000"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
8'N''100000000000'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
9'N''1000000000000'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
10'N''10000000000000000'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
11'N''01'"\x01""\x0F""\xFF""\x0F\xFF"'01'
12'N''001'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
13'N''0001'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
14'N''00001'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
15'N''000000001'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
16'N''000000000001'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
17'N''0000000000001'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
18'N''00000000000000001'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
19'N''010'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
20'N''0010'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
21'N''00010'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
22'N''000010'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
23'N''0000000010'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
24'N''0000000000010'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
25'N''00000000000010'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
26'N''000000000000000010'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
27'N'2"\x01""\x0F"2"\02""\02"
28'N'3"\x01""\x0F"3"\03""\03"
29'N'4"\x01""\x0F"4"\04""\04"
30'N'5"\x01""\x0F"5"\05""\05"
31'N'6"\x01""\x0F"6"\06""\06"
32'N'7"\x01""\x0F"7"\07""\07"
33'N'8"\x01""\x0F"8"\08""\08"
34'N'10"\x01""\x0F""\xFF""\x0F\xFF"10
35'N'12"\x01""\x0F""\xFF""\x0F\xFF"12
36'N'15"\x01""\x0F""\xFF""\x0F\xFF"15
37'N'16"\x01""\x0F""\xFF""\x0F\xFF"16
38'N'17"\x01""\x0F""\xFF""\x0F\xFF"17
39'N'255"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
40'N'256"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
41'N'512"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
42'N'513"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"

pack('Bn',$x)

idTypebit1bit4bit8bit12bit16
43B0"\0""\0""\0""\0\0""\0\0"
44B1"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
45B10"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
46B100"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
47B1000"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
48B10000"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
49B100000000"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
50B'100000000000'"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
51B'1000000000000'"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
52B'10000000000000000'"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
53B'01'"\0""\x0F""@""\x0F\xFF""@\0"
54B'001'"\0""\x0F"" ""\x0F\xFF"" \0"
55B'0001'"\0""\x0F""\x10""\x0F\xFF""\x10\0"
56B'00001'"\0""\0""\b""\b\0""\b\0"
57B'000000001'"\0""\0""\0""\0\x80""\0\x80"
58B'000000000001'"\0""\0""\0""\0\x10""\0\x10"
59B'0000000000001'"\0""\0""\0""\0\0""\0\b"
60B'00000000000000001'"\0""\0""\0""\0\0""\0\0"
61B'010'"\0""\x0F""@""\x0F\xFF""@\0"
62B'0010'"\0""\x0F"" ""\x0F\xFF"" \0"
63B'00010'"\0""\x0F""\x10""\x0F\xFF""\x10\0"
64B'000010'"\0""\0""\b""\b\0""\b\0"
65B'0000000010'"\0""\0""\0""\0\x80""\0\x80"
66B'0000000000010'"\0""\0""\0""\0\x10""\0\x10"
67B'00000000000010'"\0""\0""\0""\0\0""\0\b"
68B'000000000000000010'"\0""\0""\0""\0\0""\0\0"
69B2"\0""\0""\0""\0\0""\0\0"
70B3"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
71B4"\0""\0""\0""\0\0""\0\0"
72B5"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
73B6"\0""\0""\0""\0\0""\0\0"
74B7"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
75B8"\0""\0""\0""\0\0""\0\0"
76B10"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
77B12"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
78B15"\x01""\x0F""\xC0""\x0F\xFF""\xC0\0"
79B16"\x01""\x0F""\x80""\x0F\xFF""\x80\0"
80B17"\x01""\x0F""\xC0""\x0F\xFF""\xC0\0"
81B255"\0""\x0F""`""\x0F\xFF""`\0"
82B256"\0""\x0F""@""\x0F\xFF""@\0"
83B512"\x01""\x0F""\xC0""\x0F\xFF""\xC0\0"
84B513"\x01""\x0F""\xE0""\x0F\xFF""\xE0\0"

pack('C*',$x)

idTypebit1bit4bit8bit12bit16
85C0"\0""\0""\0""\0\0""\0\0"
86C1"\x01""\x01""\x01""\0\x01""\0\x01"
87C10"\x01""\n""\n""\0\n""\0\n"
88C100"\x01""\x0F"d"\0d""\0d"
89C1000"\x01""\x0F""\xFF""\x03\xE8""\x03\xE8"
90C10000"\x01""\x0F""\xFF""\x0F\xFF""'\x10"
91C100000000"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
92C'100000000000'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
93C'1000000000000'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
94C'10000000000000000'"\x01""\x0F""\xFF""\x0F\xFF""\xFF\xFF"
95C'01'"\x01""\x01""\x01""\0\x01""\0\x01"
96C'001'"\x01""\x01""\x01""\0\x01""\0\x01"
97C'0001'"\x01""\x01""\x01""\0\x01""\0\x01"
98C'00001'"\x01""\x01""\x01""\0\x01""\0\x01"
99C'000000001'"\x01""\x01""\x01""\0\x01""\0\x01"
100C'000000000001'"\x01""\x01""\x01""\0\x01""\0\x01"
101C'0000000000001'"\x01""\x01""\x01""\0\x01""\0\x01"
102C'00000000000000001'"\x01""\x01""\x01""\0\x01""\0\x01"
103C'010'"\x01""\n""\n""\0\n""\0\n"
104C'0010'"\x01""\n""\n""\0\n""\0\n"
105C'00010'"\x01""\n""\n""\0\n""\0\n"
106C'000010'"\x01""\n""\n""\0\n""\0\n"
107C'0000000010'"\x01""\n""\n""\0\n""\0\n"
108C'0000000000010'"\x01""\n""\n""\0\n""\0\n"
109C'00000000000010'"\x01""\n""\n""\0\n""\0\n"
110C'000000000000000010'"\x01""\n""\n""\0\n""\0\n"
111C2"\x01""\x02""\x02""\0\x02""\0\x02"
112C3"\x01""\x03""\x03""\0\x03""\0\x03"
113C4"\x01""\x04""\x04""\0\x04""\0\x04"
114C5"\x01""\x05""\x05""\0\x05""\0\x05"
115C6"\x01""\x06""\x06""\0\x06""\0\x06"
116C7"\x01""\a""\a""\0\a""\0\a"
117C8"\x01""\b""\b""\0\b""\0\b"
118C10"\x01""\n""\n""\0\n""\0\n"
119C12"\x01""\f""\f""\0\f""\0\f"
120C15"\x01""\x0F""\x0F""\0\x0F""\0\x0F"
121C16"\x01""\x0F""\x10""\0\x10""\0\x10"
122C17"\x01""\x0F""\x11""\0\x11""\0\x11"
123C255"\x01""\x0F""\xFF""\0\xFF""\0\xFF"
124C256"\x01""\x0F""\xFF""\x01\0""\x01\0"
125C512"\x01""\x0F""\xFF""\x02\0""\x02\0"
126C513"\x01""\x0F""\xFF""\x02\x01""\x02\x01"

テーブル

すべてを展開すべてを収束
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
-
|
|
|
|
|
|
|
|
|
!
 
 
 
CREATE TABLE IF NOT EXISTS `test_bit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `packtype` varchar(1) DEFAULT NULL,
  `value` varchar(20) DEFAULT NULL,
  `bit1` bit(1) DEFAULT NULL,
  `bit4` bit(4) DEFAULT NULL,
  `bit8` bit(8) DEFAULT NULL,
  `bit12` bit(12) DEFAULT NULL,
  `bit16` bit(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
DELETE FROM `test_bit` WHERE 1;
ALTER TABLE `test_bit` AUTO_INCREMENT = 1;

添付ファイル: filetestBit.zip 281件 [詳細]

リロード   新規 下位ページ作成 編集 凍結 差分 添付 コピー 名前変更   ホーム 一覧 検索 最終更新 バックアップ リンク元   ヘルプ   最終更新のRSS
Last-modified: Sun, 02 Dec 2012 16:39:28 JST (1872d)