MySQLの覚書

重複を除いたデータ件数 COUNT( DISTINCT XXX )

	SELECT count(DISTINCT animal_id) FROM sales WHERE 1
	

ページネーションとLIMIT

	SELECT * FROM tests LIMIT 取得位置,取得数;
	

	SELECT * FROM tests LIMIT 取得数 OFFSET 取得位置;
	

日付の日の部分で検索

	SELECT * FROM `nekos` WHERE DATE_FORMAT(test_date, '%d')=3;
	

	SELECT * FROM `nekos` WHERE DATE_FORMAT(test_date, '%Y-%m')='2014-04';
	

phpMyAdminの認証ぎれ対策

1. phpMyAdminにログイン
2. ホーム→設定→機能
3. 「ログインクッキーの有効期間」を増やす。

1つのテーブルでツリー構造データを扱う

	SELECT C.id,C.title,C.parent_id,P.title
	FROM recs AS C
	LEFT JOIN recs AS P ON C.parent_id=P.id
	WHERE
		C.category_id1=99
	

新規追加と更新を自動判別 | ON DUPLICATE KEY UPDATE

	INSERT INTO animals (id, name,price)
	VALUES (9, 'キノボリトカゲ', 9000)
	ON DUPLICATE KEY UPDATE id=9, name='キノボリトカゲ', price=9100
	

複数行のデータをコンマで連結して1行にまとめる | GROUP_CONCAT

SELECT GROUP_CONCAT(id  SEPARATOR ', ')  FROM nekos

SELECT
	GROUP_CONCAT(id  SEPARATOR ',') AS ids
	,GROUP_CONCAT(text1  SEPARATOR ',') AS texts
FROM nekos
GROUP BY val1
		

SELECT
	GROUP_CONCAT(id  SEPARATOR ',') AS ids
	,GROUP_CONCAT(text1 order by text1 SEPARATOR ',') AS texts
FROM nekos
GROUP BY val1

フィールドを連結して検索 | CONCAT

	SELECT * FROM nekos WHERE CONCAT( neko_val, neko_name ,neko_date) LIKE '%2014%'
	

	SELECT * FROM nekos WHERE CONCAT( IFNULL(neko_val, ''), IFNULL(neko_name, '') ,IFNULL(neko_date), '') LIKE '%2014%'
	

行の作成日時と更新日時を自動化する

• timestamp型には2038年問題があります。
• 何かのタイミングでUTC/協定世界時とJST/日本標準時の間で変換されてしまうことがあります。
• Laravelのマイグレーションなどでtimestamp型が原因で処理中断というケースがありました。

    CREATE TABLE animals (
        id INT(10),
        name VARCHAR(10),
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    )
	

旧式

	ALTER TABLE  `tests` ADD  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT  '作成日時'
	

	ALTER TABLE  `tests` ADD  `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT  '更新日時'
	

他のテーブルのSELECT結果をUPDATEする

	UPDATE
	    animals,
	    fishs
	SET
	    animals.speed = fishs.swim_speed
	WHERE
	    animals.fish_id = fishs.id
	

月別で集計するSQL

	SELECT
	    DATE_FORMAT(test_date, '%Y-%m') as test_ym,
	    COUNT(id) as count,
	    SUM(sale_amount) as sum_sale_amount,
	FROM
	    shops
	WHERE
	    DATE_FORMAT(test_date, '%Y') = '2015'
	GROUP BY
	    DATE_FORMAT(test_date, '%Y-%m')
	

TEST