2004/05/13 ３－４　データ型(カラムタイプ) について 発表者：藤村元彦 自然言語処理研究室

指定のデータ型と違うデータを入力した場合 データ型が文字列型のカラムに数値データを入れることは可能だが、計算や検索がうまく行われない。また、数値型のカラムに文字列を入れようとすると、自動的にゼロが代入される。 次の例は、4つ目のレコードの数値型カラムAGE_Cに文字列を代入した場合である。

型の違うデータの入力例 mysql> insert into FRIENDS_T set ID_C = 4, AGE_C = "大阪"; Query OK, 1 row affected (0.00 sec) mysql> select * from FRIENDS_T; +------+----------+---------------------------+-------+ | ID_C | NAME_C | ADD_C | AGE_C | | 1 | 大友達子 | 東京都千代田区何処町1-1-1 | 23 | | 2 | 小友達夫 | 東京都葛飾区何処町1-1-1 | 45 | | 3 | 友田知香 | 青森県青森市何処町2-2-2 | 17 | | 4 | NULL | NULL | 0 | 4 rows in set (0.00 sec)

データ型の自動変更 MySQLでは、指定されたデータの種類によってカラムの型が自動的に変化することがある。例えば、4文字以上のchar型はvarchar型に、3文字以下のvarchar型はchar型に変更される。 また、timestamp型では、2から14までの偶数をサイズとして指定するが、０や14を超える値の場合は自動的に14になる。奇数の場合は、一つ大きな値がサイズとなる。

enumとsetの使い方 enum型、set型は、型の定義時にリストで指定したデータ以外は入力できない。以下の例では、カラムに入力可能なデータのリストをenum型で定義し、そこにリストにないデータや、リストにある2つ以上データを入力しようとした場合で、その場合はエラーとなる。 mysql> create table TEST_ENUM(ORDER_NO int,PC_GOODS -> enum(“モニタカバー”,“マウスパッド”,“FDケース”, 　　-> “スピーカ”)); Query OK, 0 rows affected (0.03 sec)

enumとsetの使い方の続き mysql> insert into TEST_ENUM(ORDER_NO,PC_GOODS) -> values(1,‘マウスパッド’),(2,‘スピーカ’ ), -> (3,‘FDケース’),(4,‘スピーカ,マウスパッド,スピーカ’ ), -> (5,'ケーブル' ; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 2 mysql> select * from TEST_ENUM; +----------+--------------+ | ORDER_NO | PC_GOODS | | 1 | マウスパッド | | 2 | スピーカ | | 3 | FDケース | | 4 | | | 5 | | 5 rows in set (0.00 sec)

日付型データ 日付型データは、文字列型や数値型についでよく使われるデータ型である。日付型のデータは、yyyy-mm-ddの型(4桁の西暦年、2桁の月と日)で格納され、次のように、カラムのデータ型として「date」と指定する。 mysql> create table -> DATE_T(ID int auto_increment not null -> primary key,BIRTHDAY date); Query OK, 0 rows affected (0.01 sec)

日付型データの入力例 mysql> insert into DATE_T set BIRTHDAY = "1999-01-01"; Query OK, 1 row affected (0.01 sec) mysql> insert into DATE_T set BIRTHDAY = "19800125"; Query OK, 1 row affected (0.00 sec) mysql> insert into DATE_T set BIRTHDAY = "2000/02/06"; mysql> select * from DATE_T; +----+------------+ | ID | BIRTHDAY | | 1 | 1999-01-01 | | 2 | 1980-01-25 | | 3 | 2000-02-06 | 3 rows in set (0.00 sec)

日付型データの入力方法 以上のように日付型データは、「/」で区切ったり、区切りがなくても入力可能である。 また、西暦を2桁で入力すると、4桁に変換されて登録される。MySQL3.22以降のバージョンでは1901～2155までの数値が年として自動判断されるので、意図しない変換を避けるためにも、西暦は4桁で入力するべきである。

不正な日付型データ なお、米国などで使われているmm-dd-yyyy型には対応しておらず、入力しても0000-00-00となる。 mysql> insert into DATE_T set BIRTHDAY = "06/25/1980"; Query OK, 1 row affected (0.00 sec) mysql> select * from DATE_T; +----+------------+ | ID | BIRTHDAY | | 1 | 1999-01-01 | | 2 | 1980-01-25 | | 3 | 2000-02-06 | | 4 | 0000-00-00 | 4 rows in set (0.00 sec)

日付関数による日付の操作 date_format()関数を使うと、既定の形式以外でも表示することなら可能である。 mysql> select date_format(BIRTHDAY,"%m %d,%y") from DATE_T; +----------------------------------+ | date_format(BIRTHDAY,"%m %d,%y") | | 01 01,99 | | 01 25,80 | | 02 06,00 | 3 rows in set (0.00 sec)

date_format()の西暦4桁表示 西暦を4桁表示にするには、%yを%Yと大文字にする。 mysql> select date_format(BIRTHDAY,"%m %d,%Y") from DATE_T; +----------------------------------+ | date_format(BIRTHDAY,"%m %d,%Y") | | 01 01,1999 | | 01 25,1980 | | 02 06,2000 | 3 rows in set (0.00 sec)

現在の日付と時間 現在の日付を知るにはcurdate関数、現在の時刻を知るにはcurtime関数を使う。 現在日時： mysql> select curdate(); 現在時刻： mysql> select curtime();

年のみを表示する関数：year() 誕生日と現在日時から歳を調べるには、日付から年のみを取り出すyear()関数を使って計算する。 mysql> select year(curdate()) - year(BIRTHDAY), -> BIRTHDAY from DATE_T; +----------------------------------+------------+ | year(curdate()) - year(BIRTHDAY) | BIRTHDAY | | 5 | 1999-01-01 | | 24 | 1980-01-25 | | 4 | 2000-02-06 | 3 rows in set (0.02 sec)

期間を定義：date_add() 以下の例は、REGISTRATIONという登録日カラムを持つテーブルから、1年半有効のカードなどが無効になる日を計算したものである。 mysql> select REGISTRATION, -> date_add(REGISTRATION,interval 18 month) as -> “失効日” from DATE_T; +--------------+------------+ | REGISTRATION | 失効日 | | 1999-01-01 | 2000-07-01 | | 1980-01-25 | 1981-07-25 | | 2000-02-06 | 2001-08-06 | 3 rows in set (0.00 sec)