MySQL(MariaDB)調査メモ
- MySQLの他にMariaDBもインストールする。
- MySQLサーバーにログイン後、テキストファイルからSQL文を読み込んで実行する。
- データベースの作成時に ERROR 1044 (42000) が発生する。
- GROUP BY の処理性能を改善したい。
- 日付文字列を日付型に変換する。
- ある時刻とある時刻の時間差を知りたい。
- 数値型のカラムを検索した時、検索結果に含まれる後続の0を取り除く。
- プライマリキーのカラム構成を変更する。
- シングルクオート(')のエスケープはどうやってやるのか?
- BITMAPインデックスはサポートしているのか?
- LIMIT句を用いた検索がスロークエリとなる問題に対処する。
MySQLの他にMariaDBもインストールする。
/opt/mariadb-data/my.cnf
[client]
port = 3307
socket = /opt/mariadb-data/mariadb.sock[mysqld]
datadir = /opt/mariadb-data
basedir = /opt/mariadb
port = 3307
socket = /opt/mariadb-data/mariadb.sock
user = mariadb
調査したのはだいぶ前なので、当時何をしたのかは殆ど忘れてしまった。。
唯一覚えているのは、デフォルトのままだとポート番号が被ってしまうので、MariaDBの方を3307にしたことぐらい。
データベースの作成時に ERROR 1044 (42000) が発生する。
データベースの作成時に ERROR 1044 (42000): Access denied for user 'monty'@'localhost' to database 'menagerie' のようなエラーが表示される場合があります。
これは、ユーザーのアカウントには必要な権限がないことを示しています。
この例だと、montyユーザーに権限がないことを示している。
これもだいぶ前に対処した話なので詳細は忘れてしまったが、確かrootユーザーで作成した記憶がある。
GROUP BY の処理性能を改善したい。
場合によって、MySQL はインデックスアクセスを使用することで、それよりはるかに適切に実行し、一時テーブルの作成を回避できます。
これも具体的に何をやったのか忘れてしまったけど、結果的に一時テーブルの作成は回避できた。
日付文字列を日付型に変換する。
SELECT STR_TO_DATE('10.31.2003', GET_FORMAT(DATE, 'USA'));
ある時刻とある時刻の時間差を知りたい。
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
TIMESTAMPDIFF の他に DATEDIFF, PERIOD_DIFF, TIMEDIFF といった関数もある。
数値型のカラムを検索した時、検索結果に含まれる後続の0を取り除く。
SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM yourfield)) AS yourfield FROM yourtable WHERE yourfield LIKE '%.%'
数値型のカラムを検索すると、小数部の末尾が0で埋め尽くされてしまい表示が冗長になるので、TRIM関数で末尾の0を削る。
プライマリキーのカラム構成を変更する。
ALTER TABLE provider DROP PRIMARY KEY, ADD PRIMARY KEY(person, place, thing);
シングルクオート(')のエスケープはどうやってやるのか?
A “'” inside a string quoted with “'” may be written as “''”.
シングルクオート(アポストロフィー)で囲まれた文字列の中にもシングルクオートがある場合、''という様に連続させることで文字列内のシングルクオートをエスケープできる。
BITMAPインデックスはサポートしているのか?
As of the today (MySQL 8.0), it contains only two types of indexes - HASH and BTREE.
現時点(MySQL8.0)ではHASHインデックスとBTREEインデックスしかサポートしていない。
LIMIT句を用いた検索がスロークエリとなる問題に対処する。
1. Hold the last id of a set of data(30) (e.g. lastId = 530)
2. Add the condition WHERE id > lastId LIMIT 0,30
高速化するためには、予めWHEREとかで件数を絞り込んでおく必要があるみたい。