概要

MySQLにおいて詰まったことをここに入れていきます。
更新していく予定。
間違ったところがあれば指摘お願いします。

AutoIncrement

0とNULL

NO_AUTO_VALUE_ON_ZERO

有効

AUTO_INCREMENTカラムに0を挿入するとそのまま0が入り、nullを指定した時だけ採番された新しい数字が入る。

無効

nullまたは0のどちらでも新しい数字が採番される。

Date型におけるトラブル

DateDiff

「0000-00-00 00:00:00」の検出

これを行うとすべからくNULLが返ってくる。
そのため、もし「0000-00-00 00:00:00」
の検出には、「IS NULL」を使う必要がある。

解決策

「IS NULL」を使うと検出できるのでそちらを使う。

ちなみに「0000-00-00 00:00:00」は
「IS NOT NULL」を使うと実は結果の中に入る。

以下のようなテーブルがあるとしたら

id	data
1	NULL
2	0000-00-00
3	0000-00-00
4	2017-10-10
5	2017-10-05
6	2017-01-01

IS NULLの結果

id	data
1	NULL

IS NOT NULLの結果

id	data
2	0000-00-00
3	0000-00-00
4	2017-10-10
5	2017-10-05
6	2017-01-01

欲しいカラムの検出

データベースの中に欲しいカラムがあるのはわかっているが、テーブルがわからないときに利用できる。

select table_name, column_name from information_schema.columns
where column_name = '<検索したいカラム名>' and table_schema = '<検索対象のデータベース名>';

データベースを指定するSQL

同じ構造のデータベースを複数持っている場合、データベースまで指定してSQLを実行することがある。その場合に困ったことを記述する。

サブクエリについて

一つのサーバーに同じ構造のデータベースを複数乗せている場合に気をつける必要がありそう。

AとBの二つのデータベースがあるとする。
Aに向けたSQL(以下のSQL)をデータベースBをuseしている時に実行する場合

改善前SQL

SELECT COUNT(DISTINCT `name1`.`column`) AS tenpo_num
FROM `DB-A`.`table1` AS `name1`
INNER JOIN `DB-A`.`table2` AS `name2`
ON `name1`.`column` = `name2`.`column` 
WHERE EXISTS(SELECT * FROM `name1` WHERE `name1`.`column` <> 0)

これだとサブクエリのFROMで呼び出すテーブルがデータベースBのtable１になってしまう。
そのため、サブクエリでも明示的にデータベースAを指定してあげる必要がある。

改善後SQL

SELECT COUNT(DISTINCT `name1`.`column`) AS tenpo_num
FROM `DB-A`.`table1` AS `name1`
INNER JOIN `DB-A`.`table2` AS `name2`
ON `name1`.`column` = `name2`.`column` 
WHERE EXISTS(SELECT * FROM `DB-A`.`table1` AS `name1` WHERE `name1`.`column` <> 0)

エラー編

SQLSTATE[42000]: Syntax error or access violation: 1064

そもそも発行したSQLに無理がある場合か、アクセス先の定義がされていない場合に呼び出される。

例 [フレームワーク]

CakePHPやZendframeworkなどのフレームワークにおいて
未定義のメソッドを使用しようとするとquery()メソッドが呼ばれ、
呼び出そうとしたメソッド名でクエリが発行される。

解決策

MySQLへのアクセスがうまくいっているかを確認
発行されているSQL文自体に間違いがないかチェックし、修正
フレームワークのクエリビルダーを使っている場合は、使用しようとしていたメソッドを記述間違い（名前間違い）などをチェックし、修正。

1. MySQLへのアクセスがうまくいっているかを確認

centosなどなら「/var/log/~」などにエラーのログが吐き出されているはずなので細かいところが画面に出力されていない時にはまずエラー文をチェックする。

また、mysqlコマンドにてmysqlにアクセスし、発行されているSQL文を実行して正常に実行できるかを確かめる。
できない場合は正常に実行できるSQL文に直し、それが実行されるようにソースコードを修正する。

2. 発行されているSQL文自体に間違いがないかチェックし、修正

しっかりと設計されていれば、発行されたSQL文のログが残っているはず。
そのSQLの部分をコピペし、Sequel Proでもなんでも良いので、実行してみる。

そこでうまく出力されないようなら、SQL文が間違っているため
それをうまく出力されるように変更し、等しいSQL文が発行されるように
ソースコードを修正する。

3. フレームワークのクエリビルダーを使っている場合は、使用しようとしていたメソッドを記述間違い（名前間違い）などをチェックし、修正。

これは単純だが、そもそも記述間違いに気づいていない場合があるため、
パッケージやプラグインなどでこのような間違いが今後おきないようにするのが良いと考える。

MySQLのSQLで詰まったことまとめ

概要