MySQL (MariaDB) でハマった仕様

MariaDB stores the SQL_MODE system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked.

DELETEのときにJOINする

DELETE table_name FROM table_name LEFT JOIN ..のように，DELETEの直後にテーブル名を指定します．

mysqlでjoinした結果をdelete - Qiita

DELETEで`You can't specify target table 'HOGE' for update in FROM clause`エラー

たとえば，以下ではエラーが出ます．

DELETE HOGE FROM HOGE
WHERE HOGE.id NOT IN
   (SELECT MIN(id) 
   FROM HOGE GROUP BY col1)

以下のようにselect * from ( .. ) as pを噛ませると成功します．

DELETE  FROM HOGE
WHERE HOGE.id NOT IN (
                     SELECT * FROM ( 
                                    SELECT MIN(id)  FROM HOGE GROUP BY col1
                                   ) AS p 
                      )

DELETEとかUPDATE文において，更新対象のテーブルをFROMに指定したサブクエリはそのままでは実行できないです．

mysql - You can't specify target table 'NAME' for update in FROM clause - Stack Overflow

You can't specify target table 'table_name' for update in FROM clause - MySQL

UPDATEで`General error: 1093 Table 'HOGE' is specified twice, both as a target for 'UPDATE' and as a separate source for data`

これも上記のDELETEと同様に(SELECT * FROM xxx) as tみたいにすれば成功します．

mysqlコマンドで安全にパスワードを指定したい

1. `mysql_config_editor`

公式の推奨は、mysql_config_editorを使用して、暗号化されたパスワードファイルを生成して保存することです．

MySQL :: MySQL 8.0 Reference Manual :: 6.1.2.1 End-User Guidelines for Password Security

mysql_config_editor の罠 | Netassist Blog

しかし、mysql_config_editorはMariaDBではサポートしていないです． ( mysql_config_editor compatibility - MariaDB Knowledge Base )

また，mysql_config_editorを使うのは，「特定のホストからはパスワード無しでログインできるようにしているのと同じ」なのは留意する必要があります． ( security - Where store password / login-path in MariaDB (equivalent for mysql-config-editor)? - Stack Overflow )

2. `~/.my.conf`に記載

mysqlコマンドでの接続時に，mysql --defaults-file=<conf path>のようにオプションで指定します．

.my.cnfは、chmod 600のように，所有ユーザ以外は読み書きできないようにしておきます．

当然ながら，このファイルには，ファイルがあるホストからしかアクセスできないので安全です．

MariaDBではこれがよさそうです．

3. `SO_PEERCRED`

SO_PEERCREDというオプションを使う手もあるようですが，全然調べていないです．

`DROP CONSTRAINT IF EXISTS`

制約があるときのみDROPする機能はMySQLには無いので，自分でプロシージャを作るなりする必要があります．

mysql - Drop foreign key only if it exists - Stack Overflow

mysqldumpで生成される`/*!00000`形式のコメントは？

特定のバージョン以降のMySQLで実行されるコマンドです．

互換性のため，他のDBMSで実行されないようにということでしょう．

MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.6 コメントの構文

`ALTER TABLE table_a DISABLE KEYS`は効かない

MyISAM でないと効かないらしいです．オプションの問題なのか，mysqldumpした結果に入っており，リストア時にwarningは出るということがありました．

なお，どちらにしてもmysqldumpの結果のSQLでは，FOREIGN_KEY_CHECKS=0としているので，外部キーは無効になります．

1レコードのサイズ制限

1レコードのサイズの上限は65,535バイトです． (a maximum row size limit of 65,535 bytes)

VARCHARは制限に含まれます．TEXTなどは制限外です．

What is the MySQL VARCHAR max size? - Stack Overflow

ストアドファンクションの引数にデフォルト値は設定できない

代わりにNULLを渡すなりするしかないです．

Writing optional parameters within stored procedures in MySQL? - Stack Overflow

1行返す前提のサブクエリが0行を返すとNullの扱いになる

hoge = (select fuga from ...)とかの場合です．

enum型の使いどころ

enumは追加情報を付加できないし、変更が大変なので、絶対に変わらないようなもの (male/female, child/adult) に限るべき．

基本的には別テーブルにしてJOINが良さそうです．

database design - MySQL ENUM type vs join tables - Stack Overflow

(複数procedureで共有する)定数を定義

(変数ではなく)定数を定義する構文やnamespaceは無いです．（postgresqlでもそうです）

回避策としては，

テーブルに定数値を格納
変数を定数として使う
ストアドファンクションを使う (パフォーマンス上の問題があるかも？)

MySql const values - Stack Overflow

MySQL :: How to define a constant that could be used across several procedures?

`show tables`でテーブル一覧を取得すると，ビューも含まれる

show full tables where Table_Type = 'BASE TABLE'とします．

mysql - How to get only tables, not views using SHOW TABLES? - Stack Overflow

truncate時の`ON DELETE CASCADE`

外部キーで参照されてるテーブルは，ON DELETE CASCADEしてもtruncateでエラーになります．

truncateはそういうチェックをしないで高速に削除するものなので当然といえば当然ですが．

外部キーのチェック無効化

SET FOREIGN_KEY_CHECKS=0;とします．

ただし、その後SET FOREIGN_KEY_CHECKS=1にしても違反データはそのままになります

違反がないことを確認するには、ツールなどで確認する必要があります．

mysql - Finding Damage Done by FOREIGN_KEY_CHECKS=0 - Database Administrators Stack Exchange

`errno: 150 "Foreign key constraint is incorrectly formed"`

外部キー作成時のエラーですが，原因はいろいろあります．

カラム名やテーブル名の誤り
型が違う (数値と文字列型, int vs smallint)
参照先のテーブルをまだ作っていなかった

timestamp型カラムの挙動

デフォルトでNOT NULLになります．
create tableで何も指定しないと、勝手にDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPが付きます．
- デフォルトを指定したくない場合は，create tableした後にALTER TABLE <table name> ALTER COLUMN <timestamp column name> DROP DEFAULT;とします．
ON UPDATE ...を付けても，テーブルの値が変わらないとtimestampの値が更新されません．
strict modeでない場合，NULLを入れると現在時刻が入ります．
- MySQL5.6以降では非推奨となり、これをするとデフォルト設定でwarningになります． (MySQL5.6で増えたexplicit_defaults_for_timestamp - hiroi10のブログ)
- MySQL5.6以降なら、以下の手順で、DEFAULTを省略すると、勝手に入ることはなくなります (NULLのままにしようとするとエラーになる．参考: mysql - How do I remove ON UPDATE CURRENT_TIMESTAMP from an existing column? - Stack Overflow
  1. explicit_defaults_for_timestamp=ON
  2. テーブルを作り直す

datetime型でDEFAULT CURRENT TIMESTAMP

MySQL5.6.5以前では，DATETIME型だと初期値、更新値にDEFAULT CURRENT_TIMESTAMPを設定できないです．

FULL OUTER JOINできない

代わりに，RIGHTとLEFTをしてUNIONします．

なお，CROSS JOINはできます．

php - Why does MySQL report a syntax error on FULL OUTER JOIN? - Stack Overflow

`create table as select ...`でtimestamp型があるときに`Error Code: 1067. Invalid default value for 'ts'`とエラーが出る

バグです．

MySQL Bugs: #83704: Invalid default value for TIMESTAMP DEFAULT CURRENT_TIMESTAMP column

引用すると，

When using: - a timestamp column with default - create table .. as select * from (...) - a sub query (2 "select" in cascade) - SET SESSION sql_mode = 'NO_ZERO_DATE';

=> Error Code: 1067. Invalid default value for 'ts'

ということです．

CREATE TABLE TEST_2 AS SELECT * FROM (SELECT NOW() AS a) wrap;とすると再現します．

一時的にNO_ZERO_DATEを無効化することで回避します．

temporary tableとは何？

現在のセッションのみで有効 (セッションが切れると自動でdropされる)
非テンポラリテーブルの名前空間と別
ただし、自己結合やunionができない

MySQLでmaterialized view

MySQL, MariaDBとも実装されていないです．

FlexviewというものがMariaDBにあるが、DBエンジンネイティブなものではないようです． (update イベントでテンポラリテーブル更新処理とかしないといけない)

なお、クエリキャッシュというものがあったが、新しいバージョンでは削除されました．アプリケーション側でなんとかしろということのようです．

Window関数

MySQL 8.0 から実装されました．

Check制約

MySQL 8.0.16で実装されました．

日々の覚書: MySQL 8.0.16にCHECK制約が来て、NOT ENFORCEDなんてものまでついてきた

それ以前のバージョンでCheck制約をエミュレーションする方法:

CHECK制約をエミュレートする新旧の方法とドメイン (MySQL Server Blogより) | Yakst

castで数値でないときにエラー

デフォルトでは，CAST('a' as integer)は0を返します．

エラーを検知したいときは，正規表現などで、変換可能であることを確認するのが良さそうです．

その他ヤバイ自動変換については下記にまとまっています．

これだけは覚えておきたい!!MySQL の６つの自動変換 - sakaikの日々雑感～(T)編

sql_modeをデフォルトの設定で使わない

GROUP_CONCATのバイト数の制限

ビューでの数値型の暗黙的変換

ゼロ除算

create table select * from ...で生成されるテーブルのカラムの型はどうなる？

テーブル名などの大文字小文字の区別

Charset

Collation (寿司ビール問題，ハハパパ問題)

タイムゾーンの問題

ストアドファンクションは，Create FunctionのときのSQL_MODEで実行される

DELETEのときにJOINする

DELETEでYou can't specify target table 'HOGE' for update in FROM clauseエラー

UPDATEでGeneral error: 1093 Table 'HOGE' is specified twice, both as a target for 'UPDATE' and as a separate source for data

mysqlコマンドで安全にパスワードを指定したい

1. mysql_config_editor

2. ~/.my.confに記載

3. SO_PEERCRED

DROP CONSTRAINT IF EXISTS

mysqldumpで生成される/*!00000形式のコメントは？

ALTER TABLE table_a DISABLE KEYSは効かない