MariaDBの基本をマスター！初心者向け導入ガイド

By /

MariaDBの基本をマスター！初心者向け導入ガイド

はじめに

データベース。この言葉を聞いて、「難しそう」「プログラマーだけが使うもの」と感じる方もいるかもしれません。しかし、現代の多くのアプリケーションやWebサービスにおいて、データベースは情報を効率的に管理し、活用するための基盤となっています。私たちが日常的に利用するSNS、オンラインショッピングサイト、ブログシステムなど、その裏側には必ずと言っていいほどデータベースが存在しています。

数あるデータベースシステムの中でも、特に広く普及しているのが「リレーショナルデータベース」です。そして、その代表格の一つが「MariaDB」です。MariaDBはオープンソースのリレーショナルデータベース管理システム（RDBMS）であり、高速、堅牢、そして非常に高い互換性を持つことで知られています。かつて、多くのシステムで利用されていたMySQLから派生しており、MySQLユーザーにとっては非常に親しみやすい存在です。

このガイドは、「データベースを使ったことがない」「プログラミングは少し触ったことがあるけれど、データベースは未知の領域」「MariaDBを学びたいけど、何から始めればいいか分からない」といった初心者の方を対象としています。約5000語というボリュームで、MariaDBの導入から、データベース、テーブルの作成、データの操作、そして基本的な管理タスクまで、MariaDBを使い始めるために必要な基本の全てを網羅的に解説します。

この記事を読み終える頃には、あなたはMariaDBの基本的な操作を理解し、自信を持ってデータベースを扱えるようになっているはずです。さあ、MariaDBの世界へ一緒に飛び込みましょう！

データベースの基本概念

MariaDBに触れる前に、まずはデータベースそのものの基本的な概念を理解しておきましょう。

データベースとは

データベース（Database; DB）とは、コンピュータ上でデータを集積・整理し、効率的に検索、追加、更新、削除といった操作を行えるように管理されたデータの集合体です。単にデータを保存するだけでなく、構造化して管理することで、必要な情報を素早く見つけ出し、分析したり、アプリケーションで利用したりすることが可能になります。

リレーショナルデータベースとは

データベースには様々な種類がありますが、最も一般的で広く使われているのが「リレーショナルデータベース」です。リレーショナルデータベースでは、データを「テーブル（表）」として管理します。テーブルは行と列で構成され、それぞれの行が一つのデータレコード、それぞれの列がデータの属性（フィールド）を表します。

複数のテーブルを作成し、それぞれのテーブル間で「関連（リレーション）」を持たせることができるのが特徴です。例えば、「顧客情報」テーブルと「注文履歴」テーブルがあるとします。顧客情報テーブルには顧客ID、名前、住所など、注文履歴テーブルには注文ID、顧客ID、商品名、数量などが含まれます。この両方のテーブルにある「顧客ID」を共通の鍵（キー）として関連付けることで、「特定の顧客の注文履歴を全て取得する」といった操作が容易になります。

RDBMSとは

RDBMS（Relational Database Management System）とは、リレーショナルデータベースを管理するためのソフトウェアです。データの作成、読み込み、更新、削除といった基本的な操作（CRUD操作）はもちろん、データの整合性を保つための制約の設定、複数ユーザーからの同時アクセス制御、セキュリティ管理、バックアップと復旧など、様々な機能を提供します。MariaDBはこのRDBMSの一つです。他にもMySQL、PostgreSQL、Oracle Database、SQL Serverなどが有名なRDBMSです。

基本用語

リレーショナルデータベースを扱う上で必ず知っておきたい基本的な用語をいくつか紹介します。

  • テーブル (Table): データを格納する基本的な単位で、行と列からなる表形式の構造をしています。スプレッドシートのシートのようなものをイメージすると分かりやすいでしょう。例：「ユーザー情報テーブル」「商品一覧テーブル」。
  • カラム (Column) / フィールド (Field): テーブルの列のことです。各カラムは特定の種類のデータを格納します。例えば「ユーザー情報テーブル」のカラムには「ユーザーID」「氏名」「メールアドレス」などがあります。各カラムにはデータ型（整数、文字列、日付など）が定義されます。
  • 行 (Row) / レコード (Record) / タプル (Tuple): テーブルの行のことです。一つの行は関連するデータの集まりを表します。例えば「ユーザー情報テーブル」の一つの行は、特定のユーザーに関する全ての情報（ユーザーID、氏名、メールアドレスなど）を含みます。
  • 主キー (Primary Key): テーブル内の各行を一意に識別するためのカラム（またはカラムの組み合わせ）です。主キーのカラムにはNULLや重複する値を格納することはできません。例えば「ユーザーID」は、各ユーザーを一意に識別するためによく主キーとして使われます。
  • 外部キー (Foreign Key): あるテーブルのカラムが、別のテーブルの主キーを参照している場合に、そのカラムを外部キーと呼びます。外部キーを設定することで、テーブル間の関連性を表現し、データの整合性を保つことができます。例えば「注文履歴テーブル」の「顧客ID」カラムが、「顧客情報テーブル」の主キーである「顧客ID」を参照している場合、「注文履歴テーブル」の「顧客ID」は外部キーとなります。これにより、存在しない顧客IDを持つ注文データが誤って登録されるのを防ぐといったことが可能になります。
  • SQL (Structured Query Language): リレーショナルデータベースを操作するための標準的な言語です。テーブルの作成、データの検索、追加、更新、削除など、データベースに対するほとんどの操作はSQL文を発行して行います。MariaDBもSQLを使って操作します。

MariaDBの紹介

MariaDBは、その名前がMySQLに似ていることからも分かるように、MySQLから派生したデータベースシステムです。

MariaDBの歴史（MySQLからの分岐）

MariaDBは、MySQLのオリジナル開発者たちによって2009年に開発が始まりました。これは、MySQLがOracle Corporationに買収されたことによる、将来的なオープンソース性の維持への懸念から生まれたプロジェクトです。MariaDBはMySQLと高い互換性を保ちながらも、コミュニティ主導の開発を続け、独自の機能強化やパフォーマンス改善を進めています。

MariaDBの特徴

MariaDBは、MySQLの多くの優れた点を引き継ぎつつ、さらに進化を続けています。その主な特徴は以下の通りです。

  • パフォーマンスと信頼性: 大規模なデータを扱う場合でも高速な処理が可能で、ミッションクリティカルなシステムでも利用できる高い信頼性を持っています。特に最新バージョンでは、様々な最適化が施されています。
  • オープンソース: GPLライセンスの下で公開されており、誰でも自由に利用、改変、配布することができます。これはコスト面だけでなく、透明性やコミュニティによる活発な開発という点でも大きなメリットです。
  • コミュニティ主導: 世界中の開発者やユーザーが参加する活発なコミュニティによって支えられています。バグ報告や機能改善の提案が活発に行われ、迅速なアップデートが期待できます。
  • 機能の豊富さ: 標準的なSQL機能はもちろん、最新のバージョンではJSON関数のサポート、共通テーブル式(CTE)、ウィンドウ関数など、モダンなデータベースに必要な機能が多数実装されています。また、様々なストレージエンジン（後述）を選択できる柔軟性も特徴です。
  • セキュリティ: セキュリティ関連の機能も強化されており、データの保護に配慮されています。
  • MySQLとの高い互換性: ほとんどの場合、MySQL用に開発されたアプリケーションやスクリプトは、大きな変更なしにMariaDBで動作します。これにより、MySQLからの移行が比較的容易です。

MySQLとの互換性と違い

MariaDBはMySQLのコードベースから分岐したため、基本的な操作方法やSQL構文はMySQLとほぼ同じです。多くのMySQLクライアントツールやライブラリはそのままMariaDBでも利用できます。

しかし、開発が進むにつれて、MariaDBはMySQLとは異なる独自の機能や最適化を取り入れています。例えば、デフォルトのストレージエンジンがInnoDBからAria（またはInnoDB）に変更されたり、特定のクエリの実行計画が異なったりすることがあります。また、MySQLの一部の商用機能に相当する機能がMariaDBではオープンソースとして提供されている場合もあります。

初心者の方が基本的な操作を行う上で、MySQLとの違いを意識する必要はほとんどありません。基本的なSQL構文や概念は共通であるため、MySQLの学習リソースもMariaDBの学習に役立ちます。

なぜ初心者におすすめなのか

MariaDBが初心者におすすめできる理由はいくつかあります。

  1. 広く使われている: 世界中で多くのWebサイトやアプリケーションで利用されており、情報や事例が豊富に見つかります。
  2. 学習リソースが豊富: MySQLと高い互換性があるため、MySQLの公式ドキュメントやチュートリアル、書籍などがそのままMariaDBの学習に活用できます。
  3. オープンソースで無料: コストを気にせず、気軽に学習環境を構築できます。
  4. 扱いやすい: コマンドラインツールもGUIツールも充実しており、自分のスタイルに合った方法で学習を進められます。
  5. 将来性: 活発なコミュニティによって開発が続けられており、今後も進化していくことが期待できます。

これらの理由から、データベース学習の第一歩としてMariaDBを選択することは非常に良い選択と言えるでしょう。

MariaDBのインストール

MariaDBを利用するには、まずお使いのコンピュータにMariaDBサーバーをインストールする必要があります。ここでは主要なOS（Windows, macOS, Linux）での一般的なインストール方法を紹介します。

Windowsへのインストール方法

Windowsの場合、公式ウェブサイトから提供されているインストーラーを使用するのが最も簡単です。

  1. MariaDBの公式サイト（mariadb.org）にアクセスし、ダウンロードページへ移動します。
  2. お使いのWindowsのバージョン（32ビットまたは64ビット）に合ったインストーラー（.msiファイル）をダウンロードします。通常は最新の安定版を選べば良いでしょう。
  3. ダウンロードしたインストーラーを実行します。
  4. セットアップウィザードが表示されます。「Next」をクリックして進めます。
  5. ライセンス同意画面で内容を確認し、「I accept the terms in the License Agreement」にチェックを入れて「Next」。
  6. 「Custom Setup」画面で、インストールするコンポーネントを選択します。通常は全てのコンポーネント（MariaDB Server, Client Programs, Documentation, Heading Files and Libraries）を選択したままで良いでしょう。「Browse…」でインストール先を変更することも可能ですが、特に理由がなければデフォルトのままで構いません。「Next」。
  7. 「Configuration」画面が表示されます。
    • Root password: データベースの管理者であるrootユーザーのパスワードを設定します。このパスワードは非常に重要なので、忘れないように必ずメモしておいてください。セキュリティのため、複雑なパスワードを設定することを推奨します。
    • Enable access from remote machines for ‘root’ user: チェックを入れると、他のコンピュータからrootユーザーで接続できるようになります。開発環境であればチェックを入れても構いませんが、本番環境やインターネットに接続された環境では、セキュリティリスクを高めるため、通常はチェックを外しておくべきです。リモート接続が必要な場合は、別途専用のユーザーを作成して制限付きの権限を与えるのが一般的です。
    • Use UTF8 as default character set: チェックを入れると、デフォルトの文字コードとしてUTF-8が使われます。日本語を含む多言語を扱う上でUTF-8は必須なので、必ずチェックを入れてください。
    • Install as service: MariaDBをWindowsサービスとしてインストールし、Windows起動時に自動的に開始するように設定します。通常はチェックを入れておきます。
    • Service name: サービス名を任意に指定できますが、デフォルトのMariaDBで構いません。
    • TCP port: MariaDBサーバーが使用するポート番号です。デフォルトは3306です。他のサービスと競合しない限り、デフォルトのままで構いません。
    • InnoDB/Aria Page Size: ストレージエンジンのページサイズを設定します。通常はデフォルトの16KBで問題ありません。
  8. 設定を確認し、「Next」をクリック。
  9. 「Ready to install MariaDB」画面が表示されます。「Install」をクリックするとインストールが開始されます。
  10. インストールが完了すると、ウィザードが終了します。「Finish」をクリックしてください。

インストール後、MariaDBサーバーが起動していることを確認します。Windowsのサービス一覧（services.mscで検索）でMariaDBという名前のサービスが「実行中」になっているか確認できます。

macOSへのインストール方法

macOSの場合、パッケージマネージャーであるHomebrewを使用するのが最も一般的で簡単です。Homebrewがインストールされていない場合は、まずHomebrewをインストールしてください（https://brew.sh/ を参照）。

  1. ターミナルを開きます。
  2. Homebrewを使ってMariaDBをインストールします。
    bash
    brew install mariadb
    これにより、最新版のMariaDBがインストールされます。
  3. インストールが完了したら、初期設定を行います。MariaDBサーバーの起動や、セキュリティ設定を行うスクリプトが用意されています。
    bash
    mysql_secure_installation
    このスクリプトを実行すると、以下の設定を対話形式で行えます。

    • rootユーザーのパスワード設定（Homebrewでインストールした場合、デフォルトではパスワードなしです）
    • 匿名ユーザーの削除
    • リモートからのrootログイン禁止
    • テストデータベースの削除
    • 権限テーブルの再読み込み
      プロンプトに従って設定を進めてください。特にrootユーザーのパスワード設定は必ず行ってください。
  4. MariaDBサーバーを起動します。
    bash
    brew services start mariadb
    これでMariaDBサーバーがバックグラウンドで起動します。停止する場合はbrew services stop mariadb、再起動する場合はbrew services restart mariadbです。

Linuxへのインストール方法

Linuxの場合、ディストリビューションのパッケージマネージャーを使用するのが最も簡単です。ここでは代表的なDebian/Ubuntu系とCentOS/RHEL系での方法を示します。

Debian/Ubuntu系 (apt)

  1. パッケージリストを更新します。
    bash
    sudo apt update
  2. MariaDBサーバーとクライアントをインストールします。
    bash
    sudo apt install mariadb-server mariadb-client
  3. インストールが完了すると、MariaDBサービスが自動的に起動します。サービスのステータスを確認できます。
    bash
    sudo systemctl status mariadb
    「active (running)」と表示されていれば起動しています。
  4. セキュリティ設定を行います。
    bash
    sudo mysql_secure_installation
    macOSと同様に、rootユーザーのパスワード設定などが行えます。rootユーザーは最初はパスワードなし、またはOSのrootユーザーで認証される設定になっていることが多いですが、mysql_secure_installationでパスワードを設定してください。

CentOS/RHEL系 (yum/dnf)

  1. MariaDBサーバーとクライアントをインストールします。CentOS 7まではyum、CentOS 8以降やFedoraではdnfを使います。
    bash
    # CentOS 7まで
    sudo yum install mariadb-server mariadb
    # CentOS 8以降 / Fedora
    sudo dnf install mariadb-server mariadb
  2. MariaDBサービスを起動し、システム起動時に自動実行されるように設定します。
    bash
    sudo systemctl start mariadb
    sudo systemctl enable mariadb
  3. サービスのステータスを確認します。
    bash
    sudo systemctl status mariadb
    「active (running)」と表示されていれば起動しています。
  4. セキュリティ設定を行います。
    bash
    sudo mysql_secure_installation
    rootユーザーのパスワード設定などを行います。

インストール後の初期設定 (mysql_secure_installation)

mysql_secure_installationは、MariaDBのインストール直後に行うべき非常に重要なセキュリティ設定スクリプトです。Windowsインストーラーには組み込まれていることが多いですが、macOSやLinuxで手動で実行する必要がある場合が多いです。

このスクリプトでは、主に以下の設定を行います。

  • Rootパスワードの設定: デフォルトではパスワードが設定されていない場合があるため、安全なパスワードを設定します。
  • 匿名ユーザーの削除: テスト目的などで作成される匿名ユーザーを削除します。これにより、認証なしでデータベースにアクセスされるリスクを減らします。
  • リモートからのRootログイン禁止: rootユーザーがローカルホスト以外からログインできないようにします。リモートからの管理が必要な場合は、別途、必要な権限だけを持つユーザーを作成してアクセスさせます。
  • テストデータベースの削除: デフォルトで用意されているテストデータベースとそのアクセス権限を削除します。
  • 権限テーブルの再読み込み: ここまでの変更を即座に反映させます。

プロンプトに従って質問に答えていけば設定できます。基本的に、全ての質問に「Yes」（y）と答えて、セキュリティを高める設定を選択することを推奨します。ただし、環境によってはリモートからのrootログインを一時的に許可する必要がある場合などもありますので、状況に合わせて判断してください。しかし、学習目的であれば全てYesで問題ありません。

バージョンの確認方法

MariaDBサーバーが正しくインストールされたか、バージョンを確認してみましょう。ターミナルまたはコマンドプロンプトを開き、以下のコマンドを実行します。

bash
mysql --version

または、MariaDBクライアントにログインしてから確認することもできます。

sql
SELECT VERSION();

MariaDBクライアントの使い方

MariaDBサーバーに接続し、データベースを操作するためには「クライアント」が必要です。ここでは、最も基本的なコマンドラインクライアント(mysql)の使い方を解説します。

コマンドラインクライアント (mysql) の起動と終了

MariaDBサーバーが起動していることを確認したら、コマンドラインクライアントを起動してみましょう。ターミナルまたはコマンドプロンプトを開き、以下のコマンドを入力します。

bash
mysql -u root -p

  • -u root: ユーザー名をrootとして接続することを示します。rootはデータベースの管理者権限を持つ特別なユーザーです。
  • -p: パスワードの入力を求めるプロンプトを表示します。

コマンドを実行すると、「Enter password:」と表示されるので、インストール時に設定したrootユーザーのパスワードを入力してEnterキーを押します。パスワードを入力しても画面には表示されません。

パスワードが正しければ、MariaDBのプロンプトが表示されます。

“`
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 123
Server version: 10.x.x-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>
“`

プロンプトが MariaDB [(none)]> となっていれば接続成功です。カッコ内の(none)は、現在どのデータベースも選択していない状態であることを示します。

MariaDBクライアントを終了するには、プロンプトで以下のコマンドを入力します。

sql
QUIT;

または

sql
\q

どちらかのコマンドを入力してEnterキーを押すと、クライアントが終了し、元のシェルに戻ります。

ユーザー認証

mysql -u root -p のように、-uオプションでユーザー名、-pオプションでパスワードを指定して接続します。パスワードをコマンドラインに直接記述することも可能ですが、セキュリティ上推奨されません。

“`bash

非推奨！パスワードが履歴に残る可能性がある

mysql -u root -ppassword123
“`

-pオプションを付けてパスワードを後から入力する方法がより安全です。

基本的なコマンド

MariaDBクライアントに接続中に使える便利なコマンドがいくつかあります。これらはSQL文ではなく、クライアント独自のコマンドで、セミコロン;ではなくバックスラッシュ\から始まるものが多いです。

  • status; または \s: 現在の接続情報（サーバーバージョン、接続ユーザー、文字コードなど）を表示します。
  • help; または \h: MariaDBモニターコマンドに関するヘルプを表示します。特定のSQL文（例: help SELECT;）やコマンド（例: help status;）に関するヘルプも表示できます。
  • quit; または \q: クライアントを終了します。
  • \c: 入力中のSQL文をキャンセルし、新しい入力行に戻ります。

SQL文の入力と実行

MariaDBクライアントに接続したら、SQL文を入力して実行できます。SQL文は通常、セミコロン;で終わる必要があります。

例：MariaDBの現在のバージョンを表示するSQL文

sql
SELECT VERSION();

これをプロンプトに入力してEnterキーを押します。

MariaDB [(none)]> SELECT VERSION();
+---------------+
| VERSION() |
+---------------+
| 10.x.x-MariaDB|
+---------------+
1 row in set (0.00 sec)

結果が表示されました。SQL文は複数行に渡って入力することも可能です。Enterキーを押しても、セミコロンを入力するまでは実行されず、新しい行で入力が続けられます。

sql
MariaDB [(none)]> SELECT
-> VERSION
-> ();
+---------------+
| VERSION() |
+---------------+
| 10.x.x-MariaDB|
+---------------+
1 row in set (0.00 sec)

->は、前の行からの入力が続いていることを示しています。

データベースの作成と管理

いよいよ実際にデータベースを作成し、管理する操作に進みましょう。

データベース一覧の表示

サーバーに存在するデータベースの一覧を表示するには、以下のSQL文を使います。

sql
SHOW DATABASES;

実行例：

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

ここに表示されているデータベース（information_schema, mysql, performance_schema, sys）は、MariaDBサーバー自身の動作のために使われるシステムデータベースです。誤ってこれらのデータベースの内容を変更したり削除したりしないように注意してください。

データベースの作成

新しいデータベースを作成するには、CREATE DATABASE文を使います。例えば、mydatabaseという名前のデータベースを作成する場合：

sql
CREATE DATABASE mydatabase;

成功すると、以下のようなメッセージが表示されます。

Query OK, 1 row affected (0.00 sec)

もう一度SHOW DATABASES;を実行すると、作成したmydatabaseが表示されているはずです。

データベースの選択

データベース内のテーブルを操作するには、まずそのデータベースを選択する必要があります。選択するにはUSE文を使います。

sql
USE mydatabase;

成功すると、プロンプトが変化し、現在選択されているデータベース名が表示されます。

Database changed
MariaDB [mydatabase]>

これ以降、特に指定がない限り、実行されるSQL文は選択したmydatabaseに対して行われます。別のデータベースを選択したい場合は、再度USE文を実行します。

データベースの削除

作成したデータベースが不要になった場合は、DROP DATABASE文で削除できます。この操作は元に戻せません。重要なデータが入っていないか十分に確認してから実行してください。

sql
DROP DATABASE mydatabase;

成功すると、データベースが削除されます。

Query OK, 0 rows affected (0.01 sec)

SHOW DATABASES;で確認すると、mydatabaseが消えているはずです。

文字コードと照合順序の設定

データベースを作成する際、文字コードと照合順序を指定することが推奨されます。日本語を適切に扱うためには、UTF-8（utf8mb4が推奨）を指定するのが一般的です。

  • 文字コード (CHARACTER SET): 文字のエンコーディング方式を定義します。utf8mb4は、絵文字など4バイトのUTF-8文字も扱えるため、utf8よりも推奨されます。
  • 照合順序 (COLLATE): 文字列の比較や並べ替えのルールを定義します。日本語の場合、utf8mb4_general_ciなどがよく使われます。_ciはCase Insensitive（大文字小文字を区別しない）の意味です。

データベース作成時に文字コードと照合順序を指定するには、以下のようにします。

sql
CREATE DATABASE mydatabase_utf8
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

このように設定しておくと、そのデータベース内に作成されるテーブルもデフォルトで指定した文字コードと照合順序になります。後から変更することも可能ですが、作成時に設定しておくと手間が省けます。

テーブルの作成と管理

データベースを作成したら、いよいよデータを格納するためのテーブルを作成します。

テーブル一覧の表示

現在選択しているデータベースに存在するテーブルの一覧を表示するには、以下のSQL文を使います。

sql
SHOW TABLES;

データベースを作成したばかりで、まだテーブルがない場合は、何も表示されないか、空のセットが返されます。

MariaDB [mydatabase]> SHOW TABLES;
Empty set (0.00 sec)

テーブルの作成

テーブルを作成するには、CREATE TABLE文を使います。テーブル名と、それに含まれるカラムの名前、データ型、制約を定義します。

基本的な構文：

sql
CREATE TABLE テーブル名 (
カラム名1 データ型 [制約],
カラム名2 データ型 [制約],
...
[テーブルレベルの制約]
);

例：簡単なユーザー情報を格納するusersテーブルを作成します。

sql
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

上記の例で使われている要素について説明します。

  • users: テーブル名です。
  • user_id INT AUTO_INCREMENT PRIMARY KEY:
    • user_id: カラム名です。
    • INT: データ型です。整数を格納します。
    • AUTO_INCREMENT: このカラムに新しい行が挿入されるたびに、自動的にユニークな連番が生成され、格納されます。主キーと組み合わせて使うことが一般的です。
    • PRIMARY KEY: このカラムをテーブルの主キーとして設定します。
  • username VARCHAR(50) NOT NULL UNIQUE:
    • username: カラム名です。
    • VARCHAR(50): データ型です。可変長の文字列を格納します。カッコ内の50は最大長です。
    • NOT NULL: このカラムにNULL（値がない状態）を格納することを禁止します。必ず何らかの値が入っている必要があります。
    • UNIQUE: このカラムの値がテーブル内で一意であることを保証します。重複する値の格納を禁止します。
  • email VARCHAR(100) UNIQUE:
    • email: カラム名です。
    • VARCHAR(100): 最大長100の可変長文字列型です。
    • UNIQUE: メールアドレスが重複しないように設定します。ただし、NOT NULLがないため、NULLは複数格納できます（NULLは「値がない」状態であり、互いに比較できないため）。
  • created_at DATETIME DEFAULT CURRENT_TIMESTAMP:
    • created_at: カラム名です。
    • DATETIME: データ型です。日付と時刻（YYYY-MM-DD HH:MM:SS形式）を格納します。
    • DEFAULT CURRENT_TIMESTAMP: このカラムに値を指定せずに新しい行が挿入された場合、その時点の現在の日時が自動的に格納されます。

テーブル作成が成功すると、Query OKと表示されます。

MariaDB [mydatabase]> CREATE TABLE users ( ... );
Query OK, 0 rows affected (0.01 sec)

SHOW TABLES;を実行すると、usersテーブルが表示されるはずです。

テーブル定義の確認

作成したテーブルのカラム構成や制約を確認するには、DESCRIBE文またはSHOW CREATE TABLE文を使います。

DESCRIBE テーブル名; または DESC テーブル名;

sql
DESCRIBE users;

実行例：

MariaDB [mydatabase]> DESCRIBE users;
+------------+--------------+------+-----+-------------------+----------------_
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+----------------_
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | UNI | NULL | |
| email | varchar(100) | YES | UNI | NULL | |
| created_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+----------------_
4 rows in set (0.00 sec)

  • Field: カラム名
  • Type: データ型
  • Null: NULLを許可するかどうか (NOは禁止, YESは許可)
  • Key: キーの種類 (PRIは主キー, UNIはユニークキー, MULはインデックスの一部)
  • Default: デフォルト値
  • Extra: その他の情報（auto_incrementなど）

SHOW CREATE TABLE テーブル名;

より詳細な、テーブルを作成したときのCREATE TABLE文を確認できます。

sql
SHOW CREATE TABLE users;

実行例：

MariaDB [mydatabase]> SHOW CREATE TABLE users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`email` varchar(100) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

ここで出てくるENGINE=InnoDBやDEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ciは、テーブルレベルの設定です。ENGINEは「ストレージエンジン」と呼ばれるもので、データの格納方法やトランザクション対応などを決めます。MariaDBではInnoDBがデフォルトかつ推奨されるストレージエンジンです。

テーブル構造の変更

作成済みのテーブルの構造を変更するには、ALTER TABLE文を使います。カラムの追加、削除、変更、インデックスの追加など、様々な変更が可能です。

カラムの追加 (ADD COLUMN)

例えば、usersテーブルにbio（自己紹介）カラムを追加する場合（最大255文字の可変長文字列、NULL可）：

sql
ALTER TABLE users ADD COLUMN bio VARCHAR(255);

特定のカラムの後に挿入することも可能です。emailカラムの後にbioカラムを追加する場合：

sql
ALTER TABLE users ADD COLUMN bio VARCHAR(255) AFTER email;

カラムの削除 (DROP COLUMN)

bioカラムを削除する場合：

sql
ALTER TABLE users DROP COLUMN bio;

カラムの変更 (MODIFY COLUMN または CHANGE COLUMN)

MODIFY COLUMNは、カラムのデータ型や制約を変更する場合に使います。

例えば、usernameカラムの最大長を50から100に変更し、NULLを許可する場合：

sql
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NULL UNIQUE;

CHANGE COLUMNは、カラム名自体も変更する場合に使います。構文は CHANGE 旧カラム名 新カラム名 データ型 [制約] です。

例えば、usernameカラムの名前をlogin_nameに変更し、型をVARCHAR(80)に、制約をNOT NULL UNIQUEに設定する場合：

sql
ALTER TABLE users CHANGE COLUMN username login_name VARCHAR(80) NOT NULL UNIQUE;

インデックスの追加/削除 (ADD INDEX, DROP INDEX)

特定のカラムでの検索性能を向上させるためにインデックスを追加できます。

例えば、emailカラムにインデックスを追加する場合：

sql
ALTER TABLE users ADD INDEX idx_email (email);

追加したインデックスを削除する場合：

sql
ALTER TABLE users DROP INDEX idx_email;

主キーやユニークキーを設定したカラムには自動的にインデックスが作成されますが、それ以外のカラムで頻繁に検索条件として使うものには手動でインデックスを追加することを検討します。

主キーの追加/削除 (ADD PRIMARY KEY, DROP PRIMARY KEY)

テーブル作成時に主キーを設定し忘れた場合や、主キーを変更する場合に使います。

主キーを追加する場合（ただし、対象カラムにNULLや重複がないことが前提）：

sql
ALTER TABLE users ADD PRIMARY KEY (user_id);

主キーを削除する場合：

sql
ALTER TABLE users DROP PRIMARY KEY;
※ 主キーを削除すると、AUTO_INCREMENTなどの制約も影響を受ける可能性があります。

テーブルの削除

不要になったテーブルを削除するには、DROP TABLE文を使います。この操作も元に戻せません。十分に注意してください。

sql
DROP TABLE users;

成功すると、テーブルが削除されます。

Query OK, 0 rows affected (0.01 sec)

テーブル名の変更

テーブル名を変更するには、RENAME TABLE文を使います。

sql
RENAME TABLE 旧テーブル名 TO 新テーブル名;

例：usersテーブルの名前をapp_usersに変更する場合

sql
RENAME TABLE users TO app_users;

データの操作 (CRUD)

データベースの最も基本的な操作は、データの作成 (Create)、読み込み (Read)、更新 (Update)、削除 (Delete) です。これらは頭文字をとってCRUD操作と呼ばれ、それぞれSQLのINSERT, SELECT, UPDATE, DELETE文に対応します。

ここでは、先ほど作成したusersテーブルを使って、これらの操作方法を学びます。テーブル構造は以下の通りです。

sql
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
※ 説明を分かりやすくするため、ここではVARCHAR(50), VARCHAR(100)として説明を進めます。

データの挿入 (CREATE)

テーブルに新しい行（レコード）を追加するには、INSERT INTO文を使います。

全カラムに値を指定して挿入

カラムを全て指定し、対応する値のリストを挿入します。AUTO_INCREMENTカラムにはNULLを指定するか、カラムリストから省略することで、自動的に値が生成されます。

sql
INSERT INTO users VALUES (NULL, 'alice', '[email protected]', NULL);
この例では、user_idにNULLを指定することでAUTO_INCREMENTに任せています。created_atにNULLを指定していますが、デフォルト値CURRENT_TIMESTAMPが設定されているため、現在の時刻が挿入されます。

特定のカラムを指定して挿入

挿入するカラムを明示的に指定し、そのカラムに対応する値だけを挿入します。指定しなかったカラムには、デフォルト値があればそれが、なければNULLが入ります（ただしNOT NULL制約があるカラムは省略できません）。

sql
INSERT INTO users (username, email) VALUES ('bob', '[email protected]');
この例では、user_idとcreated_atを省略しています。user_idはAUTO_INCREMENTなので自動採番され、created_atはデフォルト値が入ります。

複数行の挿入

一つのINSERT INTO文で複数の行をまとめて挿入することもできます。

sql
INSERT INTO users (username, email) VALUES
('charlie', '[email protected]'),
('david', '[email protected]'),
('eve', '[email protected]');
このようにカンマ区切りで値のリストを追加していきます。

データの取得 (READ)

テーブルからデータを取得するには、SELECT文を使います。SELECT文は最も使用頻度の高いSQL文です。

基本的な構文：

sql
SELECT カラム名 FROM テーブル名 [条件] [並べ替え] [制限];

全てのカラムと全ての行を取得

テーブル内の全てのデータ（全てのカラム、全ての行）を取得するには、カラム名の代わりに*を使います。

sql
SELECT * FROM users;

実行例（上記で挿入したデータがある場合）：

MariaDB [mydatabase]> SELECT * FROM users;
+---------+----------+-------------------+---------------------+
| user_id | username | email | created_at |
+---------+----------+-------------------+---------------------+
| 1 | alice | [email protected] | 2023-10-27 10:00:00 |
| 2 | bob | [email protected] | 2023-10-27 10:01:00 |
| 3 | charlie | [email protected] | 2023-10-27 10:02:00 |
| 4 | david | [email protected] | 2023-10-27 10:03:00 |
| 5 | eve | [email protected] | 2023-10-27 10:04:00 |
+---------+----------+-------------------+---------------------+
5 rows in set (0.00 sec)

特定のカラムを選択

取得したいカラム名だけをカンマ区切りで指定します。

sql
SELECT user_id, username FROM users;

条件を指定 (WHERE句)

取得する行を絞り込むには、WHERE句を使います。WHERE句には条件式を記述します。

  • 等価 =: user_idが3のユーザーを取得
    sql
    SELECT * FROM users WHERE user_id = 3;
  • 不等価 != または <>: user_idが3ではないユーザーを取得
    sql
    SELECT * FROM users WHERE user_id != 3;
  • 比較演算子 >, <, >=, <=: user_idが3より大きいユーザーを取得
    sql
    SELECT * FROM users WHERE user_id > 3;
  • 論理演算子 AND, OR, NOT: user_idが2より大きく、かつusernameが’david’ではないユーザーを取得
    sql
    SELECT * FROM users WHERE user_id > 2 AND username != 'david';
  • IN: usernameが’alice’または’bob’のユーザーを取得
    sql
    SELECT * FROM users WHERE username IN ('alice', 'bob');
    これは WHERE username = 'alice' OR username = 'bob' と同じ意味です。
  • BETWEEN: user_idが2から4の間のユーザーを取得（境界値を含む）
    sql
    SELECT * FROM users WHERE user_id BETWEEN 2 AND 4;
  • LIKE: usernameが’a’で始まるユーザーを取得
    sql
    SELECT * FROM users WHERE username LIKE 'a%';
    %は0文字以上の任意の文字列、_は任意の一文字を表すワイルドカードです。
    例: 'a%' -> ‘alice’, ‘anna’, ‘apply’ など
    例: '%e' -> ‘alice’, ‘charlie’, ‘eve’ など
    例: '_ob' -> ‘bob’
  • IS NULL: emailがNULLのユーザーを取得
    sql
    SELECT * FROM users WHERE email IS NULL;
  • IS NOT NULL: emailがNULLではないユーザーを取得
    sql
    SELECT * FROM users WHERE email IS NOT NULL;

結果の並べ替え (ORDER BY句)

取得した結果を特定カラムの値で並べ替えるには、ORDER BY句を使います。デフォルトは昇順(ASC)です。降順にする場合はDESCを指定します。

“`sql
— user_idの昇順
SELECT * FROM users ORDER BY user_id ASC;

— created_atの降順
SELECT * FROM users ORDER BY created_at DESC;

— 複数のカラムで並べ替え（まずusernameで昇順、次にcreated_atで降順）
SELECT * FROM users ORDER BY username ASC, created_at DESC;
“`

取得行数の制限 (LIMIT句)

取得する行数を制限するには、LIMIT句を使います。例えば、最初の3件だけ取得する場合：

sql
SELECT * FROM users LIMIT 3;

特定の開始位置から特定の件数を取得することも可能です。例えば、4件目から2件取得する場合（最初の数字はスキップする行数、次の数字は取得する行数）：

sql
SELECT * FROM users LIMIT 3, 2; -- 3行スキップして次の2行を取得

LIMIT句は、大量のデータを扱う場合にレスポンスタイムを短縮したり、ページネーションを実装したりするのに役立ちます。

重複行の削除 (DISTINCT)

取得結果から重複する行を除外するには、SELECT DISTINCTを使います。

sql
-- 例：都道府県カラムに重複がある場合
SELECT DISTINCT prefecture FROM addresses;

集計関数 (COUNT, SUM, AVG, MIN, MAX)

テーブル内のデータを集計するための関数です。

  • COUNT(*): テーブル内の全行数をカウント
    sql
    SELECT COUNT(*) FROM users;
  • COUNT(カラム名): 特定のカラムにNULLではない値が入っている行数をカウント
    sql
    SELECT COUNT(email) FROM users;
  • SUM(数値カラム): 数値カラムの合計値を計算
    sql
    -- 例：商品の在庫数を合計
    SELECT SUM(stock) FROM products;
  • AVG(数値カラム): 数値カラムの平均値を計算
    sql
    -- 例：注文の平均金額
    SELECT AVG(amount) FROM orders;
  • MIN(カラム名): カラムの最小値を取得
    sql
    SELECT MIN(created_at) FROM users; -- 最も古い作成日時
  • MAX(カラム名): カラムの最大値を取得
    sql
    SELECT MAX(user_id) FROM users; -- 最大のユーザーID

これらの集計関数は、SELECT句の中で使用します。

結果のグループ化 (GROUP BY句)

集計関数を、特定のカラムの値ごとに適用したい場合にGROUP BY句を使います。

例えば、商品テーブルがあり、カテゴリごとの商品数をカウントする場合：

sql
SELECT category, COUNT(*) FROM products GROUP BY category;

グループに対する条件 (HAVING句)

GROUP BY句でグループ化した結果に対して条件を指定するには、HAVING句を使います。WHERE句はグループ化前の個々の行に条件を適用するのに対し、HAVING句はグループ化後の集計結果に条件を適用します。

例えば、カテゴリごとの商品数のうち、商品数が10個以上のカテゴリだけを取得する場合：

sql
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING product_count >= 10;
※ AS product_countは、COUNT(*)という計算結果に別名（エイリアス）を付けて、HAVING句で使いやすくしています。

テーブルの結合 (JOIN句)

複数のテーブルを関連付けて、それぞれのテーブルからカラムを取得するにはJOIN句を使います。これはリレーショナルデータベースの強力な機能の一つです。

最も一般的な結合はINNER JOINです。これは、結合条件を満たす両方のテーブルに存在する行だけを取得します。

例：usersテーブルとordersテーブルがあり、ordersテーブルにはuser_id（外部キーとしてusersテーブルのuser_idを参照）とorder_amountカラムがあるとします。各ユーザーの注文合計金額を取得する場合：

sql
SELECT u.username, SUM(o.order_amount) AS total_amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
GROUP BY u.username;
* FROM users u: usersテーブルを使い、uというエイリアスを付けています。
* INNER JOIN orders o ON u.user_id = o.user_id: ordersテーブルをoというエイリアスで結合します。結合条件はusersテーブルのuser_idカラムとordersテーブルのuser_idカラムが一致することです。
* SELECT u.username, SUM(o.order_amount): usersテーブルのusernameと、ordersテーブルのorder_amountの合計を取得します。
* GROUP BY u.username: usernameごとに結果をグループ化して集計します。

他にも、LEFT JOIN（左側のテーブルの全ての行と、結合条件を満たす右側のテーブルの行を取得）、RIGHT JOIN（右側のテーブルの全ての行と、結合条件を満たす左側のテーブルの行を取得）、FULL OUTER JOIN（MariaDBではUNIONを使って実現することが多い）などがあります。

サブクエリ

別のSQL文の結果を、別のSQL文の中で利用することをサブクエリ（副問い合わせ）と呼びます。WHERE句やFROM句、SELECT句など、様々な場所で利用できます。

例：平均注文金額より高い注文だけを取得する場合

sql
SELECT * FROM orders WHERE order_amount > (SELECT AVG(order_amount) FROM orders);
カッコ内のSELECT AVG(order_amount) FROM ordersがサブクエリです。まずこのサブクエリが実行され、平均注文金額が計算され、その結果を使って外側のSELECT文のWHERE条件が評価されます。

データの更新 (UPDATE)

既存のテーブルのデータを変更するには、UPDATE文を使います。

基本的な構文：

sql
UPDATE テーブル名 SET カラム名1 = 値1, カラム名2 = 値2, ... [WHERE 条件];

WHERE句を付け忘れると、テーブルの全行が更新されてしまうので十分に注意が必要です。

例：user_idが1のユーザーのemailを更新する場合

sql
UPDATE users SET email = '[email protected]' WHERE user_id = 1;

例：複数のカラムを更新する場合

sql
UPDATE users SET email = '[email protected]', username = 'bobby' WHERE user_id = 2;

データの削除 (DELETE)

既存のテーブルのデータを削除するには、DELETE FROM文を使います。

基本的な構文：

sql
DELETE FROM テーブル名 [WHERE 条件];

WHERE句を付け忘れると、テーブルの全行が削除されてしまうので十分に注意が必要です。

例：user_idが5のユーザーを削除する場合

sql
DELETE FROM users WHERE user_id = 5;

全データの削除 (TRUNCATE TABLEとの違い)

テーブルの全データを削除したい場合、DELETE FROM テーブル名; と TRUNCATE TABLE テーブル名; という二つの方法があります。

  • DELETE FROM table_name;: 行を一つずつ削除します。トランザクション内で実行でき、ロールバック可能です。AUTO_INCREMENTの値はリセットされません（次の新しい値は続きから始まります）。
  • TRUNCATE TABLE table_name;: テーブル全体を再作成することでデータを削除します。DELETEよりも高速ですが、トランザクション内で実行してもロールバックできません（一部のストレージエンジンを除く）。AUTO_INCREMENTの値は通常リセットされます。

全てのデータを高速に削除し、AUTO_INCREMENTの値をリセットしたい場合はTRUNCATE TABLEが適しています。トランザクション内で実行したい場合や、AUTO_INCREMENTの値を保持したい場合はDELETEを使用します。

ユーザーと権限管理

データベースシステムでは、誰がどのような操作（データの読み込み、書き込み、テーブル作成など）を行えるかを制御する必要があります。これはセキュリティ上非常に重要です。MariaDBでは、ユーザーアカウントを作成し、それぞれのユーザーに特定のデータベースやテーブルに対する権限を付与することでこれを実現します。

ユーザー一覧の表示

MariaDBのユーザー情報は、システムデータベースであるmysqlデータベースのuserテーブルに格納されています。現在のユーザー一覧を確認するには、以下のSQL文を使います。

sql
SELECT user, host FROM mysql.user;

userはユーザー名、hostはそのユーザーがどのホスト（接続元IPアドレスなど）から接続できるかを示します。%は全てのホストからの接続を許可します。

実行例：

MariaDB [(none)]> SELECT user, host FROM mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | localhost |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost | -- 匿名ユーザー（削除推奨）
+------+-----------+

ユーザーの作成

新しいユーザーを作成するには、CREATE USER文を使います。ユーザー名と、接続を許可するホストを指定します。パスワード認証を使う場合は、IDENTIFIED BY句でパスワードを指定します。

sql
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'secure_password';

  • 'new_user': 作成するユーザー名です。
  • 'localhost': このユーザーが接続できるホストです。'%'を指定すると、全てのホストからの接続を許可します（セキュリティリスクがあるため注意が必要です）。特定のIPアドレスやサブネットを指定することも可能です。
  • 'secure_password': ユーザーのパスワードです。安全なパスワードを設定してください。

パスワード認証ではなく、クライアントプログラムが利用するOSユーザー名と同じ場合にパスワードなしで認証する仕組み（ソケット認証、GSSAPI認証など）を利用することも可能ですが、ここでは一般的なパスワード認証を説明します。

ユーザーへの権限付与

作成したユーザーは、そのままではどのデータベースにもアクセスできません。アクセスや操作を許可するには、GRANT文を使って権限を付与する必要があります。

基本的な構文：

sql
GRANT 権限 [, 権限...] ON データベース名.テーブル名 TO 'ユーザー名'@'ホスト名';

  • 権限: 付与する操作権限を指定します。代表的なものには以下があります。

    • SELECT: データの読み込み
    • INSERT: データの挿入
    • UPDATE: データの更新
    • DELETE: データの削除
    • CREATE: テーブルやデータベースなどの作成
    • DROP: テーブルやデータベースなどの削除
    • ALTER: テーブル構造の変更
    • INDEX: インデックスの作成・削除
    • ALL PRIVILEGES: そのレベルでの全ての権限

  • データベース名.テーブル名: 権限を付与する対象を指定します。

    • データベース名.*: 特定のデータベース内の全てのテーブル
    • *.*: 全てのデータベースの全てのテーブル

例：new_userユーザーに、mydatabaseデータベース内の全てのテーブルに対して、データの読み込み、挿入、更新、削除の権限を付与する場合

sql
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'new_user'@'localhost';

付与した権限を、さらに他のユーザーに付与することを許可する場合は、WITH GRANT OPTIONを付けます。

sql
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;
この権限は非常に強力なので、信頼できるユーザーにのみ付与してください。

付与された権限の確認

特定のユーザーに付与されている権限を確認するには、SHOW GRANTS文を使います。

sql
SHOW GRANTS FOR 'new_user'@'localhost';

実行例：

MariaDB [(none)]> SHOW GRANTS FOR 'new_user'@'localhost';
+-----------------------------------------------------------------------+
| Grants for new_user@localhost |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'new_user'@'localhost' | -- USAGEは接続権限のみ
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mydatabase`.* TO 'new_user'@'localhost' |
+-----------------------------------------------------------------------+

最初に表示されるGRANT USAGE ON *.*は、データベースに接続できる基本的な権限です。

権限の剥奪

ユーザーから特定の権限を剥奪するには、REVOKE文を使います。構文はGRANT文と似ています。

sql
REVOKE 権限 [, 権限...] ON データベース名.テーブル名 FROM 'ユーザー名'@'ホスト名';

例：new_userユーザーから、mydatabaseデータベースに対する更新権限を剥奪する場合

sql
REVOKE UPDATE ON mydatabase.* FROM 'new_user'@'localhost';

ユーザーの削除

不要になったユーザーを削除するには、DROP USER文を使います。

sql
DROP USER 'new_user'@'localhost';

権限の反映 (FLUSH PRIVILEGES)

GRANTやREVOKEで権限を変更した場合、通常は変更がすぐに反映されます。しかし、古いバージョンのMariaDBや、特定の状況下では、権限キャッシュが更新されないことがあります。確実に変更を反映させたい場合は、FLUSH PRIVILEGES文を実行します。

sql
FLUSH PRIVILEGES;
これはシステムコマンドなので、通常はroot権限で実行します。

ユーザーと権限管理は、データベースシステムを安全に運用するために非常に重要です。各ユーザーには必要最低限の権限だけを与えるようにしましょう。

インデックス

大量のデータが格納されたテーブルから特定の条件を満たすデータを検索する際、効率が非常に重要になります。テーブルに適切な「インデックス」を設定することで、データ検索の速度を大幅に向上させることができます。

インデックスとは何か？ なぜ必要か？

インデックスは、データベーステーブルのカラムに対して作成されるデータ構造で、書籍の索引のようなものです。索引があれば、書籍の内容を最初から順に読むことなく、探したい情報がどのページにあるかを素早く見つけられます。データベースのインデックスも同様に、特定のカラムの値を使って、目的のデータがテーブル内のどこにあるかを素早く見つけるための仕組みです。

インデックスがない場合、データベースは検索条件に一致する行を見つけるために、テーブルの全行を最初から最後まで順番に調べる必要があり、データの量が増えるにつれて検索に時間がかかるようになります（フルテーブルスキャン）。インデックスがあると、データベースはインデックス構造を利用して目的のデータを直接的に、または非常に効率的に探し出すことができます。

インデックスの種類

MariaDBでは、主に以下の種類のインデックスが使われます。

  • B-treeインデックス (INDEX / KEY): 最も一般的でデフォルトのインデックスタイプです。等価検索 (=)、範囲検索 (<, >, BETWEEN)、およびLIKE検索で先頭一致 (LIKE 'prefix%') に効果的です。主キーやユニークキーも内部的にはB-treeインデックスとして実装されます。
  • FULLTEXTインデックス: テキストデータのキーワード検索（全文検索）に特化したインデックスです。ブログの記事本文など、長いテキストカラムでの検索に使われます。
  • SPATIALインデックス: 地理情報（空間データ）の検索に特化したインデックスです。位置情報を使ったアプリケーションなどで使われます。
  • Hashインデックス: B-treeインデックスよりも等価検索（=）が高速な場合がありますが、範囲検索には使えません。Memoryストレージエンジンなどで利用可能です。

初心者の方がまず理解すべきはB-treeインデックスです。

インデックスの作成 (CREATE INDEX)

テーブル作成後にインデックスを追加するには、CREATE INDEX文を使います。

基本的な構文：

sql
CREATE INDEX インデックス名 ON テーブル名 (カラム名 [, カラム名...]);

インデックス名は任意ですが、どのテーブルのどのカラムに対するインデックスかが分かりやすい名前を付けることが推奨されます（例: idx_テーブル名_カラム名）。複数カラムを組み合わせた複合インデックスを作成することも可能です。

例：usersテーブルのemailカラムにidx_users_emailという名前のインデックスを作成する場合

sql
CREATE INDEX idx_users_email ON users (email);

例：ordersテーブルにcustomer_idとorder_dateの複合インデックスを作成する場合

sql
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
この複合インデックスは、customer_idでの検索、customer_idとorder_dateを組み合わせた検索、そしてcustomer_idのみでの検索に効果があります（ただし、order_dateのみでの検索には効果がありません）。

テーブル作成時にカラム定義の一部としてインデックスを作成することも可能です。

sql
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10, 2),
INDEX idx_products_category (category) -- テーブル作成時にインデックスを作成
);

インデックスの確認 (SHOW INDEX FROM)

テーブルに設定されているインデックスを確認するには、SHOW INDEX FROM文を使います。

sql
SHOW INDEX FROM users;

実行例：

MariaDB [mydatabase]> SHOW INDEX FROM users;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0 | PRIMARY | 1 | user_id | A | 5 | NULL | NULL | | BTREE | | |
| users | 0 | username | 1 | username | A | 5 | NULL | NULL | NO | BTREE | | |
| users | 0 | email | 1 | email | A | 5 | NULL | NULL | YES | BTREE | | |
| users | 1 | idx_users_email | 1 | email | A | 5 | NULL | NULL | YES | BTREE | | |
+---------+------------+--------------+--------------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
Key_nameがインデックス名です。PRIMARYは主キーのインデックス、usernameとemailはUNIQUE制約によって自動作成されたインデックス、idx_users_emailは手動で作成したインデックスです。Index_typeはインデックスの種類（BTREEなど）を示します。Non_uniqueが0の場合はユニークインデックス（主キー、ユニークキー）、1の場合は非ユニークインデックスです。

インデックスの削除 (DROP INDEX)

不要になったインデックスを削除するには、DROP INDEX文を使います。

sql
DROP INDEX インデックス名 ON テーブル名;

例：usersテーブルのidx_users_emailインデックスを削除する場合

sql
DROP INDEX idx_users_email ON users;
またはALTER TABLE文を使う方法もあります。

sql
ALTER TABLE users DROP INDEX idx_users_email;

インデックスの効果と注意点

効果:

  • SELECT文でのデータ検索（特にWHERE句でインデックスカラムを使う場合）が高速化されます。
  • ORDER BYやGROUP BYの処理が高速化される場合があります。
  • テーブル結合の性能が向上する場合があります。

注意点:

  • インデックスはストレージ容量を消費します。
  • データの挿入 (INSERT)、更新 (UPDATE)、削除 (DELETE) の際に、テーブルデータだけでなくインデックスも更新する必要があるため、これらの操作の性能が低下する可能性があります。特にインデックスが多いテーブルでは顕著です。
  • インデックスは、データベースがクエリを実行する際に「利用可能であれば使う」ものであり、常に利用されるとは限りません。どのようなインデックスを使うかは、データベースシステムが「クエリオプティマイザ」という機能で判断します。
  • 全てのカラムにインデックスを付ければ速くなる、というわけではありません。むしろ更新性能の低下や容量消費が大きくなる可能性があります。頻繁に検索条件として使われるカラムや、結合条件となるカラムにインデックスを付けるのが効果的です。

インデックスがどのように使われるか、またクエリの実行計画を確認するには、EXPLAIN文をSQL文の前に付けます。

sql
EXPLAIN SELECT * FROM users WHERE username = 'alice';
この結果を見ることで、どのインデックスが使われたか、フルテーブルスキャンになったかなどを確認でき、クエリのパフォーマンスチューニングに役立てることができます。

トランザクション

データベースを扱う上で、「トランザクション」という概念は非常に重要です。特に、複数の操作をまとめて一つの処理として扱いたい場合に必要になります。

トランザクションとは何か？ (ACID特性)

トランザクションとは、データベースに対する一連の操作（複数のSQL文など）を、一つの論理的な単位としてまとめたものです。この一連の操作は、全て成功するか、全て失敗して元に戻されるかのどちらかになります。途中でエラーが発生したり、システムが停止したりした場合、トランザクションに含まれる全ての変更はなかったことになり、データベースはトランザクション開始前の状態に戻ります。

トランザクション管理システムが満たすべき性質として、ACID特性が挙げられます。

  • Atomicity (原子性): トランザクションに含まれる全ての操作は、不可分な単位として扱われます。全てが実行されるか、全く実行されないかのどちらかです。
  • Consistency (一貫性): トランザクションの開始前と終了後で、データベースの整合性が保たれている必要があります。例えば、外部キー制約などの定義されたルールに違反するような状態にはなりません。
  • Isolation (分離性): 複数のトランザクションが同時に実行された場合でも、それぞれのトランザクションが他のトランザクションの影響を受けないように見えます。互いに干渉せず、順番に実行されたかのように処理されます。
  • Durability (永続性): 一度正常に完了（コミット）したトランザクションによる変更は、システム障害が発生しても失われることはありません。データは永続的に保存されます。

例えば、銀行の口座間送金処理を考えてみましょう。「Aさんの口座から1万円減らす」という操作と、「Bさんの口座に1万円増やす」という操作は、両方成功しないと問題が発生します。片方だけ成功してシステムが停止した場合、合計金額が変わってしまいます。トランザクションを使うと、これら二つの操作を一つのトランザクションとして実行し、どちらかでも失敗すれば両方の操作を取り消し、データベースを送金前の状態に戻すことができます。

トランザクションの開始、確定、破棄

MariaDBでトランザクションを明示的に制御するには、以下のコマンドを使います。ただし、後述するストレージエンジンがトランザクションに対応している必要があります（InnoDBは対応）。

  • トランザクションの開始 (START TRANSACTION;):
    トランザクションを開始します。このコマンド以降に実行されるSQL文は、次回のCOMMITまたはROLLBACKまで一つのトランザクションとして扱われます。
    sql
    START TRANSACTION;
  • トランザクションの確定 (COMMIT;):
    トランザクション内の全ての操作を確定し、データベースに永続的に反映させます。
    sql
    COMMIT;
  • トランザクションの破棄 (ROLLBACK;):
    トランザクション開始以降に行われた全ての操作を取り消し、データベースをトランザクション開始前の状態に戻します。
    sql
    ROLLBACK;

例：ユーザーのパスワードとメールアドレスを同時に更新するトランザクション

“`sql
START TRANSACTION;

— パスワードを更新
UPDATE users SET password_hash = ‘new_hashed_password’ WHERE user_id = 1;

— メールアドレスを更新
UPDATE users SET email = ‘[email protected]’ WHERE user_id = 1;

— 両方の更新が成功したら確定
COMMIT;

— もしどちらかの更新でエラーが発生したら、以下のコマンドでロールバック
— ROLLBACK;
“`

ストレージエンジン（InnoDBとMyISAMの違い）

MariaDBは、テーブルごとのデータの格納方法や機能（トランザクション対応、ロックレベルなど）を決める「ストレージエンジン」を選択できます。MariaDBで最も一般的で推奨されるストレージエンジンはInnoDBです。

  • InnoDB:

    • トランザクション対応: ACID特性を完全にサポートします。
    • 行レベルロック: データ更新時に、変更する行だけをロックするため、複数ユーザーからの同時アクセス性能に優れます。
    • 外部キー制約: 外部キーによる参照整合性をサポートします。
    • クラッシュリカバリ: システムがクラッシュした場合でも、トランザクションログを利用してデータを回復できます。

  • MyISAM:

    • トランザクション非対応: トランザクションやロールバックはできません。
    • テーブルレベルロック: データ更新時にテーブル全体をロックするため、同時書き込み性能は劣ります。
    • 高速な読み込み: 比較的簡単な構造のため、読み込み（SELECT）が高速な場合があります。
    • 全文検索インデックス: MyISAMは古くから全文検索インデックスをサポートしていましたが、InnoDBも最近のバージョンでは全文検索インデックスに対応しています。

現代のほとんどのアプリケーションでは、データの整合性や並行処理の重要性から、トランザクション対応のInnoDBが推奨されます。テーブル作成時のENGINE=InnoDBという指定は、そのテーブルをInnoDBエンジンで作成することを意味します。デフォルト設定でInnoDBが使われることが多いです。

バックアップとリカバリ

データベースは重要な情報資産です。ハードウェア障害、ソフトウェアの不具合、人為的なミスなど、様々な原因でデータが失われる可能性があります。そのため、定期的なバックアップと、必要な場合のデータ復旧（リカバリ）の仕組みを整えておくことは非常に重要です。

なぜバックアップが必要か

  • データ損失からの保護: 予期せぬ障害が発生した場合に、バックアップデータからデータベースを元の状態に戻すことができます。
  • アーカイブ: 過去のデータを長期保存しておき、必要に応じて参照や分析に利用できます。
  • 開発/テスト環境へのデータ複製: 本番環境のデータを複製して、開発やテストに利用できます。
  • 移行: 別のサーバーやシステムにデータベースを移行する際に利用できます。

mysqldumpコマンドを使ったバックアップ

MariaDBには、データベース全体や特定のテーブルのデータをSQL形式でファイルに出力するmysqldumpというコマンドラインツールが付属しています（MySQLから引き継がれています）。このコマンドはMariaDBサーバーが稼働しているマシン上で実行するのが一般的です。

基本的な構文：

bash
mysqldump [オプション] -u ユーザー名 -p データベース名 > バックアップファイル名.sql

例：mydatabaseデータベース全体をmydatabase_backup.sqlというファイルにバックアップする場合

bash
mysqldump -u root -p mydatabase > mydatabase_backup.sql

このコマンドを実行するとパスワード入力が求められます。バックアップファイルは、データベースの構造（CREATE TABLE文）とデータ（INSERT文）を含むSQLスクリプトファイルになります。

その他の便利なオプション:

  • 全てのデータベースをバックアップ: --all-databasesオプションを使います。
    bash
    mysqldump -u root -p --all-databases > all_databases_backup.sql
  • 特定のテーブルだけをバックアップ: データベース名の後にテーブル名をスペース区切りで指定します。
    bash
    mysqldump -u root -p mydatabase users orders > mydatabase_users_orders_backup.sql
  • テーブル構造だけをバックアップ（データは含めない）: --no-dataオプションを使います。
    bash
    mysqldump -u root -p --no-data mydatabase > mydatabase_structure.sql
  • データだけをバックアップ（構造は含めない）: --no-create-infoオプションを使います。
    bash
    mysqldump -u root -p --no-create-info mydatabase users > mydatabase_users_data.sql
  • 圧縮してバックアップ: 標準出力をgzipなどの圧縮コマンドにパイプで渡します。
    bash
    mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

バックアップファイルからのリストア

mysqldumpで作成したSQLバックアップファイルからデータベースを復旧（リストア）するには、MariaDBクライアント (mysql) コマンドを使用します。

基本的な構文：

bash
mysql [オプション] -u ユーザー名 -p [データベース名] < バックアップファイル名.sql

リストア先のデータベースが存在しない場合は、先にCREATE DATABASEで作成しておく必要があります。データベース名を指定しない場合は、バックアップファイルにUSE database_name;が含まれていればそのデータベースにリストアされます。

例：mydatabase_backup.sqlファイルをmydatabaseデータベースにリストアする場合

  1. まず、対象のデータベースが存在することを確認します。存在しない場合は作成します。
    sql
    CREATE DATABASE mydatabase; -- もし削除してしまった場合など
  2. コマンドラインでリストアを実行します。
    bash
    mysql -u root -p mydatabase < mydatabase_backup.sql
    パスワード入力が求められます。

圧縮されたバックアップファイル（.gzなど）の場合は、まずファイルを解凍してからmysqlコマンドに渡します。

bash
gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

バックアップとリカバリは、データベース運用において最も基本的ながら非常に重要なタスクです。本番運用を行う場合は、定期的な自動バックアップを設定し、実際にリカバリができるかテストしておくことを強く推奨します。

まとめ

このガイドでは、MariaDBの初心者向けに、データベースの基本概念から、MariaDBのインストール、コマンドラインクライアントの使い方、そしてデータベースやテーブルの作成・管理、データのCRUD操作、ユーザーと権限管理、インデックス、トランザクション、バックアップとリカバリといった基本的な操作について、約5000語の詳細な説明を行いました。

データベースとは何か、リレーショナルデータベースの基本的な構造、MariaDBがどのように誕生し、どのような特徴を持つのかを理解しました。お使いのOSにMariaDBサーバーをインストールし、mysql_secure_installationで初期設定を行う手順を学びました。

コマンドラインクライアントを使ってMariaDBに接続し、SHOW DATABASESやUSEといった基本的なコマンドを実行しました。CREATE DATABASEやDROP DATABASEでデータベースを作成・削除する方法、文字コードと照合順序の設定の重要性も学びました。

最も重要な操作であるテーブルの作成 (CREATE TABLE)、テーブル定義の確認 (DESCRIBE, SHOW CREATE TABLE)、テーブル構造の変更 (ALTER TABLE)、テーブルの削除 (DROP TABLE) について、具体的なデータ型や制約（PRIMARY KEY, UNIQUE, NOT NULL, AUTO_INCREMENT）と共に詳しく解説しました。

さらに、データの操作として、挿入 (INSERT)、取得 (SELECT – WHERE, ORDER BY, LIMIT, GROUP BY, HAVING, JOINなどの様々な句を含む)、更新 (UPDATE)、削除 (DELETE) のCRUD操作を、多くの例を挙げて説明しました。

セキュリティに関わるユーザーと権限管理についても、ユーザーの作成 (CREATE USER)、権限の付与 (GRANT)、確認 (SHOW GRANTS), 剥奪 (REVOKE), 削除 (DROP USER), そして権限の反映 (FLUSH PRIVILEGES) の手順を解説しました。

検索性能向上のためのインデックスの役割と、インデックスの作成・確認・削除方法を学びました。また、複数の操作をまとめて一貫性を保つためのトランザクションの概念と、ACID特性、そしてSTART TRANSACTION, COMMIT, ROLLBACKの使い方、トランザクション対応のストレージエンジン（特にInnoDB）の重要性についても触れました。

最後に、万が一の事態に備えるためのバックアップとリカバリの基本として、mysqldumpコマンドを使ったバックアップ方法と、mysqlコマンドを使ったリストア方法を説明しました。

次へのステップ

この記事でMariaDBの基本的な操作は一通り網羅しましたが、MariaDBにはまだまだ多くの機能や高度な概念があります。この次に学ぶべきこととして、以下のようなテーマが挙げられます。

  • より高度なSQL: ウィンドウ関数、共通テーブル式(CTE)、ストアドプロシージャ、ストアドファンクション、トリガーなど。
  • ストレージエンジン: InnoDBのより詳細な設定、他のストレージエンジンについて。
  • GUIツールの利用: DBeaver, HeidiSQL, phpMyAdmin, AdminerなどのGUIツールを使うと、直感的な操作でデータベースを管理できます。学習効率を高めるためにも利用を検討すると良いでしょう。
  • パフォーマンスチューニング: EXPLAINを使ったクエリ分析、インデックス設計の最適化、サーバー設定の調整など。
  • レプリケーションとクラスタリング: データベースの可用性やスケーラビリティを高める技術。
  • プログラミング言語からの利用: PHP, Python, Java, Node.jsなど、様々なプログラミング言語からMariaDBに接続し、操作する方法。

また、MariaDBの公式ドキュメント（https://mariadb.com/kb/en/documentation/）は非常に詳細で網羅的な情報源です。困ったときやさらに深く学びたいときには、ぜひ参照してください。活発なコミュニティも存在するので、フォーラムなどで質問することも可能です。

この記事が、あなたがMariaDBを使い始め、データベース活用の基礎を築くための一助となれば幸いです。焦らず、一歩ずつ理解を深めていきましょう。データベースの世界は奥深く、学ぶほどに面白さが増していきます。あなたのデータベース学習の旅が成功することを応援しています！

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール