MySQLの覚書

連番を作る

UPDATEで連番に変更する場合

	SET @i := 0;
	UPDATE animals SET value1= (@i := @i +1) WHERE animal_type=4;
	

animal_type=4で絞り込んだデータのvalue1に連番をセット更新する。連番は1から始まる。

SELECTで連番を作る場合

	SET @a = 0;
	SELECT id, @a := @a + 1 AS new_id, diary_date,diary_note FROM diarys ORDER BY diary_date ASC;	
	

diarysテーブルをdiary_dateでソートする。
ソートした表に対して、連番を新たに振る(new_id)。


2016-10-17 | 2017-1-11

IDを振りなおす

テーブルのIDを直接、振り直しすることはできない。
しかし、IDを振りなおしたテーブルのコピーを作ることは可能である。

	SET @a = 0;
	insert into diarys2 SELECT @a := @a + 1 AS id, diary_date,diary_note FROM diarys ORDER BY diary_date ASC;		
	

2016-10-17

オートインクリメントのリセット

ALTER TABLE テーブル名 auto_increment = 1;

以下のようにすると100から振りなおされる。

ALTER TABLE テーブル名 auto_increment = 100;

2019-10-21

UPDATEとJOINの組み合わせ

	UPDATE animals
	LEFT JOIN foods
	ON animals.food_id = foods.id
	SET animals.animal_name = food.animal_name2
	

2017-1-11

重複を調べる

animalsテーブル

idID	animal_name	animal_value	animal_date	delete_flg	modified
34	ライオン	1001	0000-00-00	0	2016/6/24 14:50
35	サイ	1002	0000-00-00	0	2016/6/24 14:50
58	クワガタ	NULL	0000-00-00	0	2016/6/29 15:11
59	ライオン	123	0000-00-00	0	2017/4/10 17:44
60	ライオン	245	0000-00-00	0	2017/4/10 17:44
61	クワガタ	NULL	0000-00-00	0	2016/6/29 15:11

重複を調べる

animal_nameの重複を調べる

	SELECT MAX(id) AS max_id,animal_name FROM animals WHERE delete_flg=0 GROUP BY animal_name having count(*) >= 2
	

結果

max_id	animal_name
61	クワガタ
60	ライオン

---

応用：重複の無効化（弱点あり）

重複レコードのうち、IDが大きい方のdelete_flgを1にする。
3件以上の重複があっても1件しか無効にできない弱点がある。

	UPDATE
	    animals T1,
	    (
			select MAX(id) AS max_id
			from animals GROUP BY animal_name having count(*) >= 2
	    ) T2
	SET 
	    T1.delete_flg = 1
	WHERE
	    T1.id = T2.max_id
	

2017-4-10

最新レコードを取得する

	SELECT * FROM animals ORDER BY id DESC LIMIT 1

2017-5-9

WHERE 1=1の利点

「WHERE 1=1」はAND部分をプログラムで書きやすくする利点がある。

「WHERE 1=1」を使わない場合

	SELECT *
	FROM animals 
	WHERE 
		value1 = 1
		AND value2 = 9
		AND value3 = 99
	

value1だけANDが付いておらず不揃い。
プログラムでSQL文を組み立てる時にソースコードが少し冗長になる。

「WHERE 1=1」を使う場合

	SELECT *
	FROM animals 
	WHERE 
		WHERE 1=1
		AND value1 = 1
		AND value2 = 9
		AND value3 = 99
	

すべての条件にANDが付いており揃っている。
プログラムでSQL文を組み立てる時にソースコードを短くできる。


2017-5-9

SQL_CALC_FOUND_ROWSとFOUND_ROWSを用いてLIMITがかかっていないデータ件数を取得する

LIMITで行数制限をかけたデータを取得すると共にLIMITがかかっていないデータ件数が取得したい場合、 SQL_CALC_FOUND_ROWSとFOUND_ROWSを使えば良い。

	# LIMITで行数制限をかけたデータを取得する
	SELECT SQL_CALC_FOUND_ROWS *
	FROM animals
	WHERE delete_flg = 0
	LIMIT 5;
	
	# LIMITがかかっていないデータ件数を取得する。上記のSQL実行後に実行する。
	SELECT FOUND_ROWS();
	

注意

上記のSQLはphpMyAdminで検証することはできない。
MySQLのコマンドラインや、phpなどのプログラム中で検証できる。


2017-5-23

タブ、改行、ダブルクォート、シングルクォート、コンマ、改行を含む文字列の扱い | 「¥t」「¥n」「"」「'」「,」

	SELECT * FROM animals WHERE text1 LIKE '%¥t%';
	SELECT * FROM animals WHERE text1 LIKE '%¥n%';
	SELECT * FROM animals WHERE text1 LIKE '%"%';
	SELECT * FROM animals WHERE text1 LIKE '%¥'%';
	SELECT * FROM animals WHERE text1 LIKE '%,%';
	

2017-5-30

FOR UPDATEによる排他制御 | LOCKの一種

SELECT文にFOR UPDATEを付けると、トランザクションのコミットが終わるまで他スレッドを待たせることができるらしい。
インポート機能やバッチ処理で活用できる。

バッチ処理などでは最初にSELECTを実行することが多いが、その際、FOR UPDATEをSELECT文の末尾につけるだけで排他制御ができるようである。
詳しく検証したわけではない。

使用例

	SELECT * FROM animals WHERE id=99 FOR UPDATE

2017-8-31

テーブル名を変更する

ALTER TABLE 変更前テーブル名 RENAME TO 変更後テーブル名;

	ALTER TABLE yagis RENAME TO yagis_bk;

2018-5-29

XXX