MySQL数字タイプ自己増加ピット

8525 ワード
MySQL 数の種類 自己増加する
表構造の設計をする時、数字のタイプは最もよくあるタイプの一つですが、数字のタイプを上手に使うのは想像ほど簡単ではありません。
  • はどのようにインターネットの大容量の同時業務の自己増加キーを設計しますか？INTで十分ですか？
  • はどうやって口座の残高を設計しますか？DECIMALタイプで大丈夫ですか？
    • 以上は全部間違っています
    デジタルタイプは簡単に見えるが、構造構造構造設計においては、上記の「設計上の思考が全面的ではない」問題が現れやすい（特に大量同時進行のインターネットシーンで）
    数の種類
    整数の種類
    MySQLデータベースはSQL標準サポートの整体タイプをサポートしています。INT、SMALLINE。また、MySQLデータベースも、例えば、TINYINT、MEDIUMINT、BIGINTの整数タイプ（表1は、各種の整数型が占有する記憶空間および取得範囲を示している）をサポートしている。
    MySQLデータタイプ
    意味(符号付き)
    tinyint(m)
    1バイト範囲（-128~127）
    smaallint(m)
    2バイト範囲(-32768~32767)
    mediumint(m)
    3バイト範囲（-8388608~8388607）
    int(m)
    4バイト範囲（-2147483648~2147483647）
    ビギナート(m)
    8バイト範囲(+-9.22*10の18乗)
    全体のタイプには、signedとunsigned属性があり、それは整数型の取得範囲を表し、デフォルトはsignedとしている。設計する時、意図的にunsigned属性を使うことを勧めません。データ分析をする時、SQLが戻ってくる可能性がある結果はほしい結果ではありません。
    「販売表sale」の例を見ると、その表の構造とデータは以下の通りです。ここは特に注意して、列sale_countはunsigned属性を使用しています。つまり、設計時に希望列が格納されている値は0以上です。
    
mysql> SHOW CREATE TABLE sale\G

*************************** 1. row ***************************

       Table: sale

Create Table: CREATE TABLE `sale` (

  `sale_date` date NOT NULL,

  `sale_count` int unsigned DEFAULT NULL,

  PRIMARY KEY (`sale_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1 row in set (0.00 sec)


mysql> SELECT * FROM sale;

+------------+------------+

| sale_date  | sale_count |

+------------+------------+

| 2020-01-01 |      10000 |

| 2020-02-01 |       8000 |

| 2020-03-01 |      12000 |

| 2020-04-01 |       9000 |

| 2020-05-01 |      10000 |

| 2020-06-01 |      18000 |

+------------+------------+

6 rows in set (0.00 sec)
    その中で、sale_dateは販売の日付を表し、sale_countは毎月の販売数量を表しています。今は需要があります。毎月の販売数量の変化を統計して、ビジネスの方策を決めたいです。このSQL文は非等値接続に適用する必要がありますが、書くのは難しくないです。
    
SELECT 

    s1.sale_date, s2.sale_count - s1.sale_count AS diff

FROM

    sale s1

        LEFT JOIN

    sale s2 ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date

ORDER BY sale_date;
    しかしながら、実行中は、列sale_によりcountはunsigned属性を使って、このような結果を投げます。
    ERROR 1690(22003)：BIGINT UNSIGN ED value is out of range in'(`test`.s 2`.sale_count`-test`.s 1`.sale_count`
    MySQLは、ユーザに対して計算の結果が範囲外であることを示唆していることがわかる。実は、ここのMySQLはunsignedの数値を相殺してもunsignedです。そうしないとエラーが発生します。
    このエラーを避けるためには、データベースパラメータsql_mode設定はNO_UNSIGNED_SUBTRACTION、減算を許可した結果、signedとなり、最終的に欲しい結果が得られます。
    
mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';

Query OK, 0 rows affected (0.00 sec)

SELECT


    s1.sale_date,

    IFNULL(s2.sale_count - s1.sale_count,'') AS diff

FROM

    sale s1

    LEFT JOIN sale s2

    ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date

ORDER BY sale_date;


+------------+-------+

| sale_date  | diff  |

+------------+-------+

| 2020-01-01 |       |

| 2020-02-01 | 2000  |

| 2020-03-01 | -4000 |

| 2020-04-01 | 3000  |

| 2020-05-01 | -1000 |

| 2020-06-01 | -8000 |

+------------+-------+

6 rows in set (0.00 sec)
    浮動小数点タイプと高精度タイプ
    整型以外にも、数字の種類には浮動小数点と高精度タイプがあります。
    MySQLの前のバージョンには浮動小数点タイプFloatとDoubleがありますが、これらのタイプは高精度でもSQL規格のタイプでもないので、実際の生産環境では使用を推奨しません。
    さらに重要なのは、MySQL 8.0.17バージョンからスタートし、表を作成するときにFloatまたはDoubleを使用すると、次の警告を投げます。MySQLは上記浮動小数点タイプを使ってはいけないとユーザに警告しています。後のバージョンで浮動小数点タイプを破棄するように注意しています。
    Specifying number of digits for float point data types is deprecated and will be removed in a future release
    デジタルタイプの高精度DECIMALタイプは、このタイプの列を宣言すると、精度とスケールを指定することができ、例えば、
    
salary DECIMAL(8,2)
    ここで、8は精度（精度は保存値の主要桁数を表します）、2はスケール（小数点以下の桁数を示します）です。一般的にテーブル構造の設計では、タイプDECIMALはユーザーの給料、口座の残高などの小数点以下2桁までの正確な業務を表しています。
    しかし、海の量を併発したインターネット業務で使用する場合、金額フィールドの設計はDECIMALタイプを推奨しないが、INT型の整体タイプを使用することを推奨する（以下、原因を分析する）。
    業務表の構造設計は実戦である。
    整体タイプと自己増加設計
    実際のビジネスシーンでは、全体のタイプで最も一般的なのは、ビジネスで使用されるアイテムの数です。例えば上記の表の販売数量、または電気商における在庫数、購入回数などです。業務において、整体タイプのもう一つの一般的で重要な使い方は、表の主キーとして、つまり、行のデータを一意に識別するために用いられる。
    整体結合属性aut_incrementは、自己増加機能を実現することができますが、テーブル構造の設計においては自己増加でキーを作ります。特に以下の2点に注意してほしいです。注意しないと、業務に対して災難的な打撃を与える可能性があります。
  • はBIGINTをメインキーとし、INTではない。
  • 自己増値は持続的ではなく、トレース現象（MySQL 8.0バージョン前）があるかもしれません。
    • 表1から、INTの範囲は最大42億レベルであり、実際のインターネットビジネスシーンの応用において、最大値に達しやすいことが分かります。例えば、いくつかの流水表、ログ表、毎日1000 Wのデータ量、420日後、INTタイプの上限が達成できます。
    したがって、自己増加型でメインキーを作ります。すべてBIGINTを使います。INTではありません。INTを4バイト節約するために使用しないでください。上限に達したら、テーブル構造の変更を行います。大きな負担と苦痛です。
    ここで面白い問題があります。もしINTタイプの上限に達したら、データベースの表現はどうなりますか？もう1になりますか？以下のSQL文で検証できます。
    
mysql> CREATE TABLE t (

    ->     a INT AUTO_INCREMENT PRIMARY KEY

    -> );


mysql> INSERT INTO t VALUES (2147483647);

Query OK, 1 row affected (0.01 sec)


mysql> INSERT INTO t VALUES (NULL);

ERROR 1062 (23000): Duplicate entry '2147483647' for key 't.PRIMARY'
    INT上限に達したら、再度自己増加挿入を行うと、エラーが繰り返されます。MySQLデータベースは自動的に1にリセットされません。
    二つ目の特に注意したい問題は、MySQL 8.0バージョンの前に、増加から持続化しないと、付加価値から遡及問題があるかもしれません。
    
mysql> SELECT * FROM t;

+---+

| a |

+---+

| 1 |

| 2 |

| 3 |

+---+

3 rows in set (0.01 sec)


mysql> DELETE FROM t WHERE a = 3;

Query OK, 1 row affected (0.02 sec)


mysql> SHOW CREATE TABLE t\G

*************************** 1. row ***************************

       Table: t

Create Table: CREATE TABLE `t` (

  `a` int NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1 row in set (0.00 sec
    3に増加したこの記録を削除した後、次の自己増値は依然として4であることが分かります。INCREMENT=4）は、ここでは間違いなく増加しています。しかし、このときデータベースが再起動されたら、テーブルtの自己増加開始値は再び3になります。すなわち、付加価値から遡ります。具体的には以下の通りです。
    
mysql> SHOW CREATE TABLE t\G

*************************** 1. row ***************************

       Table: t

Create Table: CREATE TABLE `t` (

  `a` int NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1 row in set (0.00 s
    この問題を徹底的に解決するには、次の2つの方法があります。
  • アップグレードMySQLバージョンは8.0バージョンになり、各テーブルの自己増値は持続的になります。
  • データベースバージョンをアップグレードできないと、コアトラフィックテーブルに自己増加データタイプを使用してメインキーを作ることを強く推奨しない。
    • 実際には、大規模なインターネットアーキテクチャの設計の過程では、より良い分散アーキテクチャの拡張のために、全体のタイプを使用して、メインキーを行うことを推奨しないで、より推奨されている文字列のタイプです。
    資金フィールドの設計
    ユーザー残高、基金口座残高、デジタル財布、小銭などの業務設計において、フィールドはすべて資金フィールドであるため、通常プログラマはDECIMALタイプをフィールドの選択型として使用することに慣れています。
    
CREATE TABLE User (

  userId BIGINT AUTO_INCREMENT,

  money DECIMAL(8,2) NOT NULL,

  ......

)
    大容量のインターネットビジネスの設計基準では、DECIMALタイプを推奨するのではなく、DECIMALをフルタイプに変換することをオススメします。つまり、資金の種類は単位で貯蓄するのではなく、単位で保存するのがオススメです。1元のようにデータベースに100型の型で保存されます。
    金額フィールドの取得範囲がDECIMALで表されている場合、長さはどのように定義されますか？タイプDECIMALは長くなるフィールドですので、金額フィールドを定義するにはDECIMAL（8,2）と定義するのは十分ではありません。これは、記憶の最大値が99999999.99で、百万級の資金記憶のみを表します。
    ユーザーの金額は少なくとも100億のフィールドを保存しますが、統計局のGDP金額フィールドは数十兆のレベルに達するかもしれません。タイプDECIMALで定義すると統一しにくいです。
    さらに重要なことは、タイプDECIMALは、バイナリによって実現される符号化方式であり、計算効率は全体に比べてはるかに低い効率であることである。したがって、BIG INTを使用して、金額に関するフィールドを記憶することを推奨する。
    フィールド格納にはサブメモリを採用し、それでもBIG INTはギガ級の金額を記憶することができます。ここ、1兆＝1兆円です。
    このような利点は、すべての金額関連フィールドが固定長フィールドであり、8バイトを占有し、効率的に記憶されることである。もう一つの点は，直接型を整えて計算することによって，効率がより高い。
    注意してください。データベース設計において、固定長保存の性能がより良いので、非常に強調されています。
    データベースに記録されている保存方法を見てみます。
    更新が発生した場合、記録1の元の空間は更新後の記録1の記憶空間を収容できないため、データベースは記録1を削除と表記し、新しい空間を探して記録1に使用する。
    上の図の＊記録1は、元の記録1によって占有された空間を表していますが、この空間は後にフラグメント空間となり、人為的にテーブル空間の破片整理を行わない限り、使い続けることができません。
    BIG INTを使って金額フィールドを格納する場合、小数点のデータはどう表示されますか？実は、この部分は先端に任せて処理して展示することができます。データベース自体は、分で保存すればいいです。
    この記事ではMySQLの数字の種類について、増加した坑の記事を紹介します。MySQLの数字の種類はもっと多いです。私たちの以前の文章を検索してください。または次の関連記事を引き続きご覧ください。これからもよろしくお願いします。
    mysqlフルバックアップとクイックリカバリの方法整理
    MySQLのSQL Modeの表示と設定の詳細