MySQLのデータ型

一、charとvarcharタイプ
charはvarcharタイプと類似しており、文字列を格納するために使用されますが、文字列を保存および取得する方法は異なります.charは固定長の文字タイプに属し、varcharは可変長の文字タイプに属する.たとえば、char(4)とvarchar(4)の2つのタイプ定義では、次のようになります.
(1)、''はchar(4)に4バイトの長さを占め、varchar(4)は1バイトの長さしか占めない.
(2)、'ab'はchar(4)に4バイトの長さを占め、varchar(4)には3バイトの長さしか占めていない.
(3)、'abcd'はchar(4)に4バイト、varchar(4)に5バイトの長さを占有する.
varcharタイプでバイト長が1バイト増えるのはなぜですか?これは、varcharタイプがvarcharタイプを保存するために、より多くのバイトを使用して実際にどれだけの長さを使用しているかのためです.char(4)とvarchar(4)の検索は常に同じではありません.たとえば、次のようにします.

mysql> create table char_and_varchar (v varchar(4),c char(4));
Query OK, 0 rows affected (0.20 sec)

mysql> insert into char_and_varchar values ('ab  ','ab  ');
Query OK, 1 row affected (0.33 sec)

mysql> select concat(v,'cd'),concat(c,'cd') from char_and_varchar;
+----------------+----------------+
| concat(v,'cd') | concat(c,'cd') |
+----------------+----------------+
| ab  cd         | abcd           |
+----------------+----------------+
1 row in set (0.35 sec)

charは固定長であるため、varcharよりも処理速度が速いが、記憶空間を浪費し、プログラムが末尾スペースを処理する必要があるなどの欠点があるため、長さの変化が大きくなく、クエリー速度に要求が高いデータはcharタイプを用いて記憶することが考えられる.MySQLバージョンのアップグレードに伴い、varcharデータ型のパフォーマンスも向上し、varcharタイプの応用範囲はさらに広がります.
MySQLでは、charとvarcharの使用原則が異なるストレージエンジンがあります.
(1)、MyISAMストレージエンジンでは、可変長のフィールドタイプの代わりに固定長のフィールドタイプを使用することを推奨する.(2)、Memoryストレージエンジンでは現在、固定長のデータ行で格納されているため、charタイプでもvarcharタイプでもcharタイプ処理に変換される.(3)、InnoDBストレージエンジンではvarcharタイプを推奨します.
二、TEXTとBLOB
少量の文字列を保存する場合はcharとvarcharデータ型を使用できます.大きなテキストを保存する場合、通常はtextまたはBLOBを使用することを選択します.両者の主な違いは、BLOBがバイナリデータ、例えば写真を保存するために使用でき、textは文字タイプデータを保存するためにのみ使用できます.textとBLOBには、text、mediumtext、longtext、blob、mediumblob、longblobの3つの異なるタイプが含まれています.これらの主な違いは、格納テキストの長さの違いと格納バイトの違いです.
BLOBとTEXTタイプを使用する際に注意すべき問題:
(1)、BLOB、TEXTでは、特に大量の削除操作が行われている場合、パフォーマンスの問題が発生します.削除操作はデータテーブルに大きな「空洞」を残し、後でこれらの「空洞」を埋め込んだ記録は挿入性能に影響します.性能を向上させるために、定期的にOPTIMIZERTABLE機能を使用してこれらのテーブルを断片化し、空洞による性能問題を回避しなければならない.
(2)、合成されたインデックスを使用して、テキストセグメントのクエリー性能を向上させる.合成インデックスとは、大きなテキストフィールドの内容に基づいてハッシュ値を作成し、この値を個別のデータ列に格納し、ハッシュ値によってデータ行を見つけることです.例:

mysql> create table t (id varchar(100),content blob,hash_value varchar(40));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values (1,repeat('beijing',2),md5(content)); 
Query OK, 1 row affected (0.33 sec)

mysql> insert into t values (2,repeat('beijing',2),md5(content)); 
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values (2,repeat('beijing 2008',2),md5(content));
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+--------------------------+----------------------------------+
| id   | content                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 1    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
3 rows in set (0.00 sec)

mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));
+------+--------------------------+----------------------------------+
| id   | content                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 2    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
1 row in set (0.00 sec)

コンポジットインデックスは、正確に一致するシーンにのみ使用でき、ディスクI/Oをある程度削減し、クエリー効率を向上させます.BLOB、CLOBフィールドをあいまいに検索する必要がある場合は、MySQLのプレフィックスインデックス、すなわちフィールドの前のn列にインデックスを作成します.例:

mysql> create index idx_blob on t (content(100));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t \G
*************************** 1. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_blob
 Seq_in_index: 1
  Column_name: content
    Collation: A
  Cardinality: 3
     Sub_part: 100
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

mysql> desc select * from t where content like 'beijing%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: idx_blob
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

(3)、大規模なBLOBやTEXTフィールドを検索する必要はありません.
(4)、BLOBまたはTEXTフィールドを別々のテーブルに分離する.
三、浮動小数点数と定点数
浮動小数点数は一般的に小数部を含む数値を表すのに用いられる.1つのフィールドが浮動小数点タイプとして定義された後、挿入データの精度がカラム定義の実際の精度を超えた場合、挿入値は実際に定義された精度値に四捨五入され、挿入され、四捨五入のプロセスはエラーを報告しません.MySQLのfloat、double(real)は浮動小数点数を表すために使用されます.
定点数は浮動小数点数とは異なり、定点数は実際には文字列形式で格納されるため、定点数はデータをより正確に格納することができる.挿入データの精度が実際に定義された精度よりも大きい場合、MySQLはアラートを発行しますが、データは実際の精度で四捨五入して挿入されます(従来のモードで挿入されている場合はエラーが発生します).MySQLでは、定数をdecimal(またはnumberic)で表します.
浮動小数点数でデータを格納すると誤差があり、精度が要求されるシーン(通貨など)では、定点数を使用してデータを格納する必要があります.例:

mysql> create table b (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.37 sec)

mysql> insert into b values (131072.32,131072.32);
Query OK, 1 row affected (0.00 sec)

mysql> select * from b;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)

四、日付タイプ
MySQLが提供する一般的な日付タイプは、date、time、datetime、timestamp、日付タイプの選択原則です.
(1)、実際のニーズに応じてアプリケーションの最小記憶を満たすことができる日付タイプを選択しなければならない.
(2)、年月日分秒を記録し、年代が古い場合はdatetimeタイプを使用することが望ましい.
(3)、記録された日付がマルチタイムゾーンのユーザによって使用される場合、timestampタイプを使用することが望ましい.