MariaDBの基本的な使い方

MariaDB		MariaDBの基本的な使い方
		2024/01/02

MariaDBの基本的な使い方

AWSのRDSでMariaDBを使い始めました。バージョンは10.6.14 (2023年6月リリース)です。

●最初の接続を試す
通常MySQL/MariaDBをインストールすると、rootという名前の管理者ユーザーが作られると思いますが、 RDSではインスタンス作成時に管理者ユーザーの名前も指定でき、そのデフォルトはadminです。ここではadminユーザーで最初の接続を試します。接続先ホスト名はRDS管理画面で確認できますが、 RDSが動いているサーバー自身には利用者はログインできないので、 EC2など別のマシンの上にクライアントをインストールしてそこから接続する必要があります。

したがってまず最初にmariadbクライアントをインストールします。EC2にRHEL系のOSを入れた場合は次のようなコマンドラインになります。

sudo dnf install -y mariadb

インストールできたらrootではなくていいので、通常のLinuxユーザーで早速実行します。

mariadb -h rdstest1.***.rds.amazonaws.com -P 3306 -u admin -p
Enter password:

●データベースの作成

create database nkdb;

●ユーザーの作成と権限の付与

create user nkdbuser identified by 'temptemp';
grant all on nkdb.* to nkdbuser;

-- 作成済ユーザーの確認
select user, host from mysql.user;

●表などのオブジェクトの作成
ここからは管理者ユーザーadminではなく、先ほど作成したアプリケーションユーザーnkdbuserで作業します。

use nkdb
source item_categories.sql
source items.sql

ここではmariadbのコマンドラインでCREATE TABLE文を打つのではなく、別のファイルに用意しておいた物をsourceコマンドで実行しています。 item_categories.sqlの内容は以下の通りです。

CREATE TABLE ITEM_CATEGORIES(
  CATEGORY_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  CATEGORY_NAME VARCHAR(500) NOT NULL
);

AUTO_INCREMENTは、その列の値にNULLを指定された場合に、代わりに既存行のその列の最大値に1を加えた値を自動的に設定するオプションです。
AUTO_INCREMENTを設定する列は同時にINDEX、PRIMARY KEY、UNIQUEのいずれかも設定が必要です。
次にitems.sqlの内容は以下の通りです。

CREATE TABLE ITEMS(
  ITEM_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ITEM_NAME     VARCHAR(500) NOT NULL,
  CATEGORY_ID   INTEGER,
  SIZE_DESC     VARCHAR(500),
  PRICE         INTEGER,
  MAKER_DESC    VARCHAR(500),
  NOTES         VARCHAR(500),
  DISPLAY_ORDER INTEGER,
  CDATE         DATETIME DEFAULT NOW(),
  UDATE         DATETIME DEFAULT NOW(),
  FOREIGN KEY(CATEGORY_ID) REFERENCES ITEM_CATEGORIES(CATEGORY_ID)
);

列にではなくて表全体に対して制約を定義するときの構文がMariaDBはOracleやHSQLDBなどと少し違います。通常は「CONSTRANT {制約名} FOREIGN KEY～」とする所を、MariaDBではFOREIGN KEY～といきなり書き始めます。
では中身は空の表ですが、検索してみます。

select * from ITEMS;

オブジェクト名の大文字、小文字は区別されるので、合わせる必要があります。例えば「select * from items」としてしまうと「表itemsがない」というエラーになります。

MariaDBのデータ型

データ型は基本的な物が揃っており、他のRDBMSと比べても違和感はないですが、よく似たデータ型の差異で少しだけ注意点があります。
数値型にはINT=INTEGER, DOUBLE=REAL, BOOLEAN, DECIMAL, FLOATなどがあります。 BOOLEANはTINYINT(1)の別名です。
文字列型でよく使うのはTEXTとVARCHARです。
TEXTは最大文字数指定が不要ですが、短い文字列ならVARCHARの方が高速と言われています。わずかなトレードオフですが、用途によって使い分けでしょうかね。
日時型としてはDATE、TIME、DATETIME、TIMESTAMPなどがあります。 OracleのDATE型と異なり、MariaDBのDATE型は本当に日付部分しか持てない(時刻部分を持てない) ので注意が必要です。日時両方が持てるのはDATETIMEとTIMESTAMPですが、両者は内部動作に違いがあります。 TIMESTAMPはUTCに変換して内部的に保持しますが、DATETIMEは設定された日付をそのまま保持します。またTIMESTAMPは2038年問題の影響があるので、今から使うならDATETIMEの方がお勧めとされています。
なお現在時刻を返すNOW()という関数は、DATETIME型の列に対しても設定できます。

日本語の文字を扱うと「Incorrect string value」エラーが発生する

文字セットの設定が必要です。通常のMariaDBだとmy.cnfファイルを編集するのですが、 RDSの場合ファイルの編集はできず代わりにRDS管理画面からDBパラメータグループという物を設定します。「character_set_***」というパラメータが6つあり、それぞれutf8mb3かlatin1になっているのを、全てutf8mb4に変更します。
ただし既に作ってしまった表の文字セットはこれだけでは変わらないので、強制的に変更します。

ALTER TABLE ITEMS CONVERT TO CHARACTER SET utf8mb4;

Oracle互換モード

MariaDBにはOracle互換モードという物があり、多くの部分がOracleっぽくなります。互換モードに入るには

SET SQL_MODE='ORACLE';

を実行します。早速遊んでみます。

SELECT 3+5 FROM DUAL;

元来MariaDBでは不要な「FROM DUAL」を使ってもエラーにならず、正しく8と表示されます。

SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD') FROM DUAL

OracleのSYSDATEです。正しく今日の日付が表示されます。

SELECT TO_CHAR(SYSDATE+3,'YYYY/MM/DD') FROM DUAL
#HY000Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.

あれ？エラーになります。このメッセージから分かることは二つあって、一つはSYSDATE+3はOracleのように「今日の3日後の日付」ではなくて何かの整数になってしまうこと、そしてTO_CHARはOracleと違って整数型の値には使えず、必ず日付(的な)値に対してしか使えないようです。

SELECT TO_CHAR(TRUNC(SYSDATE+3),'YYYY/MM/DD') FROM DUAL

これもエラーになります。TRUNC関数はないらしい。

SELECT TO_CHAR(DATE_ADD(SYSDATE, INTERVAL 3 DAY),'YYYY/MM/DD') FROM DUAL

これはOK。今日の3日後の日付が表示されます。DATE_ADDはMariaDBに元からある関数。このように何だか色々が混載されるイメージです。

Oracle互換のPL/SQLプロシージャ

MariaDBの特徴の一つである、Oracle互換のPL/SQLでストアドプロシージャを定義してみます。

use nkdb
set sql_mode=Oracle;
DELIMITER //

CREATE OR REPLACE PROCEDURE COUNT_HIGHER_P(ani_price in INTEGER, ano_count OUT INTEGER)
IS
begin
  SELECT COUNT(*) INTO ano_count
    FROM ITEMS
   WHERE PRICE > ani_price;
exception
when OTHERS then
  SELECT 'Error!'||SQLERRM AS Output;
  ano_count := -1;
end;
//
DELIMITER ;

Oracle互換のPL/SQLを使用するには、まず「SET SQL_MODE=ORACLE;」を実行する必要があります。
次にSQL文の終端文字がデフォルトは「;」ですがこれはプロシージャの定義内部と衝突するため、「DELIMITER //」のように別の文字に変更しておきます。PostgreSQLのPL/pgSQLで使われる方法と同じですね。
そして注目はエラーメッセージを標準出力に出している方法です。 OracleのDBMS_OUTPUT.PUT_LINEはMariaDBでは使えませんが、代わりにMariaDBに備わる「SELECT 'メッセージ'」とすれば発行結果が標準出力に出力される特性を使って、細かい書式はさておきメッセージを標準出力に出すことは可能です。また例のようにSQLERRMが使えるほか、標準SQL92では既に非推奨となっているSQLCODEも使用できます。

では実際に実行してみます。

call COUNT_HIGHER_P(1000, @a);
select @a;

mariadbクライアントの中でストアドプロシージャを実行するにはcallを使用します。また上記の例では、OUT変数はセッションローカル変数を使っています。

ストアドプロシージャと日本語を使うOUT引数の注意

別のストアドプロシージャをPL/SQLで書いてみます。

CREATE OR REPLACE PROCEDURE GET_ITEM_NAME(ani_item_id in number,
 avo_item_name out varchar2 CHARACTER SET 'utf8mb3' COLLATE 'utf8_general_ci')
IS
BEGIN
  SELECT ITEM_NAME INTO avo_item_name
    FROM ITEMS
   WHERE ITEM_ID = ani_item_id;

EXCEPTION
when NO_DATA_FOUND then
  SELECT 'Error! ITEM_ID='||ani_item_id||' is not found.'||SQLERRM AS Output;
  avo_item_name := NULL;
END GET_ITEM_NAME;
//

ITEMS表からITEM_ID列の値が引数の値と等しい行を探し、ITEM_NAME列の値をOUT引数に格納して戻る処理です。この例だけでも幾つかのことが分かります。まず引数のデータ型がNUMBERとVARCHAR2というOracleの物になっていますが、正しく認識されます。また例外処理の部分で NO_DATA_FOUND という定数やNULLの代入など、Oracleと同じ記述ができています。
最大の注意点がOUT引数のCHARACTER SETの指定です。なんとMariaDBのストアドプロシージャでは、OUT引数の文字コードは、その値が表から取り出した値であっても、その表の格納文字コードではなくて「??????????」のように化けてしまうことがあります。そこでこのように明示的に設定した方が良いようです。

call GET_ITEM_NAME(14,@result)//
select @result//
+--------------------+
| @result            |
+--------------------+
| アップルパイ       |
+--------------------+

化けずに表示されましたね。

call GET_ITEM_NAME(40,@result)//
+------------------------------------------------------------------------------------+
| Output                                                                             |
+------------------------------------------------------------------------------------+
| Error! ITEM_ID=40 is not found.No data - zero rows fetched, selected, or processed |
+------------------------------------------------------------------------------------+
select @result//
+---------+
| @result |
+---------+
| NULL    |
+---------+

ちなみにNO_DATA_FOUNDに相当するSQLERRMの文字列は上のようになります。NULLも正しく設定されています。

		もくじ
MariaDB		(C) 2002-2023 MISUMI URANO (2023/07/30 - 2024/01/02)