Michi's Tech Blog

一人前のWebエンジニアを目指して

SQL総復習⑦　MySQLのデータ型

DB/SQL

こんにちは！
スマレジ テックファームのMichiです！

今回は『SQL総復習⑦　MySQLのデータ型』編です。

文字列型

固定長文字列型（CHAR）

固定長文字列では、最初に格納する文字列の長さ（最大長）を決めます。最大長を超える長さの文字列は格納することができません。文字列の長さが最大長に満たない場合、文字数が最大長になるまで空白を半角スペースで埋めます。有効最大長は0～255です。

以下は、最大長5のCHAR型を定義する例です。

CREATE TABLE sample(column1 CHAR(5));

このcolumn1に対して、abcという文字列を格納するとします。abcは3文字でcolumn1の最大長5に満たないので、abc␣␣（abcの後ろに半角スペース2つ）という形で格納されます。ただし値を取得する場合は末尾にある空白は全て削除された上で取得されます。比較される時も同じです。

可変長文字列型（VARCHAR）

可変長文字列も、最初に格納する文字列の長さ（最大長）を決めるところは、固定長文字列と同じです。ただし、可変長文字列では、文字数が最大長に満たない場合でも、半角スペースで埋めるということはしません。有効最大長は0～255です。

以下は、最大長5のVARCHAR型を定義する例です。

CREATE TABLE sample(column1 VARCHAR(5));

このcolumn1に対して、abcという文字列を格納する場合は、半角スペースで補完されることなく、そのままabcが格納されます。

テキスト型（TEXT）

TEXT型はVARCHAR型と同じく、可変長の文字列を格納するための型ですが、VARCHARより多くの文字数を格納することができます。具体的には最大長が65,535文字（マルチバイト文字が含まれる場合は少なくなる）です。また、TEXT以外にもTINYTEXT、MEDIUMTEXT、LONGTEXTを選択することができ、それぞれ扱える最大長が異なります。

TEXT型では後ろに最大長を指定する必要はありません。

CREATE TABLE sample(column1 TEXT);

バイナリ型

BINARY型

BINARY型は固定長のバイナリデータを格納します。CAHR型と同じく、型の後ろに最大長を指定し、バイナリデータが最大長に満たない場合は、0で穴埋めします。

CREATE TABLE sample(column1 BINARY(5));

VARBINARY型

VARBINARY型は可変長のバイナリデータを格納します。VARCAHR型と同じく、型の後ろに最大長を指定し、バイナリデータが最大長に満たない場合でも、0で穴埋めはしません。

CREATE TABLE sample(column1 VARBINARY(5));

BLOB型

BLOB型はVARBINARY型と同じく、可変長のバイナリデータを格納するための型ですが、VARBINARYより多くのバイナリデータを格納することができます。例えば、画像、動画、音声ファイル、テキストデータなど、様々な種類のバイナリーデータを保存するのに使用されます。BLOB型は、最大で65,535バイトのデータを保存することができます。また、BLOB以外にもTINYBLOB、MEDIUMBLOB、LONGBLOBを選択することができ、それぞれ扱える最大長が異なります。

BLOB型では後ろに最大長を指定する必要はありません。

CREATE TABLE sample(column1 BLOB);

整数型（INT）

整数を扱える型です。扱える整数の範囲によって、MySQLでは5つの型が用意されています。

  • TINYINT ... 符号付きの範囲は -128～127。符号なしの範囲は0～255。
  • SMALLINT ... 符号付きの範囲は-32768～32767。符号なしの範囲は0～65535。
  • MEDIUMINT ... 符号付きの範囲は-8388608～8388607。符号なしの範囲は0～16777215。
  • INT ... 符号付きの範囲は-2147483648～2147483647。符号なしの範囲は0～4294967295。
  • BIGINT ... 符号付きの範囲は-9223372036854775808～9223372036854775807。符号なしの範囲は0～18446744073709551615。

下に行くほど、格納できる整数の桁数は大きくなりますが、DBの消費ストレージ量が多くなり、処理が遅くなります。

符号付き/なしとは、-（マイナス）を許容するかどうかという意味です。符号なしで定義したい場合は、型の後ろにUNSIGNEDを付けます。 例えば、以下の例では、column1は-128 ～127までの整数を許容しますが、column2は0～255までの整数を許容します。

CREATE TABLE sample(column1 TINYINT, column2 TINYINT UNSIGNED);

小数点型

小数点型には浮動小数点を扱うFLOAT、DOUBLEと固定小数点を扱うDECIMAL、NUMERICがあります。

浮動小数点型と固定小数点型の違い

両者の特徴をまとめました。細かい仕組みの違いを説明すると長くなるので、今回は型を使用するにあたって覚えておく必要のある特徴だけを記載しています。

浮動小数点型（FLOAT、DOUBLE）

浮動小数点型は、小数点以下の桁数が可変である数値型です。浮動小数点数は、非常に広い範囲の値を表現できる反面、精度が低いという特徴があります。

FLOAT、DOUBLE ともに次のように使用できます。

CREATE TABLE sample(column1 FLOAT, column2 DOUBLE);

FLOAT型はおよそ小数第 7 位まで正確で、DOUBLE型はおよそ小数第 15 位まで正確です。ただし、FLOATはDOUBLEに比べて処理速度が速いという特徴があります。

固定小数点型（DECIMAL、NUMERIC）

固定小数点型は、小数点以下の桁数が固定である数値型です。固定小数点型は、小数点以下の桁数が決まっているため精度が高く、誤差が生じることがないという利点があります。

DECIMAL、NUMERICともに次のように使用できます。

CREATE TABLE sample(column1 DECIMAL(5, 2), column2 NUMERIC(5, 2));

DECIMAL(5, 2)の第一引数は全体の桁数、第二引数は小数点以下の桁数を表します。この例であれば、全体は5桁、小数点以下は2桁の固定小数となるので、-999.99から999.99までの値を許容します。

NUMERICも使い方はまったく同じで、DECIMALのエイリアス（別名）と捉えて問題ありません。

浮動小数点型・固定小数点型ともに、整数型と同じく、型の後ろにUNSIGNEDを付与することで、マイナスの値を許容しなくなります。

日付・時刻型

日付型（DATE）

日付を扱う型です。基本となるフォーマットはYYYY-MM-DD形式です。

例）2023-04-30

時刻型（TIME）

時刻を扱う型です。基本となるフォーマットはHH:MM:SS形式です。

例）08:36:10

日付時刻型（DATETIME）

日付時刻を扱う型です。基本となるフォーマットはYYYY-MM-DD HH:MM:SS形式です。

例）2023-04-30 08:36:10

タイムスタンプ型（TIMESTAMP）

TIMESTAMP型は、DATETIME と同じく日付時刻を扱う型です。フォーマットもDATETIME型と同じですが、扱える値の範囲が少ないこと、そしてタイムゾーンの扱いが異なります。

まず、扱える値の範囲ですが、DATETIME型は西暦1000年1月1日から9999年12月31日までの範囲を持つのに対して、TIMESTAMP型は1970年1月1日から2038年1月19日までの範囲しか持てません。

また、タイムゾーンの扱いについては、DATETIME型はタイムゾーンの情報を持たないので、格納される値はタイムゾーンの影響を受けることなくそのまま保存されます。一方、TIMESTAMP型はタイムゾーンの情報を持つので、保存時にはタイムゾーンからUTC*1へ、読み出し時にはUTCからタイムゾーンへと変換されます。

それぞれの使い分けとしては、まずDATETIME型は、固定された日付と時間を表すために使用されます。例えば、誕生日や契約日など、変更される可能性の低いデータです。一方、TIMESTAMP型は、データが挿入された時点の日付と時間を記録し、データが更新されるたびに自動的に更新される場面に使用されます。例えば、最終ログイン日時やデータの変更日時など、頻繁に更新されるようなデータです。

JSON型

JSON形式のデータを扱います（MySQL 5.7以降でのみ利用可能）。
JSON型を使用するメリットは、

  • JSONデータを直接操作するためのSQL関数が提供されている
  • インデックスを使用してJSONデータを高速に検索できる
  • カラムに格納されたJSONデータをクエリでフィルタリングしたり、集約したりできる

ということが挙げられます。例えば、次のようなJSONデータが存在するとします。

{
  "name": "山田 太郎",
  "age": 30,
  "address": {
    "city": "Tokyo",
    "country": "Japan"
  },
  "phones": [
    {
      "type": "home",
      "number": "03-1234-5678"
    },
    {
      "type": "work",
      "number": "03-9876-5432"
    }
  ]
}

このJSONデータをJSON型のカラムに格納します。すると、以下のようなSQL文を使って、JSONデータをクエリで操作することができます。

-- JSONデータのフィールドにアクセス
SELECT json_extract(json_data, '$.name') AS name FROM table;

-- JSONデータのフィールドを更新
UPDATE table SET json_data = json_set(json_data, '$.age', 31) WHERE id = 1;

-- JSONデータの配列の要素を検索
SELECT * FROM table WHERE json_contains(json_data, '{"type": "home", "number": "03-1234-5678"}');

ちなみに、一番最初のJSONデータの読み取りは、TEXT型にJSONを挿入したときでも使用することができます。ただし、二、三番目のJSONの更新、及び検索は、JSON型で定義されたデータにしか適用できません。

まとめ

MySQLのデータ型は他にもまだありますが、今回は実務で使用されそうなものに絞って解説しました。

ここ数カ月、『SQL総復習』と題して記事を書いてきましたが、つい先日、業務でかなりややこしいSQLを書く機会がありました。普段ORマッパーに慣れまくっているせいでかなり苦戦しましたが、その経験とこのブログへのアウトプットで、SQLについてもかなりスキルアップできたと思います。

来月からはまた別のテーマで記事を書いていく予定です。ここまでお読みいただきありがとうございました。

*1:UTC(Universal Time, Coordinated) ... 協定世界時