[DB接続クライアント]mycliを導入してターミナルで気楽にMariaDB/MySQLを触る

Zennの方にも転載済です。(修正/加筆するときは両方で行います。)

前提 対象読者

• MariaDB(or MySQL)を導入済みであること。
  まだ導入してないよーという初学者の方は、筆者が書いたこちらの記事のMariaDB導入部分を参考にしてみてください。

• なんとなくSQLはわかる人
  SELECTやINSERTなど聞いたことはある、というレベルで問題ないと思います。
  ※ちゃんとSQLやRDB勉強したい方は『スッキリわかる SQL 入門 』などの書籍からスタートしてみるのがオススメです！

• CLI(あのターミナルの黒い画面)でDBを気楽に弄ってみたい方
  本格的に大規模なDBを弄るとなるとSequel ProなどのGUIを使ったほうが流石に楽かと。
  ただ個人開発とかちょっとした実験に触るにはmycliは気楽で良い...と思います。ターミナル触る訓練にもなるしね！

※本記事を英語で読みたい奇特な方はdev.toの方に筆者本人が上げている記事をどうぞ。

下準備 mycliをセットアップしよう

そもそもmycliって何？

mycliとは『MySQL(やMariaDB)などの一般的なDB管理システムのCLIインターフェイス』です。
※mycliはMySQL clientの略。CLIはCommand Line Interfaceのことです。

で、mycliを使うと何が嬉しいの？

補完が入るのでターミナル上で楽にコマンドを打てる、typoが減るというのが一番良いところです。

• 基本コマンドに補完が入る
• テーブル名など補完候補が表示される
• カラーの設定が出来て見やすい

上記は普通のmysqlコマンドで接続した画面。見づらいしコマンドの補完も効かない...

こちらはmycliコマンドで接続した画面。カラーがついて、候補も出るしステキ...!

mycliのインストール

pipかhomebrewを使ってインストールしていきましょう。

-- Mac使ってるなら下記で
$ brew update
$ brew install mycli
-- python使いなら下記でも
$ pip install mycli

mycliが入ったか確認してみてください。

$ mycli --version
Version: 1.21.1

mycliのカラーなどを設定を変更する

設定ファイル~/.myclircをmycliの公式サイトを参考に変更していきます。

# 色変更(若草色のようになります。)
syntax_style = native
# キーのバインディングをviに(vimキーバインドわからないならemacsのままでおｋ！)
key_bindings = vi
# 候補選択を広めに表示
wider_completion_menu = True

mycliでMariaDB(MySQL)をイジってみる

DBに接続する

DBまだ作ってない、どんなDBあったけという人は、データベース名を
省略して次の「データベース一覧を確認」をやってみましょう。

$ mycli -uユーザー名 -pパスワード データベース名
-- 例
$ mycli -uroot -pHOGEHOGE fuga_db

データベース一覧を確認

> SHOW DATABASES;

データベース作成

> CREATE DATABASE IF NOT EXISTS test;

データベース削除

> DROP DATABASE IF EXISTS test;

接続先のホスト名の確認

> SHOW VARIABLES LIKE 'hostname';

テーブル作成

CREATE TABLE 構文 / MySQL公式

ちなみに下記のクエリでこんなテーブルを作ります。


リレーションはこんな感じ(tblsというツール叩いて作った即席ですが)

CREATE TABLE テーブル名 (カラム名 データ型 制約,...
FOREIGN KEY インデックス名 (カラム名, ...) 
   REFERENCES 参照先テーブル名 (カラム名, ...),
INDEX インデックス名(カラム名) COMMENT 'こめんと');

-- 例
> CREATE TABLE IF NOT EXISTS members (
id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(255) NOT NULL UNIQUE,
email varchar(255) NOT NULL UNIQUE,
password varchar(100) NOT NULL,
picture varchar(255) NOT NULL DEFAULT '/default_icon.jpg',
created DATETIME NOT NULL,
modified TIMESTAMP NOT NULL,
-- インデックス/キーを貼るクエリは長くなりがちなので筆者は下記のように分けてます
INDEX idx_members_id_01(id) COMMENT 'for search member quickly'
);

> CREATE TABLE IF NOT EXISTS posts (
post_id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id MEDIUMINT NOT NULL,
content VARCHAR(900),
posted_at TIMESTAMP NOT NULL,
FOREIGN KEY fk_posts_userid_01(user_id)
	REFERENCES members (id)
);

テーブルの一覧を表示

> SHOW TABLES;

テーブルの構造を確認

EXPLAIN 構文

-- DESCRIBEはEXPLAINのシノニム(同義語)です
> DESCRIBE テーブル名;
-- 例
> DESCRIBE members;

※参考
『整数型(真数値) - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT / MySQL公式』

テーブルのCREATE文を表示

SHOW CREATE TABLE 構文
DESCRIBE文より詳しい情報が得られます。

> SHOW CREATE TABLE テーブル名;

テーブルを破棄

> DROP TABLE IF EXISTS テーブル名
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!

indexを確認

> SHOW INDEX FROM テーブル名;
-- 例
> SHOW INDEX FROM members;
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+
| Table   | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment             |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+
| members | 0          | PRIMARY           | 1            | id          | A         | 0           | <null>   | <null> |      | BTREE      |         |                           |
| members | 0          | name              | 1            | name        | A         | 0           | <null>   | <null> |      | BTREE      |         |                           |
| members | 0          | email             | 1            | email       | A         | 0           | <null>   | <null> |      | BTREE      |         |                           |
| members | 1          | idx_members_id_01 | 1            | id          | A         | 0           | <null>   | <null> |      | BTREE      |         | for search member quickly |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+

上記の表示の意味は下記の通り。

項目	説明
Table	テーブルの名前
Non_unique	インデックスが重複を含むことができない場合は 0、できる場合は 1。
Key_name	インデックスの名前。このインデックスが主キーである場合、その名前は常に PRIMARY
Seq_in_index	インデックス内のカラムシーケンス番号であり、1 から始まる。
Column_name	カラム名
Collation	カラムのソート方法。MySQL では、これは「A」(昇順) またはNULL(ソートされない) のどちらかの値
Cardinality	「あるカラムにおける、取りうる値の種類」のこと。カーディナリティー(濃度)が高いほど、MySQL が結合を実行するときにこのインデックスを使用する可能性は高くなる。
Sub_part	カラムが部分的にしかインデックス設定されていない場合は、インデックス設定された文字の数。カラム全体がインデックス設定されている場合は NULL
Packed	キーがパックされる方法を示します。パックされない場合は NULL。
NULL	このカラムに NULL 値を含めることができる場合は YES が、できない場合は ''
Index_type	使用されるインデックス方法 (BTREE、FULLTEXT、HASH、RTREE)
Comment	このインデックスに関する情報(インデックスが無効になっている場合の disabled など)
Index_comment	インデックスが作成されたときに COMMENT 属性でインデックスに対して提供された任意のコメント

※参考
『SHOW INDEX 構文 / MySQL公式』
『MySQL(InnoDB)でカーディナリティの低いカラムにINDEXを張る / Qiita記事』

ちなみにインデックス方法のB-treeについては『B-treeインデックス入門 / Qiita記事』や『これでわかるB-treeアルゴリズム / B-tree algorithm』などがオススメです。

とりあえず今はインデックスを上手に貼ると索引的なやつが出来て検索が早くなるのかな〜というイメージ(詳しい人に殴られそうな説明汗)で進んでもおｋです。

作成済のテーブルのカラムにindex追加

> ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);

indexを削除

DROP INDEX 構文 / 公式
ALTER TABLE 構文 / 公式

> DROP INDEX インデックス名 ON テーブル名
-- もしくは
> ALTER TABLE テーブル名 DROP INDEX インデックス名;

indexのコメント変更

> ALTER TABLE テーブル名
 DROP INDEX インデックス名,
 ADD FULLTEXT INDEX インデックス名 (カラム名) COMMENT "コメント";
-- 例
> ALTER TABLE members 
 DROP INDEX idx_members_id_01, 
 ADD INDEX idx_members_01 (id) COMMENT "HOGE";

データ挿入(INSERT)

INSERT 構文

> INSERT INTO テーブル名 (カラム名) VALUES (値);
-- 例①(全カラムの情報入れる時はカラム名指定いりません、してもいいけど)
> INSERT INTO members 
VALUES
(1, '織田信長', '[e-mail]', 'HOGE', '/oda_icon.jpg', '2020-12-02 1:14:22', '2020-12-02 1:14:22');
-- 例②(特定のカラムに情報入れる時)
> INSERT INTO members
(name, email, password, picture, created)
VALUES
('豊臣秀吉', '[e-mail]', 'FUGA', '/toyo_icon.jpg', '2020-12-01 10:00:00');

データ行削除(DELETE)

DELETE 構文

> DELETE FROM テーブル名;
-- 例①(全部の行消えます)
DELETE FROM members;
-- 例②(限定したいならWHERE句使うとか)
DELETE FROM members WHERE id=3;

データ選択(SELECT)

SELECT構文

> SELECT カラム名, FROM テーブル;
-- 例①特定のカラム
> SELECT id, name FROM members;
-- 例②全てのカラム
> SELECT * FROM members;
-- 例③特定の行のみ
> SELECT * FROM members WHERE id=3;

筆者が詰まったところ

• AUTO_INCREMENT制約のあるカラムには必ずkeyを設定しないといけない

indexを貼ったり、PRIMARY KEYの設定をしてやろう

CREATE TABLE members (
id INT NOT NULL AUTO_INCREMENT,
name varchar(255)
);
(1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')

• DROP INDEXが出来ない

SHOW INDEXを使って下記のようなインデックスがあることを確認

PRIMARYを消そうとするが...

> ALTER TABLE members DROP INDEX PRIMARY;
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PRIMARY ON members' at line 1")

主キーのインデックス名が常に PRIMARY である一方でPRIMARY が予約語であるため、引用符で囲まれた識別子として指定しないとエラーになってしまうのが原因です。下記のようにバッククオート``で囲ってやるとうまく行きます。

> ALTER TABLE members DROP INDEX `PRIMARY`;

ちなみにAUTO_INCREMENTが設定されていて一つしかKEYがないカラムのKEYをDROPさせることは出来ません。

ちょっとした知識まとめ(随時追記予定)

• varchar型のサイズ

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

varcharは可変長の文字列です。(固定長はchar型というのがあります。)上記のMというのは文字列の最大列長を表しており、0から65,532までの範囲を設定...できるのですが有効な最大長は、最大行サイズと使用する文字セットに依存します。

utf-8文字は1文字あたり最大3バイトを必要とするため、utf-8文字セットを使用するVARCHAR列は最大21,844文字と宣言することができます。

※参考

UTF-8（ユーティーエフエイト）とは？文字コードの仕組みを知れば文字化けでも慌てない

VARCHAR / MariaDB公式

参考サイト

• mycli公式ドキュメント

• MyCLI – A MySQL/MariaDB Client with Auto-completion and Syntax-highlighting)

• mycli が便利過ぎる - kakakakakku blog

• データベースオブジェクトの命名規約 / Qiita記事

• MySQLのデータ型 / DBOnline

• 第 11 章 データ型 / MySQL公式