« Mac De Mo Oracle SQL Developer | トップページ | Mac De Mo Oracle SQL Developer #2(遊んでみた) »

2006年2月28日 (火) / Author : Hiroshi Sekiguchi.

Mac De Oracle Heterogeneous! #51

文字型のつづき。今回から、MySQLの文字型の特徴を簡単検証していく。

PowerBook G4 MacOSX Tiger 10.4.5のMySQL4.0.25及び、PowerMac G5 Dual2.7GhzのMySQL4.1.13aを利用した。


gencon_blog_img33

尚、データベース及び、クライアントキャラクタセットともにSJISである。

MySQL4.0.25及び、MySQL4.1.13aには、以下のような表を作成した。。。のだが・・・・・。

mysql> create table char_testA
-> (
-> id int primary key,
-> r_char_20 char(20),
-> r_char_20_binary char(20) binary,
-> r_char_255 char(255),
-> r_char_255_binary char(255) binary,
-> r_varchar_20 varchar(20),
-> r_varchar_20_binary varchar(20) binary,
-> r_varchar_255 varchar(255),
-> r_varchar_255_binary varchar(255) binary,
-> r_tinytext tinytext,
-> r_tinyblob tinyblob,
-> r_text text,
-> r_blob blob
-> ) engine=innodb;

Query OK, 0 rows affected (0.10 sec)

char型で定義した列が全て varchar型になっていたのである。あれれ、何故????

mysql> desc char_test;
+----------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| r_char_20 | varchar(20) | YES | | NULL | |
| r_char_20_binary | varchar(20) binary | YES | | NULL | |
| r_char_255 | varchar(255) | YES | | NULL | |
| r_char_255_binary | varchar(255) binary | YES | | NULL | |
| r_varchar_20 | varchar(20) | YES | | NULL | |
| r_varchar_20_binary | varchar(20) binary | YES | | NULL | |
| r_varchar_255 | varchar(255) | YES | | NULL | |
| r_varchar_255_binary | varchar(255) binary | YES | | NULL | |
| r_tinytext | tinytext | YES | | NULL | |
| r_tinyblob | tinyblob | YES | | NULL | |
| r_text | text | YES | | NULL | |
| r_blob | blob | YES | | NULL | |
----------------------+---------------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

DDLもvarcharに変更されている。
mysql> show create table char_test¥G
*************************** 1. row ***************************
Table: char_test
Create Table: CREATE TABLE `char_test` (
`id` int(11) NOT NULL default '0',
`r_char_20` varchar(20) default NULL,
`r_char_20_binary` varchar(20) binary default NULL,
`r_char_255` varchar(255) default NULL,
`r_char_255_binary` varchar(255) binary default NULL,
`r_varchar_20` varchar(20) default NULL,
`r_varchar_20_binary` varchar(20) binary default NULL,
`r_varchar_255` varchar(255) default NULL,
`r_varchar_255_binary` varchar(255) binary default NULL,
`r_tinytext` tinytext,
`r_tinyblob` tinyblob,
`r_text` text,
`r_blob` blob,
PRIMARY KEY (`id`)
) TYPE=InnoDB
1 row in set (0.00 sec)

mysql>

慌てて、マニュアルを確認してみると、

マニュアルから引用ここから


6.5.3.1. カラムの暗黙的な変更

場合によっては、CREATE TABLE ステートメントで指定されているカラムの型、属性が、MySQL によって暗黙的に変更されることがあります(このような変更は ALTER TABLE でも発生する場合があります)。

長さが 4 文字に満たない VARCHAR 型のカラムは CHAR 型に変更される。

テーブルのいずれかのカラムが可変長である場合は、結果的にそのレコード全体が可変長になる。したがって、テーブルに可変長のカラム(VARCHAR、TEXT、BLOB)が含まれている場合、長さが 3 文字を超す CHAR 型のカラムはいずれも VARCHAR 型カラムに変更される。これはカラムの使用方法には影響しない。MySQL では、VARCHAR 型は単に文字を格納するもう 1 つの手段として使用されている。MySQL でこの変換が実行される理由は、スペースを節約し、テーブル処理を迅速化するためである。 See 章 7. MySQL のテーブル型。

バージョン 4.1.0 以降では、255 文字を超える長さを持つ CHAR 型または VARCHAR 型のフィールドはいずれも TEXT 型に変換される。 これは互換性を考慮した機能。


マニュアルから引用ここまで


仕様なら仕方ない。(バグかと思ったよ。。。。)
ということで、char型本来の癖を見る為、char型だけの表とvarchar型、text型、blob型の表に分ける事にした。(尚、mediumtext型、mediumblob型、longtext型及び、longblob型は検証対象外とした。)

char(n) と char(n) binaryの違いは、'A'と'a'を区別するかしないかということ。binary属性を付けると大文字と小文字を区別するようになる。この特徴は要注意だ。PostgreSQLもOracleもDB2でも大文字小文字は区別される、区別したくない場合には、スカラ関数を使い大文字又は、小文字に統一してから比較するからだ。もし、Generic Connectivityを経由してアクセスするMySQLの表がbinary属性のないchar型やvarchar型だと、ソートは注意が必要かもしれない。(ソートについては、Generic Connectivity経由でも検証する)
また、blob型なのに何故、文字型の検証で登場するのかと感じる方もいると思うのだが、blob型は Oracleのlob型に対応するのでは? と思っているのだが、MySQLでは文字列型に分類されており、大文字小文字を区別したい文字列を格納するための型となっている、ちなみに、text型は大文字小文字を区別しない。これらの型の詳細は、MySQLのマニュアル又は、MySQLによる最速RDBMS構築ガイドを参照するとよいだろう。

mysql> create table char_test
-> (
-> id int primary key,
-> r_char_20 char(20),
-> r_char_20_binary char(20) binary,
-> r_char_255 char(255),
-> r_char_255_binary char(255) binary
-> ) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> create table varchar_test
-> (
-> id int primary key,
-> r_varchar_20 varchar(20),
-> r_varchar_20_binary varchar(20) binary,
-> r_varchar_255 varchar(255),
-> r_varchar_255_binary varchar(255) binary,
-> r_tinytext tinytext,
-> r_tinyblob tinyblob,
-> r_text text,
-> r_blob blob
-> ) engine=innodb;
Query OK, 0 rows affected (0.02 sec)


ちなみに、一旦, char型からvarchar型に変更されてしまうと、全列を固定長に戻したとしても、varchar型がchar型に戻されることは無いようだ。以下のログ参照。(ん〜〜〜〜・・・・。いろいろ書きたい気分だがノーコメント。) 
まず、char型だけの表を作成。この定義だとすべてchar型として定義されている。

Welcome to Darwin!
G5Server:˜ discus$ mysql -u discus -p mysqldb41
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or ¥g.
Your MySQL connection id is 1 to server version: 4.1.13a

Type 'help;' or '¥h' for help. Type '¥c' to clear the buffer.

mysql> create table char_test
-> (
-> id int primary key,
-> r_char_20 char(20),
-> r_char_20_binary char(20) binary,
-> r_char_255 char(255),
-> r_char_255_binary char(255) binary
-> ) engine=innodb;
Query OK, 0 rows affected (0.22 sec)

mysql> desc char_test;
+-------------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| r_char_20 | char(20) | YES | | NULL | |
| r_char_20_binary | char(20) | YES | | NULL | |
| r_char_255 | char(255) | YES | | NULL | |
| r_char_255_binary | char(255) | YES | | NULL | |
+-------------------+-----------+------+-----+---------+-------+
5 rows in set (0.01 sec)

固定長の文字列しかない表であれば、char型のままなのだが、char型とvarchar型(text型、blob型も含む)が混在すると、既存char型は全て、自動的にvarchar型に変更されてしまう。MySQLのchar型、varchar型の仕様からすると、どちらの型でもいいのかもしれないが、他の(PostgreSQLやOracleなど)RDBMSと互換を持たせた挙動にしたい場合には困るかもしれない。(Generic Connectivity経由の挙動確認ではこの辺りも忘れずに!)
mysql> alter table char_test add column r_varchar_20 varchar(20);
Query OK, 0 rows affected (0.52 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc char_test;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| r_char_20 | varchar(20) | YES | | NULL | |
| r_char_20_binary | varchar(20) | YES | | NULL | |
| r_char_255 | varchar(255) | YES | | NULL | |
| r_char_255_binary | varchar(255) | YES | | NULL | |
| r_varchar_20 | varchar(20) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+-------+
6 rows in set (0.03 sec)


一旦、char型からvarchar型に自動変更されてしまった表は、そのきっかけになった列を削除しても元には戻らない。
どうしても、char型に戻したいのなら表を再作成するしかない。

mysql> alter table char_test drop column r_varchar_20;
Query OK, 0 rows affected (0.71 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc char_test;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| r_char_20 | varchar(20) | YES | | NULL | |
| r_char_20_binary | varchar(20) | YES | | NULL | |
| r_char_255 | varchar(255) | YES | | NULL | |
| r_char_255_binary | varchar(255) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

mysql> drop table char_test;
Query OK, 0 rows affected (0.05 sec)

mysql>

と、戸惑う仕様がいろいろあるのだが、なんとかやっている。ということで次回へつづく。

| |

トラックバック


この記事へのトラックバック一覧です: Mac De Oracle Heterogeneous! #51:

コメント

コメントを書く