概要

MySQLでのSQLによる操作を理解するため、まとめました。
認識間違いがあればご指摘ください。

MySQLへの接続

% mysql -u root
mysql> [以下 ここのコマンド]

データベース定義

選択・指定

-- 使用データベースの選択
USE データベース名;
-- 対象テーブルの指定
FROM テーブル名

作成・削除

-- データベース/テーブルの作成
CREATE DATABASE データベース名;
-- データベース/テーブルの削除
DROP DATABASE データベース名;
-- テーブルの作成
CREATE TABLE テーブル名 (カラムA Aの型, カラムB Bの型, …);
-- テーブルの削除
DROP TABLE テーブル名;
-- カラムの削除
ALTER TABLE テーブル名 DROP カラム名;

追加・変更

-- カラムの追加
ALTER TABLE テーブル名 操作
ALTER TABLE テーブル名 ADD カラムA　Aの型；  -- 1個
ALTER TABLE テーブル名 ADD (カラムA Aの型, カラムB Bの型, …); --複数
-- カラムの変更
ALTER TABLE テーブル名 CHANGE 旧カラムA 新カラムB 新カラムBの型;

確認

-- データベース・テーブルの一覧表示
SHOW DATABASES;
-- テーブルの確認
SHOW TABLES;
-- テーブル構造確認
SHOW columns FROM テーブル名;

主に使用する型

種類	型
整数	INT
実数	DECIMAL
文字列	CHAR
文字列（長）	VARCHAR
真偽値	BOOL	ture/false
真偽値	TINYINT(1)	1/0
日付	DATE
時間	TIME
日時	DATETIME
特定文字列から1個	ENUM
特定文字列から複数	SET

テーブル作成構文

DROP TABLE IF EXISTS テーブル名； --仮にテーブルが存在していたら削除
CREATE TABLE テーブル名 (
   id INT NOT NULL AUTO_INCREMENT --NULLではない整数連番、主キー設定されていれば自動設定   
   カラムB ENUM（'a','b','c'), --カラムBにはa,b,c,の内1つ挿入可（それ以外x）
   カラムCs SET（'a','b','c'), --カラムCs（複数形）にはa,b,c,の内複数挿入可（それ以外x）
   カラムD　Dの型 CHECK(条件）, --条件に当てはまらないものを制限
   カラムE　Eの型 UNIQUE, --重複を制限
   PRIMARY KEY (id)  -- idを主キーに設定し、入れ忘れや重複をエラーに。
);

INSERT INTO テーブル名(カラムA, カラムB) VALUES
　　　(値1, 値2),
　　　(値1, 値2);

データベース操作

検索

-- テーブルのカラムを検索
SELECT カラム名 FROM テーブル名
-- ワイルドカード ＊ で、すべてのカラムのデータ取得
SELECT * FROM テーブル名
-- テーブルの重複しないカラムのデータ取得
SELECT DISTINCT カラム名 FROM テーブル名
-- A名をB名に変更
A名 AS B名

登録

-- すべてのカラムに値を入れる
INSERT INTO テーブル名 VALUES(値1, 値2, 値3);
-- 特定のカラムに値を入れる
INSERT INTO テーブル名(カラムA, カラムB) VALUES(値1, 値2);

更新

-- テーブルの条件に当てはまる部分を変更内容のように更新する
UPDATE テーブル名 SET 変更内容 WHERE 条件;

削除

-- テーブルの条件に当てはまる部分を削除
DELETE FROM テーブル名 WHERE 条件;

条件(演算子使用可）

-- 条件によって取得するデータを制限
WHERE 条件
-- 特定箇所の文字を検索
WHERE カラム名 LIKE '検索文字'
-- A かつ B の指定
WHERE A AND B
-- A または B の指定
WHERE A OR B
-- A ではない の指定
WHERE NOT A
-- カラム名の上限〜下限に含まれているものの指定
WHERE カラム名 BETWEEN 下限 AND 上限
-- カラム名のリスト（値1..)に含まれているものの指定
WHERE カラム名 IN (値1, 値2, ……)

条件分岐

-- if文
IF（条件式,trueのときの値,falseのときの値）

-- case文
CASE　
　WHEN 条件A THEN Aのときの値
　WHEN 条件B THEN Bのときの値　　
  ELSE どれもあてはまらないときの値
END

連結

-- 指定したテーブルのカラムに登録された値の一致するデータを元に結合
FROM テーブルA
JOIN テーブルB ON テーブルA.カラムa = テーブルB.カラムb
-- => テーブルA の カラムa と テーブルB の カラムb が一致する行を結合する
-- 省略形
FROM users u
JOIN posts p  ON u.カラムa = p.カラムb
-- 一般的には各テーブルの頭文字を使って指定する

-- 複数の文字列を連結
CONCAT(文字列A, 文字列B, ……)

集計

-- カラムのデータを種類ごとにグループ化
GROUP BY カラム名
-- GROUP BYの結果に条件を付けたい場合
HAVING 条件
-- 並び替え
ORDER BY カラム名
-- 逆順に並び替え
ORDER BY カラム名 DESC
-- 並び替えオプション
ORDER BY カラム名,message(アルファベット順） LIMIT 4 OFFSET 3; --最初の3件除外、して4件抽出
                                     LIMIT 3,4;--上と同じ
-- カラムのデータの総量をカウント（nullだとエラー）
SELECT COUNT(カラム名)
-- null含めて全ての行数をカウント
SELECT COUNT(*) 

SELECT SUM(カラム名)-- 合計
SELECT AVG(カラム名)-- 平均
SELECT MAX(カラム名)-- 最大
SELECT MIN(カラム名)-- 最小

検索基本構文

SELECT 
　　カラム名
　　COUNT(カラム名)・・・
FROM テーブルA
　JOIN テーブルB　ON　〜〜〜　 
　WHERE 条件 -- AND OR IN LIKE ...
　  GROUP BY カラム名（グループ分け）
    ORDER BY 並び替え　--LIMIT OFFSET　
　　　HAVING 追加条件

俺です。
CacheStorageとしてよくつかうRedisですが、たまに中身をチェックしたい人生を迎えるときがあるとおもいます。

鼻ほじりながら keys * したら早いけど非力なRedis君は音を上げてしまうかもしれませんし、
RedisCommandだけで頑張るのがしんどいときはRDBMSにぶちこむと捗るよというメモをのこします。

でもちょっとまってもしも俺がAthena玄人なら...

コネクタ使ったほうが楽そうです
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/athena-prebuilt-data-connectors-redis.html

以下人生を踏み倒したい俺向け

Redis

キー一覧を取得する

僕はkeys負荷死したくないので前者の手段を使います。

• ElatiCache RedisならSnapshotから複製して実行する
• ReadEndpointをアプリケーション側で参照してないならReadEndpointに実行でもいいかも

$ redis-cli -c -h hogefugare-dis.com --scan > keysfile

キーに登録されているTypeを取得する

• typeごとにMySQLに作るテーブル列を洗い出します

$ redis-cli -c -h hogefugare-dis.com
redis-cli> type <キー一覧取得で取得したキーいくつか指定>

Redisからキー指定でvalueを抽出する

• keyのtypeがhashだったときの例です。
• datetime型のvalueが含まれている場合はMySQLへ楽にLoadできるように取り出すタイミングで加工するのが楽ちんかと
• redis-cliは --csv オプションを使うとCSV形式で取得できるのが超よいですね
• hgetallだとtypeとvalue両方出力されるのでhmgetでtype指定で抜きだします。hgetallの結果を加工してvalueだけのcsvファイルを作るもアリだとおもいます
• 1行ずつvalueを抽出するのは本当に遅いので並列実行がおすすめ

REDIS_HOST=hogefugare-dis.com
cat keysfile|  while read KEY
do
    result=$(redis-cli -c -h ${REDIS_HOST} --csv  hmget ${KEY}  hoge fuga ..取得したいtypeを並べるのです..)
    echo "$result"
done > keydata.csv

MySQL

テーブル作成とデータロード

• typeにあわせてRDB側にテーブル作ります

CREATE TABLE hogehoge (
    hoge int(11),
    fuga varchar(768),
    bar json
)

• データロード

LOAD DATA LOCAL INFILE 'keydata.csv' INTO TABLE hogehoge FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

データロード後煮るのも焼くのも好きにどうぞでレッツエンジョイ

こんにちは。
今日はMySQLでユーザーを作成してそのユーザーに対して権限を付与していきます。

開発環境

macOS Big Sur 11.0.1
MySQL 8.0.22

手順

MySQLにログインする。

MySQLをインストール直後の場合、ユーザー名がrootでホスト名がlocalhostのユーザーのみ登録されています。

この状態では、ターミナルで

mysql -uroot -hlocalhost 

と入力すると、

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.22 Homebrew

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

このようにログインすることができます。

rootユーザーにパスワードを設定している場合は -p(パスワード) オプションでログインすることができます。

ユーザーを作成する

次にMySQlにログインできた状態で新たにユーザーを作成してみます。

mysql> CREATE USER test IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.01 sec)

ここでは、ユーザー名test、パスワードsecretのユーザを作成することができました。

ユーザー名とパスワードは自由に変更することができます。

作成したユーザーに権限を付与する

まず、新たに作成したユーザーの現時点での権限をGRANT文で確認してみます。

mysql> show grants for 'test'@'%';
+----------------------------------+
| Grants for test@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
+----------------------------------+
1 row in set (0.00 sec)

USAGEと表示されていますが、これは権限が何もないことを表しています。

このユーザーに対して権限を付与してみます。

mysql> grant create all on *.* to 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

これで全てのデータベースに対して全ての権限を付与することができました。

先ほどのコマンドでもう一度確認してみます。

mysql> show grants for 'test'@'%';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `test`@`%`                                                                                                                                               |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `test`@`%` |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

権限が付与されていることが分かります。

まとめ

あるデータベースのみに権限を付与したり、権限のレベルを調整することもできるので是非調べてみてください。

どういうもの？

・GoToイートキャンペーンに加盟している店舗を地図ベースで確認することができるブラウザアプリ
・「食べログ」、「ぐるなび」、「ホットペッパー」に登録されている店舗を統括
・コロナウイルスの流行で特に甚大な影響を受けている個人店舗を掲載（有名チェーン店は除く）
https://kanjimap.jp

表示画面

機能一覧

・GPSの利用
・「たべろぐ」、「ぐるなび」、「ホットペッパー」毎に、マーカ制御
・Zoomレベルによるマーカ表示数制御
・PWAの対応
・駅名検索による表示画面の遷移

技術スタック

・OpenStreetMap
・leaflet
・jquery
・非同期通信
・MySQL
・PHP
・PWA
・GPS
・AutoComplete
・スクレイピング
・Python
・Perl
・タイルサーバ構築

こだわったところ

・タイルサーバ構築と高速化
・マーカー制御と軽量化
・通信量の低減化

開発者の戯言

GoToイートが開始してすぐに計画、開発を進めました。リリースは11月の中旬です。大体1ヵ月半程度となっています。
正直、GoToイートはもっと続くものだと思っていましたが思ったよりも寿命を早く迎えてしまったことをとても残念に思っています。発端は感染症なので仕方ありませんが。。。
あとは開発するスピードが遅かったのもあります。このサービスの開発は2人で行いましたが、2人ともはWeb系ではなく組み込み系の人です。そのため、初めて使った言語（Perl、PHP、javascript）とかMySQLも初めてでしたし、大規模データの扱いも大変でした。
デザインに関しては、bootstrapとawesomefontに頑張ってもらいましたが、デザイナーさんは必要だなぁと再認識しました。スマホで手軽に使えることをベースに作っていったので、画面サイズの大きいPCだと使いづらいかなぁとかはありますし。そこまで手が回らなかったのもあります。
現行ではまだまだ粗削りの部分が数多くありますが、今回開発した地図系ベースで色々なジャンルを抑えていきたいという気持ちがありますので、フィードバックがあると大変嬉しく思います。
今後は、システムの方にフォーカスしたものを投稿していく予定ですので、興味があればどうぞよろしくお願い致します。

こちらのMySQL版です。
WindowsでNimからMySQLへのアクセスは問題ないだろうと思っていましたが、libmysql.dllを探す旅路がありました。

import db_mysql

let db = open("localhost","user","password","mysql")

# 安定のメッセージ
could not load: (libmysql.dll|libmariadb.dll)

結局のところ、MySQLの何をダウンロードすればよいのか？

https://dev.mysql.com/downloads/ にはいくつかの選択肢がありますが、MySQL Community ServerのZip Archiveをダウンロードします。

ダウンロードしたZipファイルを解凍すると２箇所にDLLが入っています。
* libフォルダにlibmysql.dll本体
* binフォルダにlibmysql.dllが参照しているlibssl-1_1_x64.dllやlibcripto-1_1-x64.dllなどの関連DLL群

思わぬ伏兵

上記のZipを解凍したファイルからDLLを抽出(もしくはパスを通して)して、nimでコンパイルをかけても、libmysql.dllが無いよと言われます。
Dependency Walkerで調べると見つからないDLL(vcruntime140_1.dll)があるようで、このファイルを調べてみるとMicrosoft Visutal C++のラインタイムに含まれているようなので、こちらをインストールします。

https://visualstudio.microsoft.com/ja/downloads/

解決された依存関係

まとめ的な何か

MySQLもインストーラがあるから簡単にアクセスできるだろうと思っていたのですが、なかなかに面倒でした。

• Community ServerのZipアーカイバをダウンロードし、DLLをかき集める
• VisualStudio2019の再配布可能パッケージをダウンロードし、インストールする

サンプルコード

import db_mysql

const host_port = "localhost:13306"
const user_name = "docker"
const password  = "docker"
const database  = "test_database"

block:
  let db = open(host_port,user_name,password,database)
  echo "opened"
  defer:
    db.close()
    echo "closed"
  for x in db.fastRows(sql"SELECT database()"):
    echo x

実行結果

opened
@["test_database"]
closed

これは MySQL Advent Calendar 2020 の2日目の記事である。

MySQL についての記事を期待した方には申し訳ないが、MariaDB のコードベースの相当部分は MySQL と共通なので、MariaDB も MySQL の仲間ということで許してほしい。実際、テストやデバッグに関する記述は MySQL でも通用するはずだ。

はじめに

この記事の⽬的は、MariaDB の開発経験が無い者が初めて MariaDB にコントリビュートする際の指針を⽰すことにある。基本的なことは Contributing Code に書いてあるので、公式ドキュメントに書かれていない暗黙的な知識を記事の主眼とする。

MariaDB の内部構造について解説することはしない。Expert MySQL が参考になるのでそちらを見てほしい。また、Disk-oriented DB の一般論については、CMU の講義 がよい情報源になる。

※ この記事において、「コントリビュート」という語は、バグ修正や機能追加など実装を伴うもののみを指すものとする。なお、この記事は筆者の2020年11⽉時点での⾒解に基づいたものであり、将来に渡る正しさを保証しない。また、MariaDB Foundation の⾒解とは異なる記述を含む場合がある。

Issue の選び方

MariaDB は JIRA を issue tracker として使っている。まずは JIRA issue の中から適切なものを選んで取り組むのがよい。この節では、初⼼者がどのような issue を選ぶのがよいかについて、筆者の考えを述べる。

Issue にはいくつかタイプがあるが、最初はバグ修正から始めるのを勧めたい。機能追加やリファクタリングはゴールが明確でない場合が多く、最初に取り組むにはややハードルが高い。以下で issue と⾔った場合、常に type = bug の issue を指すものとする。

Contributing Code > Finding Development Projects to Work on には次のように書かれているが、このアドバイスには従わない方がよい。

We are using JIRA to manage the MariaDB project. Go to https://jira.mariadb.org and click on "Projects" to get to the MariaDB project. Browse around the unresolved and unassigned issues to see if there is something that interests you.

Unresolved issue を選ぶのは当然である。そこに異論はない。が、unassigned issue は原則選ぶべきではない。何故なら⼀定以上重要 or 適切に起票されている issue が、unassigned のままで放置されることはほぼ無いからである。裏を返せば unassigned な issue は重要度が⾮常に低いか起票の仕⽅が悪いものが多く、これらは初⼼者には向かない。

よって初⼼者が取り組むべきは unresolved かつ assigned なタスクである。メーリングリスト や Zulip で適切なタスクを提案してくれるように頼むか、assigned だが未着手の issue を勝手にやってしまうのがよいだろう。MariaDB のメンテナもそれでよいと言っている（Zulip を見るのが面倒な人のために下にキャプチャを貼る）。もちろん、他の⼈がすでに作業を開始している issue や、⼗分に議論が進んでおり着⼿⽬前のものを横取りするのは、明らかなマナー違反なので避けるべきである。

バグの再現

取り組む issue を決めたら、当該のバグを再現するテストを書く（繰り返すが issue と⾔った場合は常に type = bug のものを指す）。少なくとも最初のうちは、テストが書けない or 書き⽅がわからないようなバグには取り組むべきではない。

テストに先立って、MariaDB をビルドできるようにしよう。Ubuntu なら Building MariaDB on Ubuntu に従って依存パッケージをインストールし、例えば以下のようにコンパイルする。

mkdir build && cd $_
cmake -DCMAKE_BUILD_TYPE=Debug ..
cmake --build . --config Debug -j 8

E2E テストでバグを再現できるようにするとデバッグが捗る。MariaDB には独⾃の testing framework があるので、これを使う。例えば、既存のテストは下のようにして実⾏する。

cd build/mysql-test
./mtr --parallel=8

MariaDB のテストはコンポーネントや機能毎にいくつかの suite に分かれており、suite 毎、個々のテスト毎に実⾏することができる。

./mtr                     # すべてのテストを実⾏
./mtr innodb              # innodb suite のテストを実⾏
./mtr innodb.alter_table  # innodb suite の alter_table テストのみ実⾏
./mtr innodb --debug      # innodb suite のテストを debug mode で実⾏

新規にテストを作る場合は次のようにする。まず、適切な suite を選び、テストファイルと結果ファイルを作成する。

touch mysql-test/suite/${SUITE_NAME}/t/example.test
touch mysql-test/suite/${SUITE_NAME}/r/example.result

テストファイルにはSQL と test command が書ける。結果ファイルにはテストファイルに書いた SQL とその望ましい返り値を書く。以下に⾃明な例を挙げる。

example.test

--echo #
--echo # MDEV-XXXX issue title
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
SELECT * FROM t1;
DROP TABLE t1;
--error ER_BAD_TABLE_ERROR
DROP TABLE t;

example.result

#
# MDEV-XXXX issue title
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
SELECT * FROM t1;
a
1
DROP TABLE t1;
DROP TABLE t;
ERROR 42S02: Unknown table 'test.t'

結果ファイルを手で書くのが面倒な場合は、テストファイルだけ書いて結果ファイルは空のままにする。この状態でテストを実⾏すると、実⾏結果が結果ファイルと同じディレクトリに example.reject という名前で生成されるので、それをベースにして結果ファイルを作る。

ややこしい作業は⼤抵ライブラリ（？）化されているので、悩んだら調べてみるとよい。--source include/have_innodb.inc のように書かれているのがそれである。テストファイルを書く前に⾊々試⾏錯誤したい場合は、./mtr --start-and-exit として、現在のバイナリでサーバーを起動するのがよい。

デバッグ

テストが書けたら、それを使ってどのコミットでバグが混⼊したか調べる。2分探索の要領でどの時点からテストが fail するようになるか調べればよい (git bisec を使うと楽だ)。原因となる commit が発⾒できたならラッキーだ。もう半分解決できたようなものである。

何らかの理由によりバグの原因となる変更を絞り込めなかった場合は、地道なデバッグを行う他ない。デバッグの手法について包括的に解説することはしない（できない）が、その代わりに筆者の「いつものやり方」をいくつか挙げておく。

• --debug オプションを付けてテストを実⾏することで execution trace を⽣成し、どのあたりが怪しいか当たりをつける。その上で、必要に応じて gdb でデバッグしたりする。なお、execution trace は build/mysql-test/var/log/mysqld.1.trace に出力される。
• エラーメッセージが出る場合、それに対応するエラー番号をコードから探し出し、その周辺を調べる。メッセージと番号の対応は sql/share/errmsg-utf8.txt を見ればわかる。
• コードを読んでも何が行われているのかわからない場合、古いバージョン（例えば 5.5）まで戻って対応する箇所を読む。古いバージョンはコードが比較的単純で読みやすい。

うーむ、思ったよりもバラエティに乏しかった…。他に思いついたら追記する。

PR の作成

バグは解決できただろうか？できたなら Contributing Code > What to Expect From a MariaDB Server Developer を⼀読してから pull request を作成しよう。しばらくするとメンテナがレビュアーをアサインしてくれる。

PR を作成して1⽇たったら CI がすべて通っているか確認しよう。通っていなければ必要に応じて修正を⾏う。MariaDB の CI は遅く、混み合っている場合は本当に1日かかる?

おわりに

半年前の自分（筆者）が読みたいと思うであろう内容をまとめたのが、この記事である。MariaDB のコードベースは複雑だが、バグを修正するだけであればそこまで難しくないので、興味がある人はぜひやってみてほしい。