[MySQL]備忘録 – パソコン・スマホの覚え書き

MySQL初心者の私の備忘録です。基本的にPHP(楽天市場で探す・ amazonで探す)を使った対処法です。

エラーは「[MySQL]エラー集」に移動しました。

また関連記事として「[MySQL]個人的逆引きリファレンス」もあります。

Q. SQL文内で使える演算子の例

A.数値項目に1を加算　例項目 kaisu の時「kaisu = kaisu +1」

Q. 複数のカラム(項目)にインデックスをつけたい

A. 複合インデックス(マルチカラムインデックス)というやり方がありました。ただし、キーの代用とはならない模様です。（私の意見です）

Q. SQL文での文字列置換

A. 文字列関数のREPLACE

REPLACE(str,from_str,to_str)

文字列 from_str のすべての出現箇所が文字列 to_str で置換された、文字列 str を返します。REPLACE() は、from_str を検索する際に、大文字と小文字を区別した一致を実行します。

Q. MySQL内でランダム数字(乱数)を出す

A.　RAND()が使えます。公式によれば

0 <= v < 1.0 の範囲内で、ランダムな浮動小数点値 v を返します

i <= R < j の範囲内でランダムな整数 R を取得するには、式 FLOOR(i + RAND() * ( j − i)) を使用します。たとえば、7 <= R < 12 の範囲内でランダムな整数を取得するには、次のようなステートメントを使用します。

SELECT FLOOR(7 + (RAND() * 5));

とのことでした。

私のサンプルとして「１以上1000未満の範囲で乱数を返したい」場合、前述にあてはめて

「1 <= R < 1000」「FLOOR(i + RAND() * ( j − i)) 」という記述と公式から

FLOOR( 1 + ( RAND() * (1000-1) ) )

Q.LOAD DATA INFILE について

A. phpMyAdminを自分で入れた場合は別として、レンタルサーバー側が用意した場合、LOAD DATA INFILE は使えない可能性があります。現時点で管理人が把握している懸念事項がありました。

・PC内のローカルファイルをアップロードするには、PHPもしくはMySQLの環境ファイルの変更が必要。

・アップロードする場合でも、アップロード先ディレクトリの把握、およびアクセス権限の設定が必要。

Q-temp1. 一時テーブル(一時ファイル)を作成し、削除する手順

※キー項目のあるテーブルにはマッチングができるため、一時テーブルは有効ですが、キー項目の無いテーブルは一時テーブルを使う必要はないと、個人的に思います。キーの無いテーブルの場合、テーブルを空にして全件挿入した方がラクです。

A-temp1. あくまでも私のやり方です。

開始処理

CREATE TEMPORARY TABLE IF NOT EXISTS table (`code` char(20) NOT NULL……

…他の処理…

終了処理

DROP TEMPORARY TABLE IF EXISTS table;

Q-temp2. なぜ一時テーブルなのに DROP 処理するのか？そのままで良いのでは？

A-temp2. その理由として、CREATEの公式に下記の記述があったので、削除しています。

「TEMPORARY テーブルは、データベース (スキーマ) と非常に疎な関係（薄い関係）を持っています。データベースを削除しても、そのデータベース内で作成されたどの TEMPORARY テーブルも自動的には削除されません」

Q-temp3.一時テーブルの用途は？

A-temp3.データベース内に残しておいても良いのですが、私が行った一時テーブル処理の概要は

「CSVファイルを一時テーブルにINSERTし、メインテーブルとマッチング。未登録のレコードをメインに挿入したら、一時テーブルは不要」

という流れだったので、一時テーブルつまり中間ファイル(テーブル)は処理実行後は不要となるからです。

なお、元々は「CSVファイルとMySQLのテーブルとのマッチング」をしていたのですが、それを前述のように「２つのテーブルのマッチング」に変更したことで、約500件のテーブルでしたが処理速度が上がり、2秒ほど短縮できました。

Q. テーブルを削除する時「存在チェック」をして、存在するなら削除、存在しないのならスルーしたい。

A. DROP TABLE IF EXISTS table;

IF EXISTS。詳細はDROPの公式を。CREATE TABLEの公式では IF NOT EXISTS キーワードについての解説があります。

Q. [phpMyAdmin] でCREATE TABLE 文のサンプルが欲しい

A. 実際のテーブルを使うので、事前にテーブルのコピーをオススメします。

1 CREATE 文が欲しいテーブルの中身を空にします

2 エクスポートタブでフォーマットを[SQL]にしてエクスポートします

3 中身がテキストファイルが得られるので、必要な部分だけを引用します

-- phpMyAdmin SQL Dump
-- version 4.4.15.10
-- https://www.phpmyadmin.net
--
CREATE TABLE IF NOT EXISTS `nin_sw_taiken` (
  `CODE` char(20) NOT NULL COMMENT '数字または文字列',
  `WEB` varchar(80) NOT NULL COMMENT 'URL',
  `GAME` char(255) NOT NULL COMMENT 'ゲームソフト名',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for table `nin_sw_taiken`
--
ALTER TABLE `nin_sw_taiken`
  ADD PRIMARY KEY (`CODE`);

Q. SQLのテーブルとCSVファイルとの比較(マッチング)サンプル

while ($row = $stmt->fetch()) {
	$w_match = false;
	foreach ($file as $value) {
		if ($value[0] == $row['code']) {
			$w_match = true;
			break;
		}
	}
	if ($w_match == false) {
		$w_id_ar[] = $row['code'];
	}
}
unset($value);

Q. INSERTとUPDATEを一文でするには？

A. テーブル内のキーの項目がカギです。

通常のINSERTの場合、同じキーがすでに存在すると重複エラーになりますが、ON DUPLICATE KEY UPDATE を指定していると、重複エラーを回避してUPDATEをMySQL側がしてくれます。

ですので、キー項目がないテーブル、キー項目を自動採番(オートインクリメント)している場合は「機能しない」と推定されます（未検証のため）。

下記のサンプルは、INSERTしようとした時に「キー項目 IDに３が存在する場合」は kaiという項目の回数を１つ増やし、last_timeを現在の日時に UPDATE します。

「キー項目 IDに３が存在しない場合」は通常の INSERT なので、項目 kai には1をセットします。

INSERT INTO tabel SET ID =3, kai =1 ON DUPLICATE KEY UPDATE kai =kai+1, last_time =NOW()

公式

※あくまでも「私調べ」なので、断定はできませんが

例えば 1件のデータを INSERT ・・・ ON DUPLICATE KEY した時、キーが重複し「UPDATE」となった場合の $stmt->rowCount();　は「２」になる模様です。

Q.条件に該当するレコードの１件目だけが欲しい

A. サンプル

tabelでID=3で一致するレコードだけを record_timeの降順でソートし、その最初のレコードだけを抽出し record_time が欲しい

SELECT record_time FROM table WHERE ID =3 ORDER BY record_time DESC LIMIT 1

公式

Q. 7日より前のデータを削除したい（日時）

A. あくまでも一例ですが、こちらサイト様(謝辞)を参考にさせて頂いた結果、MySQL内の関数を使うと良いそうです。

DELETE FROM table WHERE ( tsuika_time < DATE_SUB( CURDATE(), INTERVAL 7 DAY) )

SQL文内のリンクは公式です。

なお CURDATE()は「今日の日付」、INTERVAL 7 DAYで「7日」それらをDATE_SUB で引き算をして、「<」で「より前」

つまり「現在より７日前の日より過去のデータを対象」

試験　・11/23 14:50～15:00にテスト

結果　・11/15 23:59 より前のデータがすべて削除されました。

注意事項

・CURDATE()は「今日の日付」です。つまり 2021/11/23 00:00:00 と時刻の部分の値を持っていません。そのため、厳密な「７日前」ではありません。

時刻部分も必要な場合は NOW() が良いと思います。

Q.INSERT文で日付型の値として「2021-11-19」をセットしたら「0000-00-00」となった

A.日付だけで良いのなら「’2021-11-19’」とセットすることで回避しました。時刻も付加するなど他に良い方法があるかもしれません。

Q. 2つのテーブルを比較し「一致しないレコードを消す」

A. 公式の下の方

・削除

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

t1 内に存在する行で t2 内に一致するものがない行を削除するには、LEFT JOIN を使用します。

t1　t2
1　1
2
3　3
4　4
5
6　6

この例でいえば「t1の2と5」が削除されます

・抽出だけ

２つのテーブルを比較し存在しない項目を返す→NOT EXISTS

記述例

SELECT ID, koumoku FROM tableA WHERE NOT EXISTS (SELECT ID FROM tableB WHERE tableB.ID = tableA.ID);

テーブルBとテーブルAのIDが一致するものは除く。その条件で、一致しないレコードをテーブルAの方から抽出し、そのIDと項目を表示する。

用途は、非同期の２つのテーブルを比較し、不要なレコードが残った場合は削除するというもの。

テーブルA　　　　テーブルB

レコードあ　　　レコードあ

レコードか　　　レコードか*削除済み

レコードさ　　　レコードさ

削除処理で２つのテーブルを見て「同時に消す」のなら問題はないが、上記のようにテーブルBの「レコードか」を削除した時にテーブルAの方を削除しない、できない場合は、別処理として削除する必要がある。そのため、テーブルAに残ったレコード（例では「か」）を知りたい場合に、NOT EXISTSが使える。もちろん、他の方法も考えられます。

結果のレコードをまとめる→ GROUP BY, DISTINCT

GROUP BYの解説より：「値が選択されたあとに結果セットのソートが発生します」

Q. やりたいこと(SELECT, UPDATEなど)は同じでテーブル名だけが違う場合

A. SQL文のテーブル名をパラメータ渡しで変更することは不可能の模様。よって、パラメータ渡しより前に、SQL文のテーブル名だけを変更する方法です。

$w_table = 'table1';
$w_sql = sprintf("SELECT ID, name FROM %s WHERE ID =:a", $w_table);

$stmt = $dbh->prepare($w_sql);

$w_a = '10';
$stmt->bindParam(':a', $w_a, PDO::PARAM_INT);

事前に $w_table の値を分岐文で変更すれば、処理によってテーブル名を変更できます

Q.長いSQL文の書き方は？

A.諸先輩方のやり方をそのまま拝借させて頂きます。謝辞。

PHP – phpの中での読みやすいSQLの書き方｜teratail

例

$w_sql = <<<SQL

SELECT
a.id,
a.post_title
FROM
テーブル名
条件
ORDER BY a.id;
SQL;

———

いわゆるヒアドキュメントのやり方だそうで改行が可能です。そしてSQL文として認識させるためには適宜、空白を入れた方が良いでしょう。

Q.WHERE句に複数の条件を指定したい

A.下の例は「公表日が開始日(key)～終了日(a)の範囲」かつ「コードが変数(b)」のデータのみ抽出

between A and B

$w_sql = "SELECT * FROM corona_nnu WHERE (kou_day BETWEEN :key AND :a) AND (cd=:b) ORDER BY kou_day";

Q.トリガーって何？

A.オラクル社の解説によると

データベース内で動くプログラム。たとえば

カップ麺Ａ３個、売れた場合

売上テーブルに「カップ麺Ａ　３個」計上されるとする

トリガーを作成していたら

トリガーによって、在庫テーブルのカップ麺Ａから３個マイナスする

のように

１つのテーブルを操作すると、他方のテーブルも更新するのがトリガー。

しかし、これはデータベース内で「トリガーが勝手に行う処理」。

通常なら

プログラム内で「売上テーブル」と「在庫テーブル」の両方の個数を更新すれば済む話で、トリガーでする必要があるのかどうかは、人それぞれ。

MySQL＋PHP(PDOクラス)での部分検索のサンプル

部分一致検索なので検索キーワードの前後に他の文字があっても検索結果となります。

例えば「マリオ」で検索すると、スーパーマリオやマリオワールドが検索結果となります。

$stmt = $dbh->prepare("SELECT * FROM nintendo_taikn WHERE title LIKE :key ORDER BY title");

$w_key ='%'.$key.'%';
$stmt->bindParam(':key', $w_key, PDO::PARAM_STR);

このサイト様を参考にさせて頂きました（謝辞）。ポイントは、パラメータに渡す前に部分検索の特殊文字である %(ワイルドカード) を付加している点です。SQL文やbindParamの中で % を使っても機能しませんでした。

PHP内でSQLで日付を使うサンプル(PDOクラスを使用)

  $w_sql = "SELECT * FROM corona_kyo WHERE kday BETWEEN :key AND :a ORDER BY kday";
  $stmt = $dbh->prepare($w_sql);

  $stmt->bindParam(':key', date('Y-m-d', $str_day), PDO::PARAM_STR);
  $stmt->bindParam(':a',   date('Y-m-d', $end_day), PDO::PARAM_STR);

  $count = $stmt->execute();

ポイントは、日付(int型のUnix タイムスタンプ、例 $str_day)をdate関数で、SQLに対応した文字列に変換してからパラメータ（例 :key）に渡すところです。

テーブル名に-(ハイフン)は使わない

テーブル名にハイフンを使うとSQL文で失敗しました。ネットの情報では`や”で囲む方法があるようですが、私は_(アンダースコア)で代用するか、どちらも使わないかの方法にしました。

おそらく内部でハイフンを「引き算」と定義しているのかもしれません。

テーブル名の事例

エラーになる確率が高い：corona-kyo

代用方法：corona_kyo、coronakyo

やむを得ない場合：`corona-kyo`