Table of Contents
概要
- DBI から BIT 型にバインドする際には pack('C*',$x) してからバインドするべし。
- BIT は TINYINT として実装されている。
- 格納できる数値より大きな値を格納しようとした場合は全ビットが1になった値が格納される。
ソース
testBit.pl
#!/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
結果
無変換
id | Type | 値 | bit1 | bit4 | bit8 | bit12 | bit16 |
---|---|---|---|---|---|---|---|
1 | 'N' | "\x01" | "\x0F" | "\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)
id | Type | 値 | bit1 | bit4 | bit8 | bit12 | bit16 |
---|---|---|---|---|---|---|---|
43 | B | "\0" | "\0" | "\0" | "\0\0" | "\0\0" | |
44 | B | 1 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
45 | B | 10 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
46 | B | 100 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
47 | B | 1000 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
48 | B | 10000 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
49 | B | 100000000 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
50 | B | '100000000000' | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
51 | B | '1000000000000' | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
52 | B | '10000000000000000' | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
53 | B | '01' | "\0" | "\x0F" | "@" | "\x0F\xFF" | "@\0" |
54 | B | '001' | "\0" | "\x0F" | " " | "\x0F\xFF" | " \0" |
55 | B | '0001' | "\0" | "\x0F" | "\x10" | "\x0F\xFF" | "\x10\0" |
56 | B | '00001' | "\0" | "\0" | "\b" | "\b\0" | "\b\0" |
57 | B | '000000001' | "\0" | "\0" | "\0" | "\0\x80" | "\0\x80" |
58 | B | '000000000001' | "\0" | "\0" | "\0" | "\0\x10" | "\0\x10" |
59 | B | '0000000000001' | "\0" | "\0" | "\0" | "\0\0" | "\0\b" |
60 | B | '00000000000000001' | "\0" | "\0" | "\0" | "\0\0" | "\0\0" |
61 | B | '010' | "\0" | "\x0F" | "@" | "\x0F\xFF" | "@\0" |
62 | B | '0010' | "\0" | "\x0F" | " " | "\x0F\xFF" | " \0" |
63 | B | '00010' | "\0" | "\x0F" | "\x10" | "\x0F\xFF" | "\x10\0" |
64 | B | '000010' | "\0" | "\0" | "\b" | "\b\0" | "\b\0" |
65 | B | '0000000010' | "\0" | "\0" | "\0" | "\0\x80" | "\0\x80" |
66 | B | '0000000000010' | "\0" | "\0" | "\0" | "\0\x10" | "\0\x10" |
67 | B | '00000000000010' | "\0" | "\0" | "\0" | "\0\0" | "\0\b" |
68 | B | '000000000000000010' | "\0" | "\0" | "\0" | "\0\0" | "\0\0" |
69 | B | 2 | "\0" | "\0" | "\0" | "\0\0" | "\0\0" |
70 | B | 3 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
71 | B | 4 | "\0" | "\0" | "\0" | "\0\0" | "\0\0" |
72 | B | 5 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
73 | B | 6 | "\0" | "\0" | "\0" | "\0\0" | "\0\0" |
74 | B | 7 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
75 | B | 8 | "\0" | "\0" | "\0" | "\0\0" | "\0\0" |
76 | B | 10 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
77 | B | 12 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
78 | B | 15 | "\x01" | "\x0F" | "\xC0" | "\x0F\xFF" | "\xC0\0" |
79 | B | 16 | "\x01" | "\x0F" | "\x80" | "\x0F\xFF" | "\x80\0" |
80 | B | 17 | "\x01" | "\x0F" | "\xC0" | "\x0F\xFF" | "\xC0\0" |
81 | B | 255 | "\0" | "\x0F" | "" | "\x0F\xFF" | " \0" |
||
82 | B | 256 | "\0" | "\x0F" | "@" | "\x0F\xFF" | "@\0" |
83 | B | 512 | "\x01" | "\x0F" | "\xC0" | "\x0F\xFF" | "\xC0\0" |
84 | B | 513 | "\x01" | "\x0F" | "\xE0" | "\x0F\xFF" | "\xE0\0" |
pack('C*',$x)
id | Type | 値 | bit1 | bit4 | bit8 | bit12 | bit16 |
---|---|---|---|---|---|---|---|
85 | C | "\0" | "\0" | "\0" | "\0\0" | "\0\0" | |
86 | C | 1 | "\x01" | "\x01" | "\x01" | "\0\x01" | "\0\x01" |
87 | C | 10 | "\x01" | "\n" | "\n" | "\0\n" | "\0\n" |
88 | C | 100 | "\x01" | "\x0F" | d | "\0d" | "\0d" |
89 | C | 1000 | "\x01" | "\x0F" | "\xFF" | "\x03\xE8" | "\x03\xE8" |
90 | C | 10000 | "\x01" | "\x0F" | "\xFF" | "\x0F\xFF" | "'\x10" |
91 | C | 100000000 | "\x01" | "\x0F" | "\xFF" | "\x0F\xFF" | "\xFF\xFF" |
92 | C | '100000000000' | "\x01" | "\x0F" | "\xFF" | "\x0F\xFF" | "\xFF\xFF" |
93 | C | '1000000000000' | "\x01" | "\x0F" | "\xFF" | "\x0F\xFF" | "\xFF\xFF" |
94 | C | '10000000000000000' | "\x01" | "\x0F" | "\xFF" | "\x0F\xFF" | "\xFF\xFF" |
95 | C | '01' | "\x01" | "\x01" | "\x01" | "\0\x01" | "\0\x01" |
96 | C | '001' | "\x01" | "\x01" | "\x01" | "\0\x01" | "\0\x01" |
97 | C | '0001' | "\x01" | "\x01" | "\x01" | "\0\x01" | "\0\x01" |
98 | C | '00001' | "\x01" | "\x01" | "\x01" | "\0\x01" | "\0\x01" |
99 | C | '000000001' | "\x01" | "\x01" | "\x01" | "\0\x01" | "\0\x01" |
100 | C | '000000000001' | "\x01" | "\x01" | "\x01" | "\0\x01" | "\0\x01" |
101 | C | '0000000000001' | "\x01" | "\x01" | "\x01" | "\0\x01" | "\0\x01" |
102 | C | '00000000000000001' | "\x01" | "\x01" | "\x01" | "\0\x01" | "\0\x01" |
103 | C | '010' | "\x01" | "\n" | "\n" | "\0\n" | "\0\n" |
104 | C | '0010' | "\x01" | "\n" | "\n" | "\0\n" | "\0\n" |
105 | C | '00010' | "\x01" | "\n" | "\n" | "\0\n" | "\0\n" |
106 | C | '000010' | "\x01" | "\n" | "\n" | "\0\n" | "\0\n" |
107 | C | '0000000010' | "\x01" | "\n" | "\n" | "\0\n" | "\0\n" |
108 | C | '0000000000010' | "\x01" | "\n" | "\n" | "\0\n" | "\0\n" |
109 | C | '00000000000010' | "\x01" | "\n" | "\n" | "\0\n" | "\0\n" |
110 | C | '000000000000000010' | "\x01" | "\n" | "\n" | "\0\n" | "\0\n" |
111 | C | 2 | "\x01" | "\x02" | "\x02" | "\0\x02" | "\0\x02" |
112 | C | 3 | "\x01" | "\x03" | "\x03" | "\0\x03" | "\0\x03" |
113 | C | 4 | "\x01" | "\x04" | "\x04" | "\0\x04" | "\0\x04" |
114 | C | 5 | "\x01" | "\x05" | "\x05" | "\0\x05" | "\0\x05" |
115 | C | 6 | "\x01" | "\x06" | "\x06" | "\0\x06" | "\0\x06" |
116 | C | 7 | "\x01" | "\a" | "\a" | "\0\a" | "\0\a" |
117 | C | 8 | "\x01" | "\b" | "\b" | "\0\b" | "\0\b" |
118 | C | 10 | "\x01" | "\n" | "\n" | "\0\n" | "\0\n" |
119 | C | 12 | "\x01" | "\f" | "\f" | "\0\f" | "\0\f" |
120 | C | 15 | "\x01" | "\x0F" | "\x0F" | "\0\x0F" | "\0\x0F" |
121 | C | 16 | "\x01" | "\x0F" | "\x10" | "\0\x10" | "\0\x10" |
122 | C | 17 | "\x01" | "\x0F" | "\x11" | "\0\x11" | "\0\x11" |
123 | C | 255 | "\x01" | "\x0F" | "\xFF" | "\0\xFF" | "\0\xFF" |
124 | C | 256 | "\x01" | "\x0F" | "\xFF" | "\x01\0" | "\x01\0" |
125 | C | 512 | "\x01" | "\x0F" | "\xFF" | "\x02\0" | "\x02\0" |
126 | C | 513 | "\x01" | "\x0F" | "\xFF" | "\x02\x01" | "\x02\x01" |
テーブル
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;
リンク
- Perl/packBit
MySQL