SQLとは~入門編~データ型・トランザクションを解説

SQLとは~入門編~データ型・トランザクションを解説

更新日
2023/02/14
カテゴリ
SQL
プロセス
実践
ホーム

初心者向け学習方法一覧はこちら

はじめに

この記事では、SQLを扱う上で重要な概念であるデータ型、トランザクションについて解説します。

データ型

MySQLにおけるデータ型について解説します。ここではすべてを網羅するのではなく、よく使うものについて記載しています。

すべてのデータ型について確認したい場合は公式ドキュメントを参照してください。

https://dev.mysql.com/doc/refman/8.0/ja/data-types.html

データ型とは

テーブルを作成する際には、そのテーブルの列のデータ型を指定する必要があります。

データ型とは、テーブルに格納するデータの取り扱いに関する形式のことであり、データの性質や範囲などを定義したものです。

数値データ型

数値データ型は数値を格納するためのデータ型です。

取りうる値が整数であるか浮動小数点数であるか、また取りうる値の範囲がどれくらいになるのかに応じて適切なものを選択します。

整数型（真数値）

整数型は扱える数の範囲が異なる次の5つの種類が用意されています。

型
説明
TINYINT
範囲：0〜255（符号付きの場合は-128〜127）
SMALLINT
範囲：0〜65535（符号付きの場合は-32768〜32767）
MEDIUMINT
範囲：0〜16777215（符号付きの場合は-8388608〜8388607）
INT（INTEGER）
範囲：0〜4294967295（符号付きの場合は-2147483648〜2147483647）
BIGINT
範囲：0〜18446744073709551615（符号付きの場合は-9223372036854775808〜9223372036854775807）

固定小数点型（真数値）

固定小数点型は金銭データを扱う場合など、正確な精度を保持することが重要な場合に使用されます。

MySQLにおいてNUMERICはDECIMALとして実装されるので、DECIMALに関する次の注意事項がNUMERICにも同様に適用されます。

DECIMALのカラム宣言では、精度とスケールを指定できます（通常は指定します）。

salary DECIMAL(5,2)

この例では、5が精度で、2がスケールです。 精度は、その値に格納された有効な桁数を表し、スケールは小数点以下に格納できる桁数を表しています。

上記の例では、-999.99〜999.99の値が格納できます。

型
説明
DECIMAL
整数部の最大値は65、デフォルト値は10。
NUMERIC
DECIMALと同じ。

浮動小数点型（概数値）

浮動小数点型はある一定の精度以上は近似値となるデータ型です。厳密な精度が必要ではない場合に利用します。

型
説明
FLOAT
単精度浮動小数点数で小数第7位まで正確。それ以下は誤差が発生。
DOUBLE
倍精度浮動小数点数で小数第14位まで正確。それ以下は誤差が発生。

日時データ型

日時データ型は日付や時刻を格納するためのデータ型です。

取り扱いたい値が日付なのか、時刻を含むのかなどに応じて使い分けます。

日付型

日付型は時刻を含むかどうか、タイムゾーンを含むかどうかで3種類あります。

型
説明
DATE
日付部分を含むが時間部分は含まない値。範囲：1000-01-01〜9999-12-31
DATETIME
日付と時間の両方の部分を含む値。範囲：1000-01-01 00:00:00〜9999-12-31 23:59:59
TIMESTAMP
日付と時間の両方の部分を含む値。タイムゾーン情報を含む。範囲：1970-01-01 00:00:01 UTC〜2038-01-19 03:14:07 UTC

TIME型

TIME型は時間を表すデータ型です。

型
説明
TIME
範囲：-838:59:59〜838:59:59

YEAR型

YEAR型は4桁の形式の年を表すデータ型です。

MySQLのバージョン8.0以前では2桁の形式もサポートされていましたが、MySQLバージョン8.0以降ではサポートされなくなっています。

型
説明
YEAR
範囲：1901〜2155および0000

文字列データ型

文字列データ型は、文字列を扱うためのデータ型です。

CHAR型とVARCHAR型には、格納する最大文字数を表す長さが宣言されています。たとえば、CHAR(30)には最大30文字を格納できます。

CHARカラムの長さは、テーブルを作成したときに宣言した長さに修正されます。CHAR値は格納されると、指定された長さになるように右側がスペースで埋められます。

一方のVARCHARカラム内の値は可変長の文字列です。VARCHAR値は格納されるときに右側がスペースで埋められることはありません。

型
説明
CHAR
範囲：0〜255
VARCHAR
範囲：0〜6553

JSON データ型

JSON型は、RFC 7159で定義されたJSON（JavaScript Object Notation）ドキュメントをサポートするデータ型です。

一般に、プログラミングをする際にはAPI（Application Programming Interface）を利用して外部のシステム・ウェブサイト等からデータを取得することがよくあります。

そのようなAPIはJSON型でデータを返却するものが多く、取得した値をそのままMySQLへ格納できます。

取得した値はVARCHAR型などの文字列データ型のカラムに格納してもよいのですが、JSON型の場合は以下のようなメリットがあります。

  • JSONカラムに格納されているJSONドキュメントの自動検証。無効なドキュメントではエラーが発生。
  • 最適化された記憶域形式。JSONカラムに格納されたJSONドキュメントは、ドキュメント要素へのクイック読取りアクセスを許可する内部形式に変換される。
  • JSON_MERGE_PATCH関数やJSON_SET関数などのJSON型を取り扱う関数を用いた、柔軟なデータ操作が可能。

データ型デフォルト値について

データ型指定には、明示的または暗黙的なデフォルト値を指定できます。

例えば以下のようなDDLでテーブルを作成した場合、col1はデフォルトで値が設定されませんが、col2には「初期値」という文字列が設定されます。

CREATE TABLE DEFAULT_VALUE_SAMPLE (
  col1 VARCHAR(10),
  col2 VARCHAR(10) DEFAULT '初期値'
);

デフォルト値を使うと、例えばデータの挿入時間を自動で設定ができます。

mysql> -- insert_timestampにデフォルト値としてnow()関数（現在時刻を表示）を設定
mysql> CREATE TABLE DEFAULT_VALUE_SAMPLE (
    ->   id INTEGER,
    ->   data VARCHAR(10),
    ->   insert_timestamp TIMESTAMP DEFAULT (now())
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> -- テーブル作成直後は空
mysql> SELECT * FROM DEFAULT_VALUE_SAMPLE;
Empty set (0.00 sec)

mysql> -- id, dataカラムにだけデータを設定
mysql> INSERT INTO DEFAULT_VALUE_SAMPLE (id, data) VALUES (1, "data value");
Query OK, 1 row affected (0.01 sec)

mysql> -- insert_timestampカラムにはデータを投入した時刻が入る
mysql> SELECT * FROM DEFAULT_VALUE_SAMPLE;
+------+------------+---------------------+
| id   | data       | insert_timestamp    |
+------+------------+---------------------+
|    1 | data value | 2022-08-27 12:27:47 |
+------+------------+---------------------+
1 row in set (0.00 sec)

MySQL以外のRDBMSとの違い

MySQLとそれ以外のRDBMSではデータ型の取り扱いが異なる場合があります。

特徴的なものだとBOOLEAN型があります。BOOLEAN型は一般にTRUE/FALSEを表現するデータ型です。

MySQLにはBOOLEAN型は存在せず、代わりにTYNYINT型で表現されます。

このように扱うDBMSごとに使い分けがある場合があるので、MySQL以外のRDBMSを使う際には注意が必要です。

その他、MySQL以外のDBMSのデータ型との違いについては公式ドキュメントにまとめられていますので参考にしてください。

https://dev.mysql.com/doc/refman/8.0/ja/other-vendor-data-types.html

トランザクション

データベースにおいて重要な概念であるトランザクションについて解説します。

トランザクションとは

トランザクションとは一連の処理をひとまとめにしたものです。

トランザクションを利用することで、例えば一連の処理が途中で失敗してしまった場合に、途中まで成功していたものも含めてすべてもとに戻す、というようなことが可能になります。

MySQLにおけるトランザクションの操作方法

トランザクションを操作するためには以下の3つのステートメントを利用します。

  • BEGIN: トランザクションの開始
  • COMMIT: トランザクション内のデータ操作の確定
  • ROLLBACK: トランザクション内のデータ操作を取り消し

以下にトランザクション操作の例を示しています。

テーブルを作成し、データを挿入してCOMMITした場合とROLLBACKした場合の両方を試してみます。

mysql> -- 事前準備
mysql> CREATE TABLE transaction_test (
    ->   col VARCHAR(10)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> -- トランザクションの開始１
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> -- データを挿入
mysql> INSERT INTO transaction_test (col) VALUES ('データ挿入成功');
Query OK, 1 row affected (0.04 sec)

mysql>
mysql> -- トランザクションの確定
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> -- データ確認（データが挿入されている）
mysql> SELECT * FROM transaction_test;
+-----------------------+
| col                   |
+-----------------------+
| データ挿入成功        |
+-----------------------+
1 row in set (0.01 sec)

mysql>
mysql> -- トランザクションの開始２
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> -- データを挿入
mysql> INSERT INTO transaction_test (col) VALUES ('データ挿入失敗');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> -- トランザクションの取り消し
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> -- データ確認（データが挿入されていないはず）
mysql> SELECT * FROM transaction_test;
+-----------------------+
| col                   |
+-----------------------+
| データ挿入成功        |
+-----------------------+
1 row in set (0.00 sec)

上記のように、BEGINでトランザクションを開始し、COMMITまたはROLLBACKでそのトランザクション内の操作を確定または取り消しができます。

オートコミット設定

MySQLではデフォルトでオートコミット設定がONになっています。

そのため、BEGIN宣言なしでINSERT文などを実行した場合には自動でCOMMITが実行されていて、データの更新が確定しています。

オートコミット設定は以下のように確認できます。1の場合は設定が有効化されています。

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

また、設定を変更したい場合は以下のように実行します。

mysql> SET autocommit=0;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.01 sec)

まとめ

SQLを扱う上で重要な概念であるデータ型、トランザクションについて解説しました。

データベースにテーブルを作成する際には、適切なデータ型を選択することで効率的にデータを扱えたり、プログラミングがしやすくなるといったメリットがあります。 また、数値しか入らない想定だったところに文字列が入ってしまっていた、というような間違いも起こりにくくなります。

トランザクションを活用することで誤った操作をしてしまったり、中途半端なデータが挿入されてしまった、というような間違いを防ぐことができます。

いずれも快適なデータ操作・プログラミング開発を行う上で重要な概念ですので、しっかりと理解して使いこなしましょう。

プログラミングスクールの選び方

転職を検討中の方向け

フリーランス・副業で活躍したい方向け

教養・キャリアアップしたい方向け

給付金について詳しく知りたい方向け

学習方法一覧

学習方法一覧

‣
学習方法
‣
VS Code
‣
GitHub
‣
Docker
‣
Linux
‣
SQL
‣
HTML
‣
CSS
‣
Javascript
‣
オブジェクト指向
‣
Ruby
‣
Python

Copyright © 2022 Protogram