ERROR 1452 (23000): Cannot add or update a child row: …

Posted by muchag | MySQL | 2016-01-19 (火) 13:58:54

【環境】
MySQL:5.5.27
状況

とあるテーブルに CSV データを入れ込もうとして下記エラーが出た。

エラー
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`database`.`table_b`, CONSTRAINT `[constraint_name]` FOREIGN KEY (`table_a_id`) REFERENCES `table_a` (`id`) ON DELETE CASCADE)

このエラーは外部キー制約に違反したデータを保持した状態で
当該テーブルを更新しようとすると出るとのこと。

原因

親テーブル(table_a)の取り込み時に、2行欠落していた。

解決

親テーブル(table_a)を取り込み直して解決。
かなすぃ。 XO

調査過程

あ~、またやってしまったと思い、
あれこれデータを調べてみたけど、不整合を見つけることができなかった。

検索

親テーブル(table_a)の ID は、1~365。
子テーブル(table_b)の table_a_id カラムに入っている値を
phpMyAdmin で検索して、1 より小さいもの、365 より大きいものを見てみたけど
検索結果は 0。

データ件数

ふと気になって、table_a のデータ件数を見てみたら
なんと! 364件。
あり? と思って、データを追ってみたら
ID:358 がない。。。

ログ

改めて、LAOD DATA LOCAL INFILE のログを見てみたら、あらら。。。

  1. Query OK, 363 rows affected (0.05 sec)
  2. Records: 365  Deleted: 0  Skipped: 2  Warnings: 0

じぇんじぇん見てなかったじぇ。。。
これを確認していれば、無駄な時間を取られずにすんだのに~。

Skipped: 2 なのに、364件あったのは。。。
い、今は気が付かなかったことにしましょ。。。

MySQL | 2016-01-19 (火) 13:58:54 |

HAVING

Posted by muchag | MySQL | 2015-12-28 (月) 9:49:07

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.2.9.2 JOIN Syntax

用途

グループ化されたデータ(GROUP BY)に対して
検索条件を指定する。

検索対象

よって、GROUP BY 句内のカラムか、または集約関数で使用されるカラムしか参照できない。

記述位置

HAVING 句は、すべての GROUP BY 句のあとで、かつすべての ORDER BY 句の前にある必要があります。

MySQL:13.2.9 SELECT 構文

この記事は、日本語版 5.5用はなかった。

適用順

HAVING 句は、ほぼ最後 (項目がクライアントに送信される直前) に最適化なしで適用されます。(LIMIT は HAVING のあとに適用されます。)

MySQL:13.2.9 SELECT 構文

エイリアス

カラムエイリアスを使用できる。

  1. SELECT COUNT(user_id) AS cnt
  2. FORM hoge
  3. HAVING cnt > 10;
MySQL | 2015-12-28 (月) 9:49:07 |

調査

Posted by muchag | MySQL | 2015-12-27 (日) 23:18:52

ちょ、ちょっと私にはハードルが高い。。。と思われる。
でも、少しずつでも触っていれば、理解していけるかもしれないので
いつもの通り特攻してみるメモ。

EXPLAIN

クエリの実行内容解析?
SELECT ステートメントの頭に EXPLAIN を付けて実行する。

  1. EXPLAIN
  2. SELECT *
  3. FROM tableA
  4. WHERE column1 = a;

上のクエリ例と下の EXPLAIN 例の内容は全く合致してないけど。。。

参考サイト

漢のコンピュータ道:MySQLのEXPLAINを徹底解説!!(2009-03-31)
→ちょっと古い記事だけど、詳しくてわかりやすい

Qiita:MySQLのEXPLAIN(2014-10-23)
→上記記事を参考にまとめられた記事

FROFILE

MySQL 5.6 では、非推奨になってしまったらしい。
なくなっちゃうんだって。
GMOメディア エンジニアブログ:MySQL 5.6ではprofilingが非推奨になってしまった件

参考サイト

漢のコンピュータ道:プロファイリングで快適MySQLチューニング生活(2009-02-03)
→ちょっと古い記事だけど、詳しくてわかりやすい

MySQL Casual:show profileでフェーズごとの実行時間を見る – MySQL Casual Advent Calendar 2010(2010-12-05)
→PROFILE を貯める方法? と、それを参照する方法が載っている

障害解析
参考サイト

Qiita:誰も教えてくれなかったMySQLの障害解析方法(2014-07-12)
→まだまだサッパリわからない。というか試す暇がないけど、いつかやりたい!

MySQL | 2015-12-27 (日) 23:18:52 |

String

Posted by muchag | MySQL | 2015-11-20 (金) 15:10:04

面白い関数もあったよ~♪

【環境】
MySQL:5.5.27, 5.5.38

MySQL:12.5 String Functions
 
文字列処理関数。

REPLACE

REPLACE

書式
  1. REPLACE(str,from_str,to_str)

第1引数:対象文字列
第2引数:検索文字列
第3引数:置換文字列

第1引数の対象文字列には、もちろん直接文字列を指定してもいいんだけど
要はデータを扱いたいので、そこにフィールド名を指定する。
フィールド名の場合は、”” は無し。

  1. UPDATE sample SET name=REPLACE(name, 'Tanaka', 'Yamada');
MySQL | 2015-11-20 (金) 15:10:04 |

CASE

Posted by muchag | MySQL | 2015-11-20 (金) 14:39:18


いわゆる CASE 文。
こんなこともできるなんてね~。

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.6.5.1 CASE Syntax

書式

THEN の後ろの値が戻ってくる。

  1. CASE case_value
  2.     WHEN when_value THEN statement_list
  3.     [WHEN when_value THEN statement_list] ...
  4.     [ELSE statement_list]
  5. END CASE
  6.  
  7. //
  8. CASE sex
  9.     WHEN 1 THEN '男'
  10.     WHEN 2 THEN '女'
  11.     ELSE 'その他'
  12. END
条件式
  1. CASE
  2.     WHEN search_condition THEN statement_list
  3.     [WHEN search_condition THEN statement_list] ...
  4.     [ELSE statement_list]
  5. END CASE
  6.  
  7. //
  8. CASE
  9.     WHEN sex = 1 THEN '男'
  10.     WHEN sex = 2 THEN '女'
  11.     ELSE 'その他'
  12. END
  13.  
  14. // 不等号はもちろん、IN 句も使える
  15. CASE
  16.     WHEN age IN (13, 14, 15) THEN '中学生'
  17.     WHEN age IN (16, 17, 18) THEN '高校生'
  18.     ELSE 'その他'
  19. END
利用
SELECT

散らばった値を集約して表示できる。

点数に応じて振り分け

80点を超えた人は合格。
60点を超えた人は再テスト。
それ以外の人は不合格。

  1. SELECT
  2.     name, result,
  3.     CASE
  4.         WHEN result > 80 THEN 'Pass'
  5.         WHEN result > 60 THEN 'ReTest'
  6.         ELSE 'Fail'
  7.     END as judgment
  8. FROM test;
集約関数併用
  1. SELECT  dept_nbr,
  2.     COUNT(CASE WHEN sex = 1 THEN 1 ELSE 0 END), // 男の人数
  3.     COUNT(CASE WHEN sex = 2 THEN 1 ELSE 0 END)  // 女の人数
  4. FROM Table_A
  5. GROUP BY dept_nbr;
GROUP BY

散らばった値を集約できる。
ただし、SELECT にも同内容の指定をすること。

豪快に GROUP BY句にも SELECT句の CASE式をコピーしてやるのがポイントです。
単純に「GROUP BY pre_name」と変換前の列を指定すると、
正しい結果が得られませんので注意してください。

ミックのページ:CASE式のススメ

ORDER BY

任意のソート順を設定できる。

CHECK 制約との併用

CASE式は CHECK制約と非常に相性が良いのです。
実際に CASE式を使う局面の半分は CHECK制約の中ではないかと思うほどです。

ミックのページ:CASE式のススメ

と、あるけど、まだ理解できていない。

複数カラムの最大値を返す

ミックのページ:CASE式のススメ 参照。

参考サイト

DBOnline:カラムの値に応じて異なる結果を返す条件式の記述

サンプルコードによるPerl入門:SQLのhaving句はcase式で書くと直感的に記述できる(2011-04-13)

DN-Web64:MySQL : CASE文で複雑な条件を指定
→簡潔で読みやすい。SELECT, GROUP BY, ORDER BY 別にサンプルあり

ミックのページ:CASE式のススメ
→いつもお世話になっております。相変わらずの詳細な解説をありがとうございます。

MySQL | 2015-11-20 (金) 14:39:18 |

TRUNCATE

Posted by muchag | MySQL | 2015-09-28 (月) 18:37:18

データの全削除。

【環境】
MySQL:5.5.27
XAMPP:1.8.3
書式
  1. TRUNCATE TABLE tbl_name;
リレーション付きの際のエラー

当該テーブルにリレーションを貼ってあると下記のようなエラーが出る。

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

こういうときは、リレーションに対するチェック機能を一旦 OFF に。

  1. SET foreign_key_checks = 0;
  2. TRUNCATE TABLE table_name;
  3. SET foreign_key_checks = 1;
参考サイト

ミラボ:mysqlでrelation貼ってあるテーブルに対してtruncate tableを強制的に行うときの処理

MySQL | 2015-09-28 (月) 18:37:18 |

設定 -> 文字コード

Posted by muchag | MySQL | 2015-05-22 (金) 22:39:16

最近の XAMPP に入っている MySQL は、my.ini をいじらなくても
phpMyAdwmin からデータベースを作成する際に
文字コードを指定することで、文字化けをしなかったので
そのままで利用していたけど、久しぶりに文字化けに遭った。

【環境】
MySQL:5.5.27
XAMPP:1.8.3
デフォルト?

何もしてない気がするので、多分下記がデフォルト。

  1. mysql> show variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | cp932                          |
  7. | character_set_connection | cp932                          |
  8. | character_set_database   | latin1                         |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | cp932                          |
  11. | character_set_server     | latin1                         |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+
my.ini

インストール 1.7.3
以前、こちらの記事で書いたのが基本。

  1. [mysqld]
  2. # 2015-05-22 追記
  3. character-set-server=utf8
  4. skip-character-set-client-handshake
  5.  
  6. [mysqldump]
  7. # 2015-05-22 追記
  8. default-character-set=utf8
  9.  
  10. [mysql]
  11. # 2015-05-22 追記
  12. default-character-set=utf8
以前との変更点

以前 の通りに my.ini をいじったら、MySQL が起動しなくなった。

[ERROR] (xampp)\mysql\bin\mysqld.exe: unknown variable ‘default-character-set=utf8’

MySQL 5.5 から仕様が変更になったらしい。

[mysqld] については
default-character-set=utf8 ではなく
character-set-server=utf8 と記述することで無事に起動。

  1. [mysqld]
  2. - default-character-set=utf8
  3. + character-set-server=utf8
参考サイト

cyberarchitect:[ERROR] mysql\bin\mysqld.exe: unknown variable ‘default-character-set=utf8′

my.ini 編集後
  1. mysql> show variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | utf8                           |
  7. | character_set_connection | utf8                           |
  8. | character_set_database   | latin1                         |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | utf8                           |
  11. | character_set_server     | utf8                           |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+

むw 1つだけ latin1。

global 値

参考サイトにしたがって、global も覗いてみる。

  1. mysql> show global variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | utf8                           |
  7. | character_set_connection | utf8                           |
  8. | character_set_database   | utf8                           |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | utf8                           |
  11. | character_set_server     | utf8                           |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+

ほー。こちらはちゃんとなっている。

my,ini 設定前の global 値も見ておけばよかった~。

character_set_database
原因

character_set_database だけが値が異なっている原因は・・・

この値はどこを参照しているかというと、create database した時の値です。

よかろうもん!:MySQLでcharacter_set_databaseがlatin1になってしまう問題の対応方法

だそうです。

あれれ。
データベースを作成した時に、文字コードを設定しそこねたのかしら。

確認方法
  1. mysql> show create database `databese1`;
  2.  
  3. +-----------+----------------------------------------------------------------------+
  4. | Database  | Create Database                                                      |
  5. +-----------+----------------------------------------------------------------------+
  6. | databese1 | CREATE DATABASE `databese1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
  7. +-----------+----------------------------------------------------------------------+

うほ!確かに。。。

解決方法
  1. mysql> ALTER DATABASE `databese1` default character set utf8;

直った~♪

経緯

問題解決にはあまり関係ないので
記事末に覚え書き用経緯を。

データ情報

駅データ.jp
こちらのサイトから、全国の駅データをいただいてきた。

編集

配布されているデータは、ちゃんと UTF8 なのだけど
データを追加する都合上、Excel で編集してから DB へ流し込んだ。

データが多く、phpMyAdmin からではタイムアウトになってしまったので
コマンドラインから実行。

手順
  1. 配布元よりデータを DL
  2. Terapad で、SHIFT-JIS、CR+LF へ変換
  3. Excel で、データ編集
  4. Excel から CSV 出力
  5. Terapad で、UTF-8N、LF へ変換
  6. コマンドラインより DB へ流し込み

すると、途中から文字化けという事態が起こった。
これはこれで不思議。。。

MySQL | 2015-05-22 (金) 22:39:16 |

CLI -> 基本

Posted by muchag | MySQL,コマンドプロンプト | 2015-05-22 (金) 11:41:07

MySQL のコマンドラインからの操作。

【環境】
MySQL:5.5.27
XAMPP:1.8.3
開始

私は MySQL を XAMPP から利用しているので
MySQL も XAMPP 内にある。

コマンドプロンプトを開いたら、下記3種の利用方法がある。

  • そのまま記述(環境変数を登録している場合)
  • MySQL\bin まで cd
  • フルパスで記述
コマンドラインツールの起動
  1. $ mysql -u ユーザ名 -p

これでパスワードの入力を促されるので
パスワードを入力し、認証が通ったら
ツールを利用できるようになる。

  1. mysql>

プロンプトが上記のようになれば、起動成功。

パスワードも含めて起動
  1. $ mysql -u ユーザ名 -pパスワード

-p オプション指定の直後にパスワードを記述する。
スペースを開けずに記述する ところがミソ。

データベースを指定して起動
  1. $ mysql -u ユーザ名 -D データベース名 -p
データベースの選択
  1. mysql> use データベース名
テーブル一覧表示
  1. mysql> show tables
終了
  1. mysql> quit
  2.  
  3. または
  4.  
  5. mysql> \q
書式
文末

SQL 文の最後に ;(セミコロン)を付けること。
なぜって、下記のようにカスケード記述ができるから?

カスケード記述

長い SQL 文を分割して入力できる。

  1. mysql> SELECT a, b, c, d, e FROM table_a, table_b WHERE table_a.id = table_b.table_a_id AND table_a.id = 1;
  2.  
  3. // 途中で Enter キーを押下すると、継続して SQL 文を記述できる
  4. mysql> SELECT a, b, c, d, e(Enter)
  5.     -> FROM table_a, table_b(Enter)
  6.     -> WHERE table_a.id = table_b.table_a_id(Enter)
  7.     -> AND table_a.id = 1;

というわけで、文末にセミコロンを付けないと、永遠に -> が出てくる。 🙄

メモ
コマンドエイリアス
コマンド エイリアス
quit \q
MySQL,コマンドプロンプト | 2015-05-22 (金) 11:41:07 |

GROUP BY

Posted by muchag | MySQL | 2015-04-05 (日) 9:32:20

レコードのグループ化

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.2.9 SELECT 構文

書式
  1. SELECT * FROM table_name GROUP BY (カラム名)
複数設定
  1. SELECT * FROM table_name GROUP BY (カラム名1), (カラム名2), ...

こうすることで、まず (カラム名1) でグループ化され
その後 (カラム名1) の中で (カラム名2) でグループ化され・・・
ということになる。

挙動

グループ化されたカラム以外の値はどうなるのか。

id amount
101 1
101 2
101 3
  1. SELECT * FROM table_name GROUP BY id

id:101, amount:1 となる。

つまり、先頭行 の値を取ってくる。

最大値、最小値

但し、ちゃんと指定すれば、期待する値を取ることもできる。

  1. SELECT id, MAX( amount ) FROM table_name GROUP BY id

id:101, amount:3 となる。

最小値は MIN( amount )

その他の指定

MySQL:12.19.1 GROUP BY (集約) 関数
最大値、最小値の他には、下記の指定ができる。
合計:SUM( amount ):6
平均:AVG( amount ):2
レコード数:COUNT( amount ):3

併用も可能
  1. SELECT id, MAX( amount ), MIN( amount ) FROM table_name GROUP BY id
それ以外

私の場合は、当該グループの最初と最後のデータが欲しかったので
最初はノーマルに取得。
最後はサブクエリでテーブルを逆順にソートして取得。

ORDER BY との併用

ORDER BY と併用する場合、グループ化された後にソートされる。
つまり! 無意味

あかつきのお宿:MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと

MySQL | 2015-04-05 (日) 9:32:20 |

UNION

Posted by muchag | MySQL | 2015-02-13 (金) 17:28:26

他の記事もそうなんだけど、この記事は特に勉強中で自信がないので、内容が間違っている可能性大。 8-X

【環境】
MySQL:5.5.27

MySQL:13.2.9.4 UNION Syntax

概要

クエリ結果が同一構成となる2つ以上のクエリ結果を合成する。
 
JOIN による結合と違って、リレーションしているわけではない。
単に2つ以上のクエリ結果を1つのテーブル上にする。
 

注意
  1. それぞれのクエリが同カラム数であること
  2. 各カラムが同じ順番に並んでいること
  3. 各カラムのデータ型が同一であること
  4. 場合によって、メイン SELECT の部分にも()が必要
  5. FROM 句サブクエリ内で利用する場合は、サブクエリ全体にエイリアスをつけること

 

基本
  1. SELECT id, name, created_at FROM table_a
  2.     UNION SELECT id, name, modified_at FROM table_b

2つの SELECT がある。
カラム数は同じく 3。
カラム名は異なっていてもよい。→メインの SELECET 文のカラム名が返る。
2015-02-13_UNION_1

 

重複

この項は勉強、実証ともに特に不十分

UNION は、そのまま使うと重複した行は無視されるらしい。

重複した行を拾いたいときは、下記のように ALL をつける。

  1. SELECT id, name, created_at FROM table_a
  2.     UNION ALL SELECT id, name, modified_at FROM table_b

 

注文

こうして合成されたクエリ結果に ORDER BY, LIMIT などの注文を付けたい場合は、更にその後ろに続ける。
そして、SELECT 文を()で括る

  1. ( SELECT id, name, created_at FROM table_a )
  2.     UNION ( SELECT id, name, modified_at FROM table_b )
  3.     ORDER BY id

 

FROM 句

FROM 句にて UNION を利用するときは、エイリアスを付ける。

  1. SELECT id, name, created_at
  2.     FROM (
  3.     SELECT id, name, created_at FROM table_a
  4.     UNION SELECT id, name, modified_at FROM table_b
  5.     ) as sub

このとき、UNION SELECT 文にエイリアスを付けてしまわないように注意。
あくまでも FROM 句全体 に()を付ける。
 
これはサブクエリのルールだから当然か~。
サブクエリ

MySQL | 2015-02-13 (金) 17:28:26 |
次ページへ »