MySQL BIT型

Table of Contents

MySQL BIT型

概要

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

ソース

testBit.zip

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
PerlMonks - The Monastery Gates
- Handle MySQL BIT data type in Perl

MySQL-BIT

MySQL BIT型

概要

ソース

testBit.pl

結果

無変換

pack('Bn',$x)

pack('C*',$x)

テーブル

リンク

Perl