見落としがちなMySQLのCOLLATIONの例の詳細な説明

序文

MySQL データベースの文字列型は、CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、および SET です。異なるタイプは、ビジネス設計とデータベースパフォーマンスの点でまったく異なるパフォーマンスを持ちます。 CHAR と VARCHAR が最も一般的に使用されます。今日は、文字列型 CHAR と VARCHAR のアプリケーションについて詳しく説明します。

CHAR と VARCHAR の定義

CHAR(N) は固定長の文字を格納するために使用されます。N の範囲は 0 ～ 255 です。N はバイトではなく文字を表すことに注意してください。 VARCHAR(N)は可変長文字を格納するために使用されます。Nの範囲は0〜65536で、Nも文字を表します。

サイズが 65536 バイトを超える場合は、より大きな文字型である TEXT または BLOB の使用を検討できます。どちらも最大ストレージ長は 4G です。違いは、BLOB には文字セット属性がなく、純粋にバイナリストレージであることです。

Oracle や SQL Server などの従来のリレーショナルデータベースとは異なり、MySQL データベースの VARCHAR 文字型は最大 65536 バイトを格納できます。したがって、MySQL データベースでは、ほとんどのシナリオで VARCHAR 型で十分です。

文字セット

テーブル構造の設計では、文字を格納するための列を CHAR および VARCHAR として定義することに加えて、文字セットのエンコードが異なると各文字が異なるバイナリ値に対応するため、文字に対応する文字セットも定義する必要があります。一般的な文字セットには、GBK と UTF8 があります。通常は、デフォルトの文字セットを UTF8 に設定することをお勧めします。

また、モバイルインターネットの急速な発展に伴い、MySQL のデフォルトの文字セットを UTF8MB4 に設定することをお勧めします。そうしないと、一部の絵文字を UTF8 文字セットで保存できなくなります。たとえば、絵文字のスマイリーフェイスの対応する文字エンコーディングは 0xF09F988E です。

文字セットが UTF8 である列に絵文字を強制的に挿入すると、MySQL は次のエラーメッセージをスローします。

mysql> SHOW CREATE TABLE emoji_test\G

************************** 1. 行 ****************************

       テーブル: emoji_test

テーブルの作成: CREATE TABLE `emoji_test` (

  `a` varchar(100) 文字セット utf8,

  主キー (`a`)

) エンジン=InnoDB デフォルト文字セット=utf8



セット内の1行（0.01秒）

mysql> emoji_test に値 (0xF09F988E) を挿入します。

エラー 1366 (HY000): 行 1 の列 'a' の文字列値が正しくありません: '\xF0\x9F\x98\x8E'

MySQL バージョン 8.0 以降では、文字セットはデフォルトで UTF8MB4 に設定されています。バージョン 8.0 より前では、デフォルトの文字セットは Latin1 でした。バージョンによってデフォルトの文字セットが異なるため、構成ファイルで関連するパラメータを明示的に構成する必要があります。

[mysqld]

文字セットサーバー = utf8mb4

...

さらに、異なる文字セットには、CHAR(N) と VARCHAR(N) に対応する最長バイトが異なります。たとえば、GBK 文字セットでは 1 文字を最大 2 バイトで格納でき、UTF8MB4 文字セットでは 1 文字を最大 4 バイトで格納できます。したがって、基盤となるストレージカーネルの観点から見ると、マルチバイト文字セットでは、基盤となる CHAR と VARCHAR の実装はまったく同じであり、どちらも可変長ストレージです。

上記の例から、CHAR(1) は 1 バイトの 'a' と 4 バイトの絵文字スマイリーフェイスの両方を格納できることがわかります。つまり、CHAR も本質的に可変長です。

現在推奨されているデフォルトの文字セットは UTF8MB4 であるため、テーブル構造を設計するときに、すべての CHAR を VARCHAR に置き換えることができ、基礎となるストレージは基本的に同じになります。

並べ替えルール

照合は、文字列を比較および並べ替えるためのルールです。各文字セットにはデフォルトの照合があり、SHOW CHARSET コマンドで表示できます。

mysql> SHOW CHARSET LIKE 'utf8%';

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

| 文字セット | 説明 | デフォルトの照合順序 | Maxlen |

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

| utf8 | UTF-8 ユニコード | utf8_general_ci | 3 |

| utf8mb4 | UTF-8 ユニコード | utf8mb4_0900_ai_ci | 4 |

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

セットに2行（0.01秒）



mysql> SHOW COLLATION LIKE 'utf8mb4%';

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

| 照合 | 文字セット | ID | デフォルト | コンパイル済み | ソート長 | Pad_attribute |

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

| utf8mb4_0900_ai_ci | utf8mb4 | 255 | はい | はい | 0 | パッドなし |

| utf8mb4_0900_as_ci | utf8mb4 | 305 | | はい | 0 | パッドなし |

| utf8mb4_0900_as_cs | utf8mb4 | 278 | | はい | 0 | パッドなし |

| utf8mb4_0900_bin | utf8mb4 | 309 | | はい | 1 | パッドなし |

| utf8mb4_bin | utf8mb4 | 46 | | はい | 1 | パッドスペース |

......

ソート規則は、大文字と小文字を区別しないことを意味する _ci、大文字と小文字を区別することを意味する _cs、および文字のバイナリ表現を格納することによる比較を意味する _bin で終わります。 MySQL 文字列を比較する場合、デフォルトの照合では大文字と小文字が区別されないことに注意してください。

mysql> 'a' = 'A' を選択します。

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

| 'a' = 'A' |

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

| 1 |

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

セット内の 1 行 (0.00 秒)



mysql> SELECT CAST('a' as char) COLLATE utf8mb4_0900_as_cs = CAST('A' as CHAR) COLLATE utf8mb4_0900_as_cs as result;

+--------+

| 結果 |

+--------+

| 0 |

+--------+

セット内の 1 行 (0.00 秒)

ほとんどのビジネスのテーブル構造設計では、並べ替えルールを大文字と小文字を区別するように設定する必要はありません。あなたのビジネスに本当に必要なものが何であるかを理解できない限り。

文字セットを正しく変更する

もちろん、多くの企業は設計時に文字セットが業務データストレージに与える影響を考慮していないため、後で文字セットの変換が必要になると思います。しかし、多くの学生は、次の操作を実行した後でも、絵文字などの UTF8MB4 文字を挿入できないことに気付くでしょう。

テーブル emoji_test の文字セットを utf8mb4 に変更します。

実際、上記の変更では、テーブルの文字セットが UTF8MB4 に変更されるだけです。次に新しい列を追加するときに、文字セットを明示的に指定しないと、新しい列の文字セットは UTF8MB4 に変更されます。ただし、既存の列の場合、デフォルトの文字セットは変更されません。これを確認するには、SHOW CREATE TABLE コマンドを実行します。

mysql> SHOW CREATE TABLE emoji_test\G

************************** 1. 行 ****************************

       テーブル: emoji_test

テーブルの作成: CREATE TABLE `emoji_test` (

  `a` varchar(100) 文字セット utf8 COLLATE utf8_general_ci NOT NULL,

  主キー (`a`)

) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8mb4_0900_ai_ci

セット内の 1 行 (0.00 秒)

列 a の文字セットは UTF8MB4 ではなく、まだ UTF8 であることがわかります。したがって、列の文字セットを変更する正しいコマンドは ALTER TABLE ... CONVERT TO... であり、これにより、前の列の文字セットを UTF8 から UTF8MB4 に変更できます。

mysql> ALTER TABLE emoji_test を CHARSET utf8mb4 に変換します。

クエリは正常、影響を受けた行は 0 行 (0.94 秒)

レコード: 0 重複: 0 警告: 0



mysql> SHOW CREATE TABLE emoji_test\G

************************** 1. 行 ****************************

       テーブル: emoji_test

テーブルの作成: CREATE TABLE `emoji_test` (

  `a` varchar(100) 文字セット utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,

  主キー (`a`)

) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8mb4_0900_ai_ci

セット内の 1 行 (0.00 秒)

ビジネステーブル構造の実用的な設計

ユーザーの性別デザイン

テーブル構造を設計するときに、固定されたオプション値を持ついくつかのフィールドに遭遇するでしょう。たとえば、性別フィールド (Sex) には男性または女性のみを入力できます。また、状態フィールド (State) には、実行中、停止、再開などの有効な値が制限されています。
ほとんどの開発者は、性別フィールドを保存するために INT 数値型を使用することを好むことがわかりました。たとえば、次のようになります。

テーブル `User` を作成します (

  `id` bigint NOT NULL AUTO_INCREMENT、

  `sex` tinyint デフォルト NULL,

  ......

  主キー (`id`)

)ENGINE=InnoDB;

その中で、tinyint 列の sex はユーザーの性別を示していますが、この設計上の問題は明らかです。

不明瞭な表現: データを保存するとき、0 は女性を表しますか、それとも 1 は女性を表しますか?各企業には異なる暗黙のルールがある場合があります。
ダーティデータ: tinyint であるため、0 と 1 に加えて、ユーザーは 2、3、4 などの値を挿入できます。最終的には、テーブルに無効なデータが存在する可能性があり、後でクリーンアップするコストが非常に高くなります。

MySQL 8.0 より前では、ENUM 文字列列挙型を使用できましたが、挿入できるのは定義された値の限られた数のみでした。パラメータ SQL_MODE が厳密モードに設定されている場合、未定義のデータを挿入するとエラーが発生します。

mysql> SHOW CREATE TABLE ユーザー\G

************************** 1. 行 ****************************

       表: ユーザー

テーブルの作成: CREATE TABLE `User` (

  `id` bigint NOT NULL AUTO_INCREMENT、

  `sex` enum('M','F') COLLATE utf8mb4_general_ci デフォルト NULL,

  主キー (`id`)

) エンジン=InnoDB

セット内の 1 行 (0.00 秒)



mysql> SET sql_mode = 'STRICT_TRANS_TABLES';

クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)



mysql> ユーザー VALUES (NULL, 'F') に INSERT INTO します。

クエリは正常、1 行が影響を受けました (0.08 秒)



mysql> ユーザー VALUES (NULL, 'A') に INSERT INTO します。

エラー 1265 (01000): 行 1 の列 'sex' のデータが切り捨てられました

ENUM 型は SQL 標準データ型ではなく、MySQL 固有の文字列型であるためです。表示されるエラーメッセージも直感的ではありません。この実装は、主に MySQL バージョン 8.0 より前では制約機能が提供されていないため、常に少し残念です。 MySQL 8.0.16 以降、データベースは CHECK 制約関数をネイティブに提供しており、有限状態列タイプの設計を容易にすることができます。

mysql> SHOW CREATE TABLE ユーザー\G

************************** 1. 行 ****************************

       表: ユーザー

テーブルの作成: CREATE TABLE `User` (

  `id` bigint NOT NULL AUTO_INCREMENT、

  `sex` char(1) COLLATE utf8mb4_general_ci デフォルト NULL,

  主キー (`id`)、

  制約 `user_chk_1` チェック (((`sex` = _utf8mb4'M') または (`sex` = _utf8mb4'F')))

) エンジン=InnoDB

セット内の 1 行 (0.00 秒)



mysql> ユーザー VALUES (NULL, 'M') に INSERT INTO します。

クエリは正常、1 行が影響を受けました (0.07 秒)



mysql> ユーザー VALUES (NULL, 'Z') に INSERT INTO します。

エラー 3819 (HY000): チェック制約 'user_chk_1' に違反しています。

このコードから、8 行目の制約定義 user_chk_1 が性別列の値の範囲を示しており、その範囲は M または F のみであることがわかります。同時に、行 15 に不正なデータ Z が挿入されると、MySQL が不正な制約に関するプロンプトを明示的に表示していることがわかります。

アカウントパスワード保存設計

データベーステーブル構造を設計する際には、パスワードをデータベーステーブルに直接保存しないでください。悪意のあるユーザーがシステムに侵入すると、ユーザーデータが漏洩する大きなリスクがあります。たとえば金融業界では、コンプライアンスの観点から、すべてのユーザーのプライバシーフィールドを暗号化する必要があり、企業自体でさえユーザーが保存した情報 (ログインパスワード、携帯電話、クレジットカード情報などのプライバシーデータ) を知ることはできません。

多くの開発者は、プライベートデータを暗号化して保存するために MD5 関数を使用すると思います。これは、MD5 アルゴリズムが元に戻せないため正しいです。ただし、MD5 暗号化後の値は固定です。たとえば、パスワード 12345678 の MD5 値は 25d55ad283aa400af464c76d713c07ad に固定されます。

したがって、MD5 をブルートフォースクラッキングして、考えられるすべての文字列に対応する MD5 値を計算できます。すべての文字列の組み合わせを列挙することが不可能な場合は、111111、12345678 などの一般的なパスワードを計算することができます。文書に記載した Web サイトを使用すると、MD5 で暗号化された文字列をオンラインで復号化できます。

そのため、パスワードの保存を設計する際にはソルトを追加する必要があります。各社のソルト値が異なるため、計算される値も異なります。ソルト値が psalt の場合、データベース内のパスワード 12345678 の値は次のようになります。

パスワード = MD5('psalt12345678')

このパスワード保存設計は、固定ソルト値を持つ暗号化アルゴリズムであり、主に次の 3 つの問題があります。

ソルト値が（元）従業員によって漏洩された場合、外部のハッカーがそれを解読して大きな利益を得る可能性が依然として残ります。

同じパスワードの場合、パスワード保存値は同じです。ユーザーのパスワードが漏洩すると、同じパスワードを持つ他のユーザーのパスワードも漏洩します。

MD5 暗号化アルゴリズムは固定的に使用されています。MD5 アルゴリズムが解読されると、その影響は大きくなります。

したがって、本当に優れたパスワード保存設計は、動的ソルト + 固定されていない暗号化アルゴリズムである必要があります。

パスワードは次のように設計することをお勧めします。パスワード列は次の形式で保存されます。

$ソルト$暗号化アルゴリズム$値

で：

$salt: 動的なソルトを表します。ユーザーが登録するたびに、ビジネスは異なるソルト値を生成し、データベースに保存します。さらに洗練されたものにしたい場合は、動的なソルト値 + ユーザー登録日を組み合わせて、より動的なソルト値を作成できます。
$cryption_algorithm: 暗号化アルゴリズムを示します。たとえば、v1 は MD5 暗号化アルゴリズム、v2 は AES256 暗号化アルゴリズム、v3 は AES512 暗号化アルゴリズムを示します。
$value: 暗号化された文字列を表します。

現時点での User テーブルの構造設計は次のとおりです。

テーブルユーザーの作成 (

    id BIGINT NOT NULL AUTO_INCREMENT、

    名前 VARCHAR(255) NOT NULL,

    性別 CHAR(1) NOT NULL,

    パスワード VARCHAR(1024) NOT NULL,

    regDate DATETIME NULLではありません。

    チェック (性別 = 'M' または 性別 = 'F')、

    主キー(id)

);



ユーザー\Gから*を選択

************************** 1. 行 ****************************

      id: 1

    名前: デビッド

     性別: 男性

パスワード: $fgfaef$v1$2198687f6db06c9d1b31a030ba1ef074

 登録日: 2020-09-07 15:30:00

************************** 2. 行 ****************************

      id: 2

    名前: エイミー

     性別: 女性

パスワード: $zpelf$v2$0x860E4E3B2AA4005D8EE9B7653409C4B133AF77AEF53B815D31426EC6EF78D882

 登録日: 2020-09-07 17:28:00

上記の例では、ユーザー David と Amy のパスワードは両方とも 12345678 です。ただし、動的ソルトと動的暗号化アルゴリズムを使用しているため、2 つに保存されている内容は完全に異なります。

たとえ悪意のあるユーザーが現在のパスワード暗号化アルゴリズムを入手したとしても、ユーザーの保存されたパスワードは暗号化アルゴリズム $cryption_algorithm バージョンを通じてアップグレードされ、悪意のあるデータ攻撃をさらに防ぐことができます。

要約する

皆さんが見逃していたかもしれないMySQLのCOLLATIONに関する記事はこれで終わりです。MySQLのCOLLATIONについてさらに詳しく知りたい方は、123WORDPRESS.COMの過去の記事を検索するか、以下の関連記事を引き続きご覧ください。今後とも123WORDPRESS.COMをよろしくお願いいたします。

以下もご興味があるかもしれません: