テーブル作成(CREATE TABLE)・データ型

Tag:

データ型やオプションなど考慮してテーブルを作成しましょう。一度作成したテーブル構造の変更方法についても説明します。

テーブル作成の基本

CREATE TABLEを使ってテーブルを作成します。

/* 書式 */
CREATE  TABLE  DB名.テーブル名 (
  カラム名1  データ型  オプション, 
  カラム名2  データ型  オプション, 
  ...
  オプション
);


/* 例 */
CREATE TABLE `sampleDB`.`sampleTable` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR( 30 ) NULL DEFAULT NULL ,
  `update_time` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY ( `ID` ) 
)

使用頻度の高いデータ型

データ型 範囲 備考
boolean tinyint(1)が使われる。
0がfalseで、それ以外がtrueになる。
int -2147483648 ~ 2147483647

符号なしの場合
0 ~ 4294967295

4バイト
bigint -9223372036854775808 ~ 9223372036854775807

符号なしの場合
0 ~ 18446744073709551615

8バイト
float -3.402823466E+38 ~ -1.175494351E-38
0
1.175494351E-38 ~ 3.402823466E+38
概数値として格納
4バイト
(符号部1ビット, 指数部8ビット, 仮数部23ビット)
double -1.7976931348623157E+308 ~ -2.2250738585072014E-308
0
2.2250738585072014E-308 ~ 1.7976931348623157E+308
概数値として格納
8バイト
(符号部1ビット, 指数部11ビット, 仮数部52ビット)
decimal 真数値として格納
varchar(M) Mはカラムの最大の長さを文字数で表す。
Mの範囲は 0 ~ 65,535
可変長文字列
text 最大 65,535 (216 -1) 文字のデータを格納 値にマルチバイト文字が含まれる場合、有効な最大長は小さくなる
date ‘1000-01-01’ ~ ‘9999-12-31’ 3バイト
datetime ‘1000-01-01 00:00:00’ ~
‘9999-12-31 23:59:59’
8バイト

タイムゾーンの情報をもたない。そのため、地球上のどこの国の時間か特定できいので、サーバー変更時などに注意が必要。

timestamp ‘1970-01-01 00:00:01’ ~
‘2038-01-19 03:14:07’
4バイト

INSERT または UPDATE 操作の日付と時刻を自動記録するのに利用されることが多い。

タイムゾーン情報をもつが、2038年問題に注意。

geometry 5.7からInnoDBでもキーをはれる。

SPATIAL KEY `latlng` (`latlng`)

詳しくは以下で取り上げてます。
MySQL : Geometry型の動作確認

桁数指定

float、double、decimalは精度(数値を表現する細かさ)小数点以下の桁数を次のように指定できます。

DOUBLE(全体の桁数, 小数点以下の桁数)

float、doubleは丸め誤差に注意

floatとdoubleは、仮数部の精度以上の数値を保存すると丸め処理が行われます。10進法の小数の多くが2進法では無限小数となることが多く、無限小数を有限の型に入れるため丸め処理が頻繁に行われます。そのため、「=演算子」「<>演算子」による値の比較を行う場合には、floatやdoubleを使用してはいけません。小数部がある数値を指定どおり正確に格納したい場合は、decimalを利用します。

動作確認用にテーブルを作成します。次のクエリを実行します。

CREATE TABLE `test`.`test` (
 `float` FLOAT NOT NULL ,
 `float2` FLOAT(5,2) NOT NULL ,
 `double` DOUBLE NOT NULL ,
 `double2` DOUBLE(5,2) NOT NULL ,
 `decimal` DECIMAL NOT NULL,
 `decimal2` DECIMAL(5,2) NOT NULL
) ENGINE = InnoDB;

下記テーブルが作成されました。
mysql_decimal
データを挿入します。

INSERT INTO `test` (`float`, `float2`, `double`, `double2`, `decimal`, `decimal2`) 
VALUES (0.1, 0.1, 0.1, 0.1, 0.1, 0.1);

結果は以下の通りです。
mysql_decimal1

100000000倍してみます。

SELECT `float` * 100000000, 
       `float2` * 100000000,
       `double` * 100000000, 
       `double2` * 100000000, 
       `decimal` * 100000000, 
       `decimal2` * 100000000 
FROM `test`

mysql_decimal2

datetime、timestamp

MySQL 5.6.5以前だと、DATETIME型のカラムにCURRENT_TIMESTAMPを設定できない。 また、CURRENT_TIMESTAMPを設定したTIMESTAMP型はテーブル内に1つだけしか作成できない。
バージョンによる動作の違いについては、下記リンクを参照。
https://dev.mysql.com/doc/refman/5.6/ja/timestamp-initialization.html

テーブル作成時に利用可能なオプション

オプション 概要
PRIMARY UNIQUEとINDEXを指定したのと同じ。NULL入力は不可
UNIQUE 重複したものを入力しようとしたときエラーとする。NULL入力は可能
INDEX インデックスを作成。一般的に検索は速くなるが、データ挿入が遅くなる可能性がある。
NULL または
NOT NULL
NOT NULLを指定したカラムは入力必須となる
AUTO_INCREMENT 自動採番
DEFAULT データ挿入時のデフォルト値。
『DEFAULT CURRENT_TIMESTAMP』 と指定することで、日付時刻を自動初期化
ON UPDATE CURRENT_TIMESTAMP 日付時刻を自動更新
FOREIGN KEY 外部キー制約

オプション

ON UPDATE reference_option
ON DELETE reference_option

reference_optionに設定できる値は以下の通りです。

RESTRICT エラーになる(デフォルト)
CASCADE 参照先の変更と同じ変更が参照元で行われる
SET NULL 参照元でNULLになる
NO ACTION RESTRICTと同じ

利用例です。

// 参照先が更新されたら同時更新。参照先が削除されたらNULL設定
 FOREIGN KEY (`顧客コード`) REFERENCES 顧客(`顧客コード`)
  ON UPDATE CASCADE
  ON DELETE SET NULL

※詳しくはこちら
MySQL : 外部キー制約(FOREIGN KEY) | DN-Web64

NOT NULL制約の動作確認

NOT NULL制約の動きは特殊なところがあります。次のクエリで作成したテーブルを例に動作確認します。

CREATE TABLE `test`.`test` (
  `id` INT NOT NULL ,
  `name` VARCHAR(30) NOT NULL ,
  `old` INT NOT NULL
)

1レコードインサート

INSERT INTO `test` (`id`, `name`, `old`) 
VALUES ('1', NULL, NULL);

上記クエリを実行すると、「Column ‘name’ cannot be null」といったエラーになりました。

複数レコードインサート

INSERT INTO `test` (`id`, `name`, `old`) 
VALUES ('1', NULL, NULL),('2', NULL, NULL);

複数レコードだと挿入できました。
mysql_notnull

VARCHAR型には空文字が入力され、INT型には0が入力されています。

未指定での1レコード入力

INSERT INTO `test` (`id`) 
VALUES (3);

上記のようにNOT NULL制約を設定したカラムを指定しなかった場合も、レコード挿入できました。
mysql_notnull1

テーブル構造の変更

ALTER TABLEを使ってテーブル構造を変更します。

/* カラムの追加 */
ALTER TABLE テーブル名 ADD カラム名 データ型 オプション;

/* カラム指定の変更 */
ALTER TABLE テーブル名 CHANGE 変更前のカラム名 変更後のカラム名 データ型 オプション;

/* カラムの削除 */
ALTER TABLE テーブル名 DROP カラム名;

スポンサーリンク