(MariaDB) MySQL のデータ型とストレージメカニズムの包括的な説明

1.1 データ型の概要

データ型は、各フィールドに保存できるデータの種類、保存できるデータの量、保存できる形式を制限するフィールド制約です。 MySQL/MariaDB には通常、整数、浮動小数点、文字列、日付と時刻、特殊な ENUM 型と SET 型の 5 種類のデータ型があります。

これら 5 つのデータ タイプの意味、制限、および関連する説明を次の図に示します。

各データ型が占有するバイト数については、MariaDB の公式マニュアルを参照してください。

1.2 保管機構と動作モード

データ型によってフィールドのデータ格納長が制限される理由は、テーブルの作成時にメモリ内のアドレス空間が厳密に区切られるためです。アドレス空間の長さによって、必要なバイト数のデータを格納できます。もちろん、これは非常に大まかな概念です。より具体的な保存方法については、以下の説明を参照してください。

データ型の範囲を制限する方法は 2 つあります。1 つはスペースを厳密に制限し、割り当てられたスペースと同じ量のデータのみを格納し、制限を超えたデータは切り捨てる方法です。もう 1 つは、追加のバイト ビットを使用して、特定のアドレス空間内のバイトにデータが格納されているかどうかをマークする方法です。データが格納されている場合はマークされ、格納されていない場合はマークされません。

1.2.1 整数の保存

ここでは主に整数の格納方法について解説しますが、浮動小数点データ型の格納方法については考慮すべき点が多すぎます。

整数データ型の場合、厳密に空間が制限されますが、文字とは異なり、分割された各バイトのビット位置の 0 と 1 で値を直接計算できるため、その範囲はビット値の数に応じて計算されます。 1 バイトには 8 ビットがあり、この 8 ビットで 2^8=256 個の値を形成できます。同様に、2 バイトには合計 2^16=65536 個の値があります。4 バイトの int は 32 ビットを占め、0 ～ 2^32 の範囲を表すことができます。つまり、0 から 255 までの数字は 1 バイトのみを占有し、256 から 65535 までの数字は 2 バイトを占有します。

MySQL/mariadb の整数データ型ではパラメータ M を使用できることに注意してください。ここで、M は INT(M)、tinyint(M) などの正の整数です。 M は表示長を表します。例えば、int(4) は出力時に 4 桁の整数が表示されることを意味します。実際の値の桁数が表示値の幅より少ない場合、デフォルトでは左側にスペースが埋め込まれます。桁数が制限を超えても、表示される結果には影響しません。通常、この関数は、スペースの代わりに 0 を埋めるために zerofill 属性とともに使用されますが、zerofill を使用した後、列は自動的に符号なしフィールドになります。例えば：

テーブルtest3を作成します(id INT(2) ZEROFILL NOT NULL);
test3にVALUES(1),(2),(11),(111)を挿入します。
test3からidを選択します。
+-----+
|id|
+-----+
| 01 |
| 02 |
| 11 |
| 111 |
+-----+
セット内の 4 行 (0.00 秒)

注意する必要があるのは、表示幅は表示効果にのみ影響し、保存、比較、長さの計算などの操作には影響しないということです。

1.2.2 文字タイプの保存

ここでは主に、保存方法と char と varchar の違いについて説明します。

char 型は「固定長文字列型」と呼ばれることがよくあります。スペースの長さを厳密に制限しますが、バイト数ではなく文字数を制限します。ただし、以前のバージョンではバイト数が制限されていました。したがって、char(M)は厳密にM文字を格納し、不足する部分はスペースで埋められ、M文字を超える部分はそのまま切り捨てられます。

char 型には「短すぎる場合はスペースで埋める」機能があるため、データの信頼性を反映するために、アドレス空間からデータを取得するときに末尾のスペース部分が自動的に削除されます。これは char の特別な機能です。手動で保存した末尾のスペースも自動的に埋められたものとみなされ、取得時に削除されます。つまり、where ステートメントでは、name='gaoxiaofang ' と name='gaoxiaofang' は同じ結果になります。

例えば：

テーブルtest2(a char(4) charset utf8mb4)を作成します。
test2 に値を挿入します('おめでとうございます'),('昇進おめでとうございます'),('こんにちは'),('彼 ');
test2からconcat(a,'x')を選択します。
+---------------+
| 連結(a,'x') |
+---------------+
| おめでとうございます x |
| x になったおめでとうございます |
| ヘルクス |
| 16 進数 |
+---------------+
4行セット

上記の結果から、char(4) は 4 文字のみを格納でき、末尾のスペースを削除できることがわかります。

VARCHAR は「可変長文字列型」と呼ばれることがよくあります。データを格納するときに、バイトにデータが格納されているかどうかをマークするために追加のバイト ビットを使用します。保存される各バイト (文字ではない) は記録に 1 ビットを占有するため、1 バイト追加すると合計 256 バイトになり、2 バイト追加すると合計 65536 バイトになります。しかし、MySQL/mariadb では最大ストレージ容量が 65536 バイトに制限されています。つまり、シングルバイト文字の場合は、最大 65536 文字を格納できます。UTF8 などのマルチバイト文字の場合は、各文字が 3 バイトを占めるため、最大 65536/3 = 21845 utf8 文字を格納できます。

したがって、varchar(M)を格納する場合、実際のデータ空間の長さに加えて、1バイトまたは2バイトのビット長を計算する必要があります。つまり、シングルバイト文字の場合、実際に占有される空間はM + 1バイトまたはM + 2バイトであり、マルチバイト文字(3バイトなど)の場合、実際に占有される空間はM * 3 + 1バイトまたはM * 3 + 2バイトです。

varchar ストレージでは各バイトを記録するために追加のビットが必要なので、短いデータは自動的にパディングされず、明示的に保存された末尾のスペースもビットに保存されてマークされるため、末尾のスペースは削除されません。

varchar(2)を指定した場合、char(M)と同様に2バイト文字しか格納できません。制限を超えると文字が切り捨てられます。

char、varchar、およびテキスト文字列型に関しては、比較時に末尾のスペースは考慮されませんが、一致時に文字が正確であるため、類似一致または通常の一致を行うときはスペースが考慮されます。例えば：

テーブルtest4(a char(4),b varchar(5))を作成します。
test4 に値 ('ab ','ab ') を挿入します。
test4 から a='ab '、b='ab '、a=b を選択します。
+-----------+--------------+-----+
| a='ab ' | b='ab ' | a=b |
+-----------+--------------+-----+
| 1 | 1 | 1 |
+-----------+--------------+-----+
セット内の1行
test4 から 'ab' のようなものを選択します。
+-------------------+
| a は 'ab' に似ています |
+-------------------+
| 0 |
+-------------------+
セット内の1行

最後に、値を保存（またはメモリにロード）するときに、数値型として保存すると、文字型や日付と時刻型として保存するよりも多くのスペースを節約できることに注意してください。整数値はビット計算を通じて直接保存されるため、0～255 の整数は 1 バイトのみを占有し、256～65535 の整数は 2 バイトを占有します。4 バイトを占有する場合、数十億の整数のいずれかを表すことができます。これにより、各文字が 1 バイトを占有する文字ストレージよりもはるかに多くのスペースが節約されます。たとえば、値「100」は、文字型として保存される場合は 3 バイトを占めますが、数値型として保存される場合は 1 バイトしか占めません。そのため、データベースは引用符で囲まれていない値をデフォルトで数値として扱います。明示的に文字型または日付と時刻型として保存する場合は、曖昧さを避けるために引用符を使用する必要があります。

1.2.3 日付と時刻型の保存

日付と時刻のデータは、数値データとの曖昧さを避けるために引用符で囲んで保存する必要があります。日付と時刻の入力方法は非常に緩やかです。許可されている区切り文字であればどれでも使用できますが、4 桁の年を使用することをお勧めします。

20110101
2011-01-01 18:40:20
2011/01/01 18-40-20
20110101184020

1.2.4 ENUMデータ型

ENUM データ型は列挙型です。定義方法はENUM('value1', 'value2', 'value3', ...)です。このタイプのフィールドにデータを挿入する場合、値またはNULLのいずれか1つだけを挿入できます。他の値またはNULL(つまり、'')を挿入すると、空のデータに切り捨てられます。保存時に大文字と小文字は無視され（ENUM の文字に変換されます）、末尾のスペースは切り捨てられます。

mysql> テーブル test6(id int auto_increment primary key、name char(20)、gender enum('Mail'、'f')) を作成します。
mysql> test6(name,gender) に値を挿入します('malongshuai','Mail'),('gaoxiaofang','F'),('wugui','x'),('tuner',null),('woniu','');
クエリは正常です。5 行が影響を受けました
記録: 5 重複: 0 警告: 2
mysql> 警告を表示します。
+---------+------+----------------------------------------------------------+
| レベル | コード | メッセージ |
+---------+------+----------------------------------------------------------+
| 警告 | 1265 | 行 3 の列「性別」のデータが切り捨てられました |
| 警告 | 1265 | 行 5 の列「性別」のデータが切り捨てられました |
+---------+------+----------------------------------------------------------+
2行セット
mysql> test6 から * を選択します。
+----+-------------+---------+
| ID | 名前 | 性別 |
+----+-------------+---------+
| 1 | malongshuai | メール |
| 2 | ガオシャオファン | f |
| 3 | ウーギ | |
| 4 | チューナー | NULL |
| 5 | ウォニウ | |
+----+-------------+---------+
5行セット

ENUM 型のデータはインデックス値を通じて保存されます。文字列型と比較すると、保存に必要なバイト数は 1 バイトまたは 2 バイトだけです。理論上、値の数が 256 未満の場合は 1 バイトのみが必要で、数が 256 を超えて 65536 未満の場合は 2 バイトが保存に使用されます。 MySQL/MariaDB は、最大 65536 個の値を保存するように制限されています。もちろん、これは理論上の制限です。実際のストレージでは考慮すべき要素が多数あります。たとえば、NULL もビットを占有するため、実際のストレージでは 250 個の値に 2 バイトが必要になる場合があります。

ENUM の各値はインデックス番号で番号付けされます。フィールドを検索する場合でも操作する場合でも、インデックス値が操作に使用されます。 value1 のインデックス = 1、value2 のインデックス = 2、など。ただし、NULL 値の場合は index = NULL、空のデータの場合は index = 0 という 2 つの特別なインデックス値があることに注意してください。

たとえば、ENUM('a','b','c') の場合、フィールドに '''b','a','c',NULL,'xxx' を順番に挿入すると、最初と最後のものは空のデータに切り捨てられるため、それらのインデックスは 0 になり、挿入された NULL のインデックスは NULL になり、挿入された 'b','a','c' のインデックス値はそれぞれ 2,1,3 になります。したがって、インデックス番号と値の対応は次のようになります。

ENUM インデックスを使用してデータを取得します。

mysql> test6 から * を選択します。ここで、性別 = 2 です。
+----+-------------+---------+
| ID | 名前 | 性別 |
+----+-------------+---------+
| 2 | ガオシャオファン | f |
+----+-------------+---------+
セット内の1行

特に、ENUM を使用して数値を格納することは推奨されません。これは、ソート、検索、その他の操作のいずれの場合も、すべてインデックス値を条件として行われるため、誤解を招きやすいためです。たとえば、次の例では、ENUM を使用して 2 つの値を保存し、取得と並べ替えの操作を実行します。

mysql> テーブル test7(id enum('3','1','2')) を作成します。
mysql> test7 に値 ('1'),('2'),('3') を挿入します。
# 検索すると id=2 ですが、結果は 1 になります。これは、id=2 の 2 が enum のインデックス値であり、enum の index=2 の値が 1 であるためです。
mysql> test7 から * を選択します (id=2);
+----+
|id|
+----+
| 1 |
+----+
セット内の1行
# id でソートすると、インデックス サイズでもソートされます。mysql> select * from test7 order by id asc;
+----+
|id|
+----+
| 3 |
| 1 |
| 2 |
+----+
3行セット

したがって、浮動小数点値であってもあいまいになりやすいため、ENUM に値を格納しないことを強くお勧めします。

1.2.5 SET データ型

SET 型の場合、enum と同様に大文字と小文字は区別されず、保存時に末尾のスペースは削除され、null も有効な値となります。しかし、違いは、複数の指定された値を組み合わせることができることです。たとえば、set('a','b','c','d') は 'a, b'、'd, b' などを格納できます。複数のメンバーはカンマで区切られます。したがって、複数のメンバーを使用する場合、メンバー自体の値にカンマを含めることはできません。保存する値がセット リストにない場合は、null 値に切り捨てられます。

SET データ型が占めるスペースは、SET メンバーの数 M に関連し、(M+7)/8 を切り上げて計算されます。つまり、1 ～ 8 個のメンバーが 1 バイトを占有します。

メンバー 9 ～ 16 は 2 バイトを占有します。
メンバー 17 ～ 24 は 3 バイトを占有します。
メンバー 25 ～ 32 は 4 バイトを占有します。
メンバー 33 ～ 64 は 8 バイトを占有します。

MySQL/MariaDB は最大 64 メンバーに制限されます。

SET データ型のデータを保存する場合、重複するメンバーは無視され、列挙された順序で保存されます。たとえば、set('b','b','a') では、'a,b,a' と 'b,a,b' を格納した結果は 'b,a' になります。

mysql> テーブル test8(a set('d','b','a')) を作成します。
mysql> test8 に値を挿入します ('b,b,a'),('b,a,b'),('bab');
クエリは正常、3 行が影響を受けました
記録: 3 重複: 0 警告: 1
mysql> test8 から * を選択します。
+-----+
| ア |
+-----+
| バ、ア |
| バ、ア |
| |
+-----+
3行セット

指定されたセット値 set_value を含む行を取得するには、find_in_set(set_value,set_column_name) を使用します。たとえば、フィールド a にメンバー b が含まれる行を取得するには、次のようにします。

mysql> test8 から * を選択します。ここで find_in_set('b',a);
+-----+
| ア |
+-----+
| バ、ア |
| バ、ア |
+-----+
2行セット

1.3 データ型属性: 符号なし

unsigned 属性は数値データを符号なしにします。 unsigned 属性を使用すると、数値データ型の範囲が変わります。たとえば、signed tinyint 型の範囲は -128 ～ 127 ですが、unsigned を使用すると範囲は 0 ～ 255 になります。同時に、unsigned は列に負の値を挿入することも制限します。

テーブル t(a int unsigned,b int unsigned) を作成します。
tに挿入 1,2を選択;
tに挿入 -1,-2を選択;

上記のステートメントでは、負の数を挿入する 2 番目のステートメントを実行すると、範囲が範囲外であることを示すエラーが報告されます。

unsigned を使用すると、場合によってはメリットがあります。たとえば、一般的な ID 主キー列では負の数は許可されません。これは、チェック制約を実装するのと同じです。ただし、unsigned を使用すると予期しない問題が発生する場合があります。数値計算中に負の数値が得られた場合は、エラーが報告されます。たとえば、上記の表 t では、フィールド a と b は両方とも符号なしの列であり、a=1、b=2 の行があります。

mysql> t から * を選択します。
+---+---+
| ア | ロ |
+---+---+
| 1 | 2 |
+---+---+
セット内の1行

このとき、ab を計算するとエラーが発生します。それだけでなく、計算に含まれる符号なしの列はすべて負の数になります。

mysql> t から ab を選択します。
1690 - BIGINT UNSIGNED 値が '(`test`.`t`.`a` - `test`.`t`.`b`)' の範囲外です
mysql> t から a-2 を選択します。
1690 - BIGINT UNSIGNED 値が '(`test`.`t`.`a` - 2)' の範囲外です

計算結果が負でない場合は効果はありません。

mysql> t から 2-a,a*3 を選択します。
+-----+-----+
| 2-a | a*3 |
+-----+-----+
| 1 | 3 |
+-----+-----+
セット内の1行

これは MySQL/MariaDB のバグではありません。C 言語の unsigned にも同様の問題があります。この問題は、MySQL/MariaDB で set sql_mode='no_unsigned_subtraction' を設定することで解決できます。

したがって、個人的には、フィールドを変更するために unsigned 属性を使用しないことをお勧めします。

1.4 データ型属性:ゼロフィル

zerofill がフィールドを変更した後、不足しているフィールドの表示部分はスペースではなく 0 で埋められます。zerofill が有効になっている場合は、自動的に unsigned に設定されます。 Zerofill は通常、列の表示幅を設定した後にのみ使用されます。列の表示幅については上記で紹介しました。

mysql> テーブル t1(id int(4) zerofill) を作成します。
mysql> t1 から * を選択します。
+-------+
|id|
+-------+
| 0001 |
| 0002 |
| 0011 |
| 83838 |
+-------+
セット内の 4 行 (0.00 秒)

Zerofill は表示結果のみを変更し、保存されたデータ値には影響しません。

上記の（MariaDB）MySQLデータ型とストレージメカニズムの包括的な説明は、編集者があなたと共有するすべての内容です。参考になれば幸いです。また、123WORDPRESS.COMをサポートしていただければ幸いです。