MySQL - データベース

データベースの選択

ログイン後、データベースを選択する。

 USE <データベース名>;

MySQLサーバの接続時において、mysqlコマンドのオプションにデータベース名を指定することにより、予めデータベースを選択することができる。

mysql -u <ユーザ名> -p <データベース名>

データベースの作成

文字コード

データベースの作成時において、文字コードを指定する。

 CREATE DATABASE <データベース名>
    CHARACTER SET=utf8mb4  -- 文字コードをutf8mb4に指定する場合

MySQLのデフォルトの文字コードを確認する。

 Show Variables Like "chara%";

• character_set_client

  クライアント側で発行したSQL文はこの文字コードになる。

• character_set_connection

  クライアントから受け取った文字をこの文字コードへ変換する。

• character_set_database

  現在参照しているDBの文字コード

• character_set_results

  クライアントへ送信する検索結果はこの文字コードになる。

• character_set_server

  DB作成時のデフォルトの文字コード

• character_set_system

  システムの使用する文字セットのことであり、utf8やutf8mb3が使用される。

MySQLのデフォルトの文字コードを設定する場合は、my.cnfファイルを編集する。
my.cnfファイルを変更した後は、MySQLを再起動する必要がある。

 # my.cnfファイル
 
 [mysqld]
 character-set-server=utf8mb4   # MySQLサーバのデフォルトの文字コードを指定する
 
 [client]
 default-character-set=utf8mb4  # MySQLクライアントのデフォルトの文字コードを指定する

既存のデータベースの文字コードは、INFORMATION_SCHEMAデータベースのSCHEMATAテーブルから確認することができる。

 SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE SCHEMA_NAME = '<データベース名>';

既存のデータベースの文字コードを変更する。

 ALTER DATABASE <データベース名>
    [[DEFAULT] CHARACTER SET <文字コード>]
    [[DEFAULT] COLLATE <照合順序>]
 
 # 例: 文字コードはutf8mb4, 照合順序はutf8mb4_general_ci
 ALTER DATABASE <データベース名>
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

既存のデータベースの存在を確認して作成

既存のデータベースが存在していない場合はデータベースを作成する。
これは、データベースの重複、または、既存のデータベースを上書き/削除することを防ぐことができる。

CREATE DATABASE IF NOT EXISTS <データベース名>
   CHARACTER SET=utf8mb4

データベースの暗号化

暗号化プラグインが必要である。
なお、MySQL8では標準で暗号化プラグインがインストールされている。

CREATE DATABASE IF NOT EXISTS <データベース名>
   CHARACTER SET=utf8mb4
   ENCRYPTION='Y'  -- 暗号化を有効にする場合
                   -- 無効にする場合は、Nを指定する

照合順序

照合順序とは

MySQLにおいて、照合順序（Collation）はテーブルや列ごとに設定することができる。
照合順序は、文字列の比較やソートの際に使用され、異なる言語や地域に合わせてテキストの比較を行うために重要である。

MySQLでは、様々な照合順序が提供されている。
例えば、大文字小文字を区別やアクセントを考慮するかどうか等の違いがある。

• utf8mb4_general_ci

  大文字小文字を区別せず、アクセントを区別しない。

  通常の比較の場合には適しているが、文字の並び順は一般的な場合に従う。

  
  

• utf8mb4_bin

  バイナリ比較を行う。

  大文字小文字やアクセント等を区別して、バイト単位で比較する。

  これは厳密なバイナリ比較が必要な場合に使用される。

  
  

• utf8_general_ci

  utf8mb4と同様、大文字小文字を区別せず、アクセントを区別しない。

  ただし、utf8mb4よりも少ない文字セットを使用する。

  
  

• latin1_swedish_ci

  ラテン1文字セットに基づいた照合順序であり、スウェーデンの標準に従う。

  大文字小文字を区別せず、アクセントを区別しない。

照合順序の詳細を知りたい場合は、MySQLの公式ドキュメントを参照すること。

• MySQL 8

  https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html

データベース作成時における照合順序の指定

以下の例では、データベースの作成時において、照合順序をutf8mb4_unicode_ciに指定している。

 CREATE DATABASE <データベース名> {
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 };

※注意
照合順序は、データベース、テーブル、列ごとに指定することができ、異なる部分で異なる照合順序が設定されることがある。

デフォルトの照合順序

データベースやテーブルの照合順序が指定されていない場合、デフォルトの照合順序が使用される。

一般的に、MySQLのデフォルトの照合順序は、サーバの文字セットに基づいている。
例えば、MySQLデータベースを作成時に指定された文字セットに応じて、デフォルトの照合順序が選択される。

一般的なデフォルトの文字セットと照合順序の組み合わせには、以下に示すようなものがある。
ほとんどの場合、UTF-8マルチバイト文字セットとその一般的な照合順序がデフォルトとして使用される。

• utf8mb4
• utf8mb4_general_ci

デフォルトの照合順序を確認する場合は、以下に示すクエリを実行する。

 SHOW VARIABLES LIKE 'collation%';

このクエリは、サーバの照合順序に関する情報を表示する。
デフォルトの照合順序は、collation_serverとして表示される。

既存のデータベースの照合順序を確認する

データベースの照合順序は、INFORMATION_SCHEMAデータベースのSCHEMATAテーブルから確認することができる。

 SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE SCHEMA_NAME = '<データベース名>';

既存のデータベースの照合順序を変更する

データベースの照合順序を変更する。

 ALTER DATABASE <データベース名>
    [[DEFAULT] CHARACTER SET <文字コード>]
    [[DEFAULT] COLLATE <照合順序>]
 
 ALTER DATABASE <データベース名>
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

データベースの削除

DROP DATABESE文により削除したデータベースは、復元することができないことに注意すること。

 DROP DATABASE <データベース名>;
 
 -- または
 
 DROP DATABASE IF EXISTS <データベース名>

データベースの確認

MySQLに存在する全てのデータベースを確認する。

 SHOW DATABASES;

現在使用しているデータベースを確認する。

 USE <データベース名>;
 SELECT DATABASE();

データベースのバージョンの確認

現在使用しているデータベースのバージョンを確認する。

 USE <データベース名>;
 SELECT VERSION();

データベースのバックアップ

全てのデータベースのバックアップ

mysqldump -u root -p --opt --all-databases > <バックアップファイル名>.sql

特定のデータベースのバックアップ

mysqldump -u <ユーザ名> -p -x <バックアップするデータベース名> > <バックアップファイル名>.sql

データベースの復元

全てのデータベースの復元

まず、MySQLサービスを停止する。

sudo systemctl stop mysql

次に、データベースの実体ファイルを確認する。

• /var/lib/mysql/ib_logfileファイル

  ログ用領域ファイル

• /var/lib/mysql/ibdata1ファイル

  データ用領域ファイル

上記のファイル群を削除する。

sudo rm 'ib_logfile*' 'ibdata*'

MySQLサービスを起動する。

sudo systemctl start mysql

全てのデータベースを復元する。

mysql -u root -p < <バックアップファイル名>.sql