MENU
アイデアを形にする( プログラミング / クラウド / フレームワーク / ライブラリ / デザイン / IT豆知識 )
  1. ホーム
  2. MySQL
  3. テーブルの作成・変更に必要な基礎知識

テーブルの作成・変更に必要な基礎知識

MySQL

テーブルの作成・変更に必要な基礎知識について解説します。「int」「text」「datetime」などのデータ型や、「NOT NULL制約」「INDEX」などのオプションについて取り上げます。

目次

CREATE TABLE
( テーブル作成 )

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` ) 
);

テーブルが存在しないときだけ作成としたい場合、以下のようにします。

CREATE TABLE IF NOT EXISTS DB名.テーブル名 ();

ALTER TABLE
( テーブル構造の変更 )

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

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

データ型

使用頻度の高いデータ型

使用頻度の高いデータ型について取り上げます。

データ型範囲備考
booleantinyint(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年問題に注意。
geometry5.7からInnoDBでもキーをはれる。SPATIAL KEY `latlng` (`latlng`)

Geometry型で緯度・経度を管理
json5.7から利用可能。JSON型の使い方(検索, 抽出, 挿入, 更新, 置換)

桁数指定

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> SHOW COLUMNS FROM test;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| float    | float         | NO   |     | NULL    |       |
| float2   | float(5,2)    | NO   |     | NULL    |       |
| double   | double        | NO   |     | NULL    |       |
| double2  | double(5,2)   | NO   |     | NULL    |       |
| decimal  | decimal(10,0) | NO   |     | NULL    |       |
| decimal2 | decimal(5,2)  | NO   |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

データを挿入します。

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

結果は以下の通りです。

mysql> SELECT * FROM test;
+-------+--------+--------+---------+---------+----------+
| float | float2 | double | double2 | decimal | decimal2 |
+-------+--------+--------+---------+---------+----------+
|   0.1 |   0.10 |    0.1 |    0.10 |       0 |     0.10 |
+-------+--------+--------+---------+---------+----------+
1 row in set (0.00 sec)

100000000倍してみます。

mysql> SELECT `float` * 100000000,                                                                                                                                            ->        `float2` * 100000000,
    ->        `double` * 100000000, 
    ->        `double2` * 100000000, 
    ->        `decimal` * 100000000, 
    ->        `decimal2` * 100000000 
    -> FROM `test`;
+---------------------+----------------------+----------------------+-----------------------+-----------------------+------------------------+
| `float` * 100000000 | `float2` * 100000000 | `double` * 100000000 | `double2` * 100000000 | `decimal` * 100000000 | `decimal2` * 100000000 |
+---------------------+----------------------+----------------------+-----------------------+-----------------------+------------------------+
|  10000000.149011612 |          10000000.15 |             10000000 |           10000000.00 |                     0 |            10000000.00 |
+---------------------+----------------------+----------------------+-----------------------+-----------------------+------------------------+
1 row in set (0.00 sec)

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 に設定できる値は以下の通りです。

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

「参照先が更新されたら同時更新」「参照先が削除されたらNULL設定」としたい場合、以下のようにします。

 FOREIGN KEY (`顧客コード`) REFERENCES 顧客(`顧客コード`)
  ON UPDATE CASCADE
  ON DELETE SET NULL
MySQL
よかったらシェアしてね！

関連記事

目次