仮想開発環境の構築トップページ


MariaDBの 基本操作と Mroonga による日本語全文検索

MariaDB (MySQL) で Mroonga を使った日本語の全文検索を行ってみよう.CentOS で通常の手順で MariaDB (あるいは MySQL)をインストールしたとき,日本語の全文検索は行なえません.ここの手順で Mroonga を設定すると日本語であっても全文検索を高速に行えるようになります.インストールと MySQL パスワードの設定までができた状態を前提にします.なお,SQL のコマンドは大文字で表示していますが,大半は小文字で入力しても動作します.

目次

  1. MySQLの利用方法
    1. ログイン
    2. プラグインの確認
    3. データベースの作成
    4. データベースの指定
    5. テーブルの作成
    6. テーブル定義情報の確認
    7. データの投入
    8. データの検索
    9. ログアウト
  2. ユーザの作成と利用
    1. データベースとユーザの作成
    2. 作成したユーザで操作する
    3. ユーザの削除
  3. テーブルやデータベースを削除する

MySQLの利用方法

ログイン

MySQL にログインするには mysql コマンドを使います.まずは管理者 (root) でログインします.-p オプションをつけることで,コマンド入力後にパスワードが尋ねられます.

[vagrant@centos8 centos8ansible]$ mysql -u root -p ⏎
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.4.17-MariaDB MariaDB Server

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 のプラグインを一覧で表示し,Mroonga がインストールされていることを確認する.

MariaDB [(none)]> SHOW PLUGINS; ⏎
+-------------------------------+----------+--------------------+------------------------+---------+
| Name                          | Status   | Type               | Library                | License |
+-------------------------------+----------+--------------------+------------------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL                   | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL                   | GPL     |
| wsrep                         | ACTIVE   | REPLICATION        | NULL                   | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| CLIENT_STATISTICS             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INDEX_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| TABLE_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| USER_STATISTICS               | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| SQL_SEQUENCE                  | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_VIRTUAL            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_MUTEXES                | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_SEMAPHORE_WAITS    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE   | INFORMATION SCHEMA | NULL                   | BSD     |
| INNODB_TABLESPACES_SCRUBBING  | ACTIVE   | INFORMATION SCHEMA | NULL                   | BSD     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| SEQUENCE                      | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| unix_socket                   | ACTIVE   | AUTHENTICATION     | NULL                   | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL                   | GPL     |
| user_variables                | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| Mroonga                       | ACTIVE   | STORAGE ENGINE     | ha_mroonga_official.so | GPL     |
+-------------------------------+----------+--------------------+------------------------+---------+
55 rows in set (0.001 sec)

データベースの作成

データベースの一覧を表示する.

MariaDB [(none)]> SHOW DATABASES; ⏎
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.007 sec)

新しいデータベースを作成する.

MariaDB [(none)]> CREATE DATABASE mroonga_test; ⏎
Query OK, 1 row affected (0.000 sec)

データベースの一覧を表示すると,今作成したデータベースが確認できる.

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

データベースの指定

利用するデータベースを指定する.

MariaDB [(none)]> USE mroonga_test; ⏎
Database changed

テーブルの作成

テーブルの一覧を表示する.まだテーブルはありません.

MariaDB [mroonga_test]> SHOW TABLES; ⏎
Empty set (0.000 sec)

テーブルを作成する.このとき,日本語全文検索を利用するためには Engine=Mroonga を指定する.

MariaDB [mroonga_test]> CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    body VARCHAR(255),
    FULLTEXT INDEX (body)
  ) Engine=Mroonga DEFAULT CHARSET utf8; ⏎
Query OK, 0 rows affected (0.101 sec)

テーブルの一覧を表示する.

MariaDB [mroonga_test]> SHOW TABLES; ⏎
+------------------------+
| Tables_in_mroonga_test |
+------------------------+
| comments               |
+------------------------+
1 row in set (0.000 sec)

テーブル定義情報の確認

テーブルの定義を確認する.

MariaDB [mroonga_test]> DESC comments; ⏎
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| body  | varchar(255) | YES  | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.001 sec)

テーブル定義の詳細情報を確認する.このとき body カラムにインデックスが設定されていることに注意する.

MariaDB [mroonga_test]> SHOW CREATE TABLE comments; ⏎
+----------+------------------------------------------------------+
| Table    | Create Table                                         |
+----------+------------------------------------------------------+
| comments | CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `body` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `body` (`body`)
) ENGINE=Mroonga DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------+
1 row in set (0.001 sec)

データの投入

データを投入する.

MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("暗号とデジタル署名"); ⏎
Query OK, 1 row affected (0.005 sec)

MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("換字式暗号と転置式暗号"); ⏎
Query OK, 1 row affected (0.001 sec)

MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("共通鍵暗号方式"); ⏎
Query OK, 1 row affected (0.000 sec)

MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("公開鍵暗号方式"); ⏎
Query OK, 1 row affected (0.000 sec)

MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("ハイブリッド暗号方式"); ⏎
Query OK, 1 row affected (0.000 sec)

MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("ハッシュ関数"); ⏎
Query OK, 1 row affected (0.000 sec)

MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("デジタル署名"); ⏎
Query OK, 1 row affected (0.000 sec)

MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("認証局"); ⏎
Query OK, 1 row affected (0.001 sec)

データの検索

データをすべて表示する.

MariaDB [mroonga_test]> SELECT * FROM comments; ⏎
+----+-----------------------------------+
| id | body                              |
+----+-----------------------------------+
|  1 | 暗号とデジタル署名                |
|  2 | 換字式暗号と転置式暗号            |
|  3 | 共通鍵暗号方式                    |
|  4 | 公開鍵暗号方式                    |
|  5 | ハイブリッド暗号方式              |
|  6 | ハッシュ関数                      |
|  7 | デジタル署名                      |
|  8 | 認証局                            |
+----+-----------------------------------+
8 rows in set (0.000 sec)

インデックスを使った検索を行ってみる.まずは「署名」というキーワードが含まれるレコードを抽出すると,うまく検索できている.

MariaDB [mroonga_test]> SELECT * FROM comments WHERE MATCH(body) AGAINST("署名"); ⏎
+----+-----------------------------+
| id | body                        |
+----+-----------------------------+
|  1 | 暗号とデジタル署名          |
|  7 | デジタル署名                |
+----+-----------------------------+
2 rows in set (0.001 sec)

別のキーワードでも検索してみる.キーワード「暗号」もうまく検索できました.

MariaDB [mroonga_test]> SELECT * FROM comments WHERE MATCH(body) AGAINST("暗号"); ⏎
+----+-----------------------------------+
| id | body                              |
+----+-----------------------------------+
|  2 | 換字式暗号と転置式暗号            |
|  1 | 暗号とデジタル署名                |
|  5 | ハイブリッド暗号方式              |
|  4 | 公開鍵暗号方式                    |
|  3 | 共通鍵暗号方式                    |
+----+-----------------------------------+
5 rows in set (0.014 sec)

さらに「暗号方式」で検索すると,「暗号」が含まれるが「方式」を含まないレコードも検索されてしまいました.

MariaDB [mroonga_test]> SELECT * FROM comments WHERE MATCH(body) AGAINST("暗号方式"); ⏎
+----+-----------------------------------+
| id | body                              |
+----+-----------------------------------+
|  2 | 換字式暗号と転置式暗号            |
|  1 | 暗号とデジタル署名                |
|  5 | ハイブリッド暗号方式              |
|  4 | 公開鍵暗号方式                    |
|  3 | 共通鍵暗号方式                    |
+----+-----------------------------------+
5 rows in set (0.001 sec)

Mroonga では「IN BOOLEAN MODE」というオプションをつけて検索すると,うまく検索できます.

MariaDB [mroonga_test]> SELECT * FROM comments WHERE MATCH(body) AGAINST("暗号方式" IN BOOLEAN MODE); ⏎
+----+--------------------------------+
| id | body                           |
+----+--------------------------------+
|  3 | 共通鍵暗号方式                 |
|  5 | ハイブリッド暗号方式           |
|  4 | 公開鍵暗号方式                 |
+----+--------------------------------+
3 rows in set (0.001 sec)

なお,インデックスを使わずに LIKE を使った検索も可能ですが,この方法は検索性能(速度)の観点からおすすめできません.

MariaDB [mroonga_test]> SELECT * FROM comments WHERE body LIKE "%暗号%"; ⏎
+----+-----------------------------------+
| id | body                              |
+----+-----------------------------------+
|  1 | 暗号とデジタル署名                |
|  2 | 換字式暗号と転置式暗号            |
|  3 | 共通鍵暗号方式                    |
|  4 | 公開鍵暗号方式                    |
|  5 | ハイブリッド暗号方式              |
+----+-----------------------------------+
5 rows in set (0.000 sec)

ログアウト

データベースからログアウトします.

MariaDB [mroonga_test]> EXIT ⏎
Bye
[vagrant@centos8 centos8ansible]$

ユーザの作成と利用

管理者 (root) ではすべてのデータに対して閲覧・操作が可能になるので,一般的にはユーザを作成して,特定のデータベースに権限を与えることが行われる.ここでは,myappDB というデータベースを作成し,このデータベースを操作できる myapp_user というユーザを作成する例を示す.

データベースとユーザの作成

まず,管理者 (root) でログインする.

[vagrant@centos8 centos8ansible]$ mysql -u root -p ⏎
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 10.4.17-MariaDB MariaDB Server

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)]> SHOW DATABASE; ⏎
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mroonga_test       |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

データベースを作成する.

MariaDB [(none)]> CREATE DATABASE myappDB; ⏎
Query OK, 1 row affected (0.000 sec)

データベースの一覧を確認すると,いま作成した myappDB が見える.

MariaDB [(none)]> SHOW DATABASES; ⏎
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mroonga_test       |
| myappDB            |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)

mysql というデータベースにユーザ情報が登録されているので,ユーザの一覧を確認する.

MariaDB [(none)]> SELECT user, host FROM mysql.user; ⏎
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
3 rows in set (0.007 sec)

ユーザ myapp_user を作成し,データベース myappDB のすべてのテーブルにすべての権限を付与する.なお,パスワードは「hogehogehoge」にする(もちろん適宜変更してください).

MariaDB [(none)]> GRANT ALL ON myappDB.* to myapp_user@localhost identified by 'hogehogehoge'; ⏎
Query OK, 0 rows affected (0.001 sec)

ユーザが登録できたことを確認する.

MariaDB [(none)]> SELECT user, host FROM mysql.user; ⏎
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| myapp_user  | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
4 rows in set (0.001 sec)

一旦ログアウトする.

MariaDB [(none)]> EXIT ⏎
Bye
[vagrant@centos8 centos8ansible]$

作成したユーザで操作する

まず,mysql にログインする.このときのユーザ名は先程作成した myapp_user にする.

[vagrant@centos8 centos8ansible]$ mysql -u myapp_user -p ⏎
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 10.4.17-MariaDB MariaDB Server

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)]> SHOW DATABASES; ⏎
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myappDB            |
+--------------------+
2 rows in set (0.000 sec)

使用するデータベースを指定する.権限を持つデータベースに対しては様々な操作が可能です.

MariaDB [(none)]> USE myappDB; ⏎
Database changed

ログアウトする.

MariaDB [myappDB]> EXIT; ⏎
Bye
[vagrant@centos8 centos8ansible]$

ユーザの削除

まず,管理者 (root) でログインして,ユーザの一覧を表示する.

[vagrant@centos8 centos8ansible]$ mysql -u root -p ⏎
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 10.4.17-MariaDB MariaDB Server

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)]> SELECT user, host FROM mysql.user; ⏎
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| myapp_user  | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
4 rows in set (0.001 sec)

ユーザ myapp_user を削除する.

MariaDB [(none)]> DROP USER myapp_user@localhost; ⏎
Query OK, 0 rows affected (0.008 sec)

再びユーザの一覧を表示して,削除されたことを確認する.

MariaDB [(none)]> SELECT user, host FROM mysql.user; ⏎
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
3 rows in set (0.001 sec)

ログアウトする.

MariaDB [(none)]> EXIT ⏎
Bye
[vagrant@centos8 centos8ansible]$

テーブルやデータベースを削除する

まずは,管理者 (root) でログインして,データベースの一覧を表示する.

[vagrant@centos8 centos8ansible]$ mysql -u root -p ⏎
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.4.17-MariaDB MariaDB Server

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)]> SHOW DATABASES; ⏎
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mroonga_test       |
| myappDB            |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)

データベースを指定して,テーブル一覧を確認する.

MariaDB [(none)]> USE mroonga_test; ⏎
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mroonga_test]> SHOW TABLES; ⏎
+------------------------+
| Tables_in_mroonga_test |
+------------------------+
| comments               |
+------------------------+
1 row in set (0.000 sec)

テーブル comments を削除する

MariaDB [mroonga_test]> DROP TABLE comments; ⏎
Query OK, 0 rows affected (0.004 sec)

テーブルが削除されたこと確認する.

MariaDB [mroonga_test]> SHOW TABLES; ⏎
Empty set (0.000 sec)

次に,データベース mroonga_test を削除する.

MariaDB [mroonga_test]> DROP DATABASE mroonga_test; ⏎
Query OK, 0 rows affected (0.005 sec)

データベースの一覧を見ると,削除されたことが確認できる.

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

さらに データベース myappDB も削除する.

MariaDB [(none)]> DROP DATABASE myappDB; ⏎
Query OK, 0 rows affected (0.000 sec)

データベースの一覧で結果を確認する.

MariaDB [(none)]> SHOW DATABASES; ⏎
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

ログアウトする.

MariaDB [(none)]> EXIT ⏎
Bye
[vagrant@centos8 centos8ansible]$