【MySQL】テーブルの作成・削除、カラムの型・オプション

テーブルの作成・削除、カラムの型・オプション

テーブルの作成方法

CREATE TABLE

テーブルを作成する際は、カラムの型とオプションの指定ができます。
複数カラムを指定する場合は「,」で区切ります。


CREATE TABLE テーブル名 (
  カラム名1 型 オプション1 オプション2 オプション3,
  カラム名2 型 オプション1 オプション2
  カラム名3 型 オプション1
)

では、実際にユーザ情報を格納するテーブルを作成してみます。
データ型やオプションなどは表を用意しているのでそちらで詳細を確認してください。


CREATE TABLE users (
  user_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL COMMENT '名前',
  address VARCHAR(150) NOT NULL COMMENT '住所',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)

テーブルの削除方法

DROP TABLE

以下のSQLでテーブルを削除できます。


DROP TABLE テーブル名;

型の一覧

数値型指定時のオプション

「UNSIGNED」の指定がある場合は、負の値を許容しなくなります。
その代わり正の値の範囲が増えます。

「ZEROFILL」の指定がある場合は、指定の桁数まで0埋めを行います。
※MySQL 8.0.17以降　非推奨です。

数値型

型	範囲	範囲（UNSIGNED指定あり）
TINYINT	-128 ～ 127	0 ～ 255
SMALLINT	-32768 ～ 32767	0 ～ 65535
MEDIUMINT	-8388608 ～ 8388607	0 ～ 16777215
INTEGER	-2147483648 ～ 2147483647	-0 ～ 4294967295
BIGINT	-9223372036854775808 ～ 9223372036854775807	0〜 18446744073709551615

固定小数点型

型	範囲	範囲（UNSIGNED指定あり）
DECIMAL[M,D]	最大桁数は65	負の値を許可しません。正の値の範囲は変わりません。

ポイント

M（整数桁),D（少数桁)で整数部と小数部の桁数指定が可能です。

浮動小数点型

型	範囲	範囲（UNSIGNED指定あり）
FLOAT[M,D]	-3.402823466E+38 ～ -1.175494351E-38, 1.175494351E-38 ～ 3.402823466E+38	負の値を許可しません。正の値の範囲は変わりません。
DOUBLE[M,D]	-1.7976931348623157E+308 ～ -2.2250738585072014E-308, 2.2250738585072014E-308 ～ 1.7976931348623157E+308	負の値を許可しません。正の値の範囲は変わりません。

ポイント

M（整数桁),D（少数桁)で整数部と小数部の桁数指定が可能です。

日付と時刻

型	フォーマット	範囲
DATE	YYYY-MM-DD	1000-01-0 ～ 19999-12-31
DATETIME	YYYY-MM-DD hh:mm:ss	1000-01-01 00:00:00 ～ 9999-12-31 23:59:59
TIMESTAMP	YYYY-MM-DD hh:mm:ss	'1970-01-01 00:00:01' UTC ～ '2038-01-19 03:14:07' UTC
TIME	hh:mm:ss または hhh:mm:ss	-838:59:59.000000 ～ 838:59:59.000000 UTC
YEAR	YYYY	1901 ～ 2155

文字列

型	説明	範囲
CHAR[M]	指定した文字数(M)でデータが登録されます。指定した文字数よりデータが短い場合は指定した文字数になるまでスペースで右側を埋めます。	0 ～ 255
VARCHAR[M]	指定した文字数(M)までデータが登録できます。	0 ～ 65,535

ポイント

「CHAR」は指定した桁数に達しないデータはスペースで右埋めしていきます。
例えば「CHAR(5)」のカラムに「abc」というデータを入れた場合「abc　　」といった形になります。
「VARCHAR」は指定した桁数までデータを格納できます。
桁数に達しないデータでも、スペースで埋めることはしません。

型	説明	範囲
BINARY[M]	指定したバイト数(M)でデータが登録されます。指定した長さよりデータが短い場合は指定したバイト数になるまで「0x00」で右側を埋めます。	0 ～ 255
VARBINARY[M]	指定したバイト数(M)までデータが登録できます。	0 ～ 65,535

ポイント

「CHAR」と「VARCHAR」の関係と似ています。
「BINARY」は指定したバイト数に達しないデータは「0x00」で右埋めしていきます。
「VARBINARY」は指定したバイト数までデータを格納できます。
バイト数に達しないデータでも、スペースで埋めることしません。

型	範囲（最大）
TINYBLOB	255
BLOB[M]	65,535
MEDIUMBLOB	16,777,215
LONGBLOB	4,294,967,295
LONGTEXT	4,294,967,295

型	説明	範囲
ENUM	指定したリストの中から値を一つ格納することができます。内部的には整数でデータが保存されます。	65,535

ポイント

「column ENUM('apple','orange','banana')」という形でリストを指定します。
リストとして指定した値を一つのみ格納できます。
内部的には整数で1から順で振った値で保存されます。
データを取得する場合「where colum1 = 2」というような形で取得できます。
その場合、「orange」のデータを取得することになります。

型	説明	範囲
SET	指定したリストの中から値を一つ以上格納することが出来ます。内部的には二進数でデータが保存されます。	65,535

ポイント

「column SET('apple','orange','banana','strawberry')」という形でリストを指定します。
リストとして指定した値をカンマ区切りで複数の値を格納できます。
内部的には1,2,4,8と「2のべき乗」で順に振った値で保存されます。
データを取得する場合「where colum1 = 9」というような形で取得できます。
その場合、「1 + 8」の組み合わせのデータを取得するので、「apple,strawberry」と格納されたデータを取得することになります。

カラムのオプション一覧

オプション	説明
NOT NULL	NULLを許容しません。指定がない場合はNULLを許容します。
DEFAULT	対象のカラムの初期値を設定します。
AUTO_INCREMENT	1から順番に自動で連番を振ってくれます。
COMMENT	カラムにコメントが追加できます。説明用に任意の文字列を指定できます。
PRIMARY KEY	一意のデータとして認識するためのカラムを指定します。複数のカラムを複合して設定する事も出来ます。またNULLを許容しません。
UNIQUE	重複したデータが登録できないようにします。複数のカラムに指定することが出来ます。NULLを許容します。
INDEX	検索の為の最適化を行います。検索用のデータを別途作成しておき、検索の高速化を図ります。カラム数が少ない場合は効果の体感は難しいかもしれません。また、データを追加する際は元のテーブルとINDEX用のデータの両方に追加されるので通常より処理が重くなることに注意してください。
FOREIGN KEY	外部キー制約と呼ばれるものです。親テーブルと子テーブルのデータ整合性を確保する為のものです。親データのIDを持つ子テーブルがあった際に、その親データが削除されることを防ぎます。

みるはぴ

データ型とカラムのオプション沢山ありましたね
今回紹介したデータ型にも色々なものがありましたが、
普段一部のものしか使っていないんだなと実感しました。
※すべてのデータ型やオプションを紹介したわけではないので、
興味がある方は公式マニュアルをご覧ください。